|
PHP - Import Details From File
Hello, I am new to PHP and am trying to make a script that will ftp into the server via a cron of every hour or so and save the data to a mysql table. The first step is to draw variables out of information in the file via ftp. Its a ban list for a game server I run.
How could I get php to single out the ban reason, admin, etc. the looks like this Code: [Select] "STEAM_0:0:42741441" { "name" "good mom" "unban" "0" "time" "1329161245" "admin" "(Console)" "reason" "no more trolling for you" } "STEAM_0:1:40563669" { "unban" "0" "admin" "Headcrap(STEAM_0:1:42287608)" "time" "1332855999" "reason" "" } "STEAM_0:1:17693550" { "name" "ʎɖɑɱ" "time" "1323055427" "modified_time" "1323221308" "unban" "0" "modified_admin" "Strategos(STEAM_0:0:26662109)" "admin" "Strategos(STEAM_0:0:26662109)" "reason" "And so died the trolling" } Example: Code: [Select] <?php $bannedid = "STEAM_0:0:42741441"; $bannedby "(console)"; // and so on ?> Similar TutorialsI am trying to import csv file to wordpress using database.the files are uploaded in database but its not coming in wordpress . The code is as below: <?php if(isset($_POST['submit'])) { $fname=$_FILES["file"]["name"]; $datetime=$_FILES["file"][""]; $chk_ext = explode(".",$fname); if(strtolower(($chk_ext[1]) == "csv")) { $filename = $_FILES['file']['tmp_name']; $handle = fopen($filename, "r"); while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { $fname=addslashes($data[0]); $datetime=addslashes($data[1]); $products=addslashes($data[2]); $preset=addslashes($data[3]); $format=addslashes($data[4]); $sql = "INSERT into ".$wpdb->prefix ."facebook(filename,datetime,products,preset,format) values('$fname','$datetime','$products','$preset','$format')"; mysql_query($sql) or die(mysql_error()); } fclose($handle); echo "Successfully Imported"; } else { echo "Invalid File"; } } ?> <form enctype="multipart/form-data" action="" method="post" name="uploadDetails"> <table cellpadding="4" cellspacing="0" border="1"> <tr> <td><label for="upload_a_file">Upload File:</label></td> <td><input type="file" name="file" id="upload_a_file" /></td> </tr> <tr> </tr> </table> <input type="hidden" name="fd_upload" value="1" /> <input type="submit" value="Submit" class="allbutton" name="submit" /> </form> Hope you will help me I am looking for a way to upload a csv file to a website and have it populate a database. Does anyone know of a way to do this. The users of the site are novice website users and I am a novice php coder. Any help would be hugely appreciated. Thanks Hello, this script originally works by reading a physical copy of 'bans.txt' located in the same directory. How could I get it to read the remote bans.txt located here and parse info from it like the on-site copy? the remote file: http://108.163.211.219/bans.txt Code: [Select] <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>Data Tables and Cascading Style Sheets Gallery</title> <style>/* Tema: Soft Table - A Simple table style with the use of the soft brown color Author: Newton de G?es Horta Site: -- Country Origin: Brazil */ table { font-size:0.9em; font-family: Arial, Helvetica, verdana sans-serif; background-color:#fff; border-collapse: collapse; width: 100%; } caption { font-size: 25px; color: #1ba6b2; font-weight: bold; text-align: left; background: url(http://www.nghorta.com/csstg/header_bg.jpg) no-repeat top left; padding: 10px; margin-bottom: 2px; } thead th { border-right: 1px solid #fff; color:#fff; text-align:center; padding:2px; text-transform:uppercase; height:25px; background-color: #a3c159; font-weight: normal; } tfoot { color:#1ba6b2; padding:2px; text-transform:uppercase; font-size:1.2em; font-weigth: bold; margin-top:6px; border-top: 6px solid #e9f7f6; } tbody tr { background-color:#fff; border-bottom: 1px solid #f0f0f0; } tbody td { color:#414141; padding:5px; text-align:left; } tbody th { text-align:left; padding:2px; } tbody td a, tbody th a { color:#6C8C37; text-decoration:none; font-weight:normal; display:block; background: transparent url(http://www.nghorta.com/csstg/links_yellow.gif) no-repeat 0% 50%; padding-left:15px; } tbody td a:hover, tbody th a:hover { color:#009193; text-decoration:none; } /* tr:nth-child(even) { background-color: grey; } */ </style></head> <body> <table summary="Submitted table designs"> <thead><tr> <th style="font-size:12px" scope="col"><center>Nickname</center></th> <th style="font-size:12px" scope="col"><center>Admin</center></th> <th style="font-size:12px" scope="col"><center>Banned</center></th> <th style="font-size:12px" scope="col"><center>Reason</center></th> <th style="font-size:12px" scope="col"><center>SteamID</center></th> <th style="font-size:12px" scope="col"><center>Length</center></th> <th style="font-size:12px" scope="col"><center>Status</center></th> </tr></thead> <tbody> <!-- <tr><th scope="row" id="r100"><a href="100.php">rows table template</a></th> <td><a href="http://www.adobati.it">Omar '0m4r' Adobati</a></td><td>Italy</td> <td>Simple, clean and a quite classic table template :)</td> <td><a href="http://www.adobati.it/labs/CSSTable/0m4r.table.css" title="Download the rows table template CSS file">Download</a></td> <td>test</td> <td>test2</td> </tr> --> <?php // Credits to justin as he was able to understand my messy PHP code and do this much better code for me. function buildBanList($arr) { $filename = "bans.txt"; $bans = array(); $tmp_array = array(); // Build Ban Array if (file_exists($filename)) { foreach( $arr as $line ) { if( substr(trim($line), 0, 7) == '"STEAM_' ) { $tmp_array["user_steamid"] = str_replace('"', '', trim($line)); } if( substr(trim($line), 0, 6) == '"time"' ) { $tmp = explode(' ', trim($line)); $tmp_array["user_bantime"] = str_replace('"', '', $tmp[1]); } if( substr(trim($line), 0, 15) == '"modified_time"' ) { $tmp = explode(' ', trim($line)); $tmp_array["user_modified"] = str_replace('"', '', $tmp[1]); } if( substr(trim($line), 0, 7) == '"unban"' ) { $tmp = explode(' ', trim($line)); $tmp_array["user_unban"] = str_replace('"', '', $tmp[1]); } if( substr(trim($line), 0, 7) == '"admin"' ) { $tmp = explode('" "', trim($line)); $tmp2 = explode('(', $tmp[1]); $tmp_array["admin_name"] = str_replace('"', '', $tmp2[0]); } if( substr(trim($line), 0, 6) == '"name"' ) { $tmp = explode('" "', trim($line)); $tmp_array["user_name"] = str_replace('"', '', $tmp[1]); } if( substr(trim($line), 0, 8) == '"reason"' ) { $tmp = explode('" "', trim($line)); $tmp_array["user_reason"] = str_replace('"', '', $tmp[1]); } // Save ban record to main array once detected end. if( substr(trim($line), 0, 1) == '}' ) { // If console ban then set required fields. if( $tmp_array["admin_name"] == "") $tmp_array["admin_name"] = "Console"; array_push($bans, $tmp_array); $tmp_array = array(); } } } //Sort Array by Ban Date $tmp = array(); foreach($bans as &$ma) $tmp[] = &$ma["user_bantime"]; array_multisort($tmp, SORT_DESC, $bans); return $bans; } //Begin Presentation $file = file("./bans.txt"); date_default_timezone_set("Europe/London"); $bans = buildBanList($file); foreach ($bans as $ban) { echo '<tr> '; // Output if ($ban["user_name"] != '') echo '<td style="text-align:center; font-size: 12px">'.$ban["user_name"].'</td><td style="text-align:center; font-size: 12px" class="admin">'; else echo '<td style="text-align:center; font-size: 12px"><span style="color:#FF0000">N/A</span></td><td style="text-align:center; font-size: 12px" class="admin">'; echo $ban["admin_name"].'</td><td style="text-align:center; font-size: 12px">'.date('H:i - d/m/y',$ban["user_bantime"]).'</td>'; /* Unban date. if ($ban["user_unban"] != "0") { echo date('H:i - d/m/y',$ban["user_unban"]); } else { echo "Never"; }*/ echo '<td style="text-align:left; font-size: 11px">'.$ban["user_reason"].'</td>'; // Status $today = strtotime("now"); echo '</td><td style="text-align:center; font-size: 12px">'.$ban["user_steamid"].' </td>'; // Ban Length $date1 = date('y-m-d H:i:s',$ban["user_bantime"]); $date2 = date('y-m-d H:i:s',$ban["user_unban"]); $to_time=strtotime($date1); $from_time=strtotime($date2); $ban_length = round(abs($to_time - $from_time) / 60,0); if ($ban["user_unban"] != "0") { // Days if ($ban_length >= 1440) { $ban_length = round(abs($to_time - $from_time) / 24 / 60 / 60,0); // Years couldn't be arsed to think of another way to do it. if ( $ban_length >= 365 ) { $ban_length = round(abs($to_time - $from_time) / 360 / 24 / 60 / 60,0); if ($bans_length > 1) echo "<td style=\"text-align:center; font-size: 12px\">".$ban_length." Years </td>"; else echo "<td style=\"text-align:center; font-size: 12px\">".$ban_length." Year </td>"; } else echo "<td style=\"text-align:center; font-size: 12px\">".$ban_length." Days </td>"; } // Hours else if ($bans_length >= 60) { $ban_length = round(abs($to_time - $from_time) / 60 / 60,0); if ($bans_length > 1) echo "<td style=\"text-align:center; font-size: 12px\">".$ban_length." Hours </td>"; else echo "<td style=\"text-align:center; font-size: 12px\">".$ban_length." Hour </td>"; } else echo "<td style=\"text-align:center; font-size: 12px\">".$ban_length." Minutes </td>"; } else { echo "<td style=\"text-align:center; font-size: 12px\">Permanent</td>"; } if ($ban["user_unban"] == "0") echo '<td style="text-align:center; font-size: 12px"><span style="color:#FF0000">Banned</span></td>'; else if ( $today > $ban["user_unban"] ) echo '<td style="text-align:center; font-size: 12px"><span style="color:#008000">Expired</span></td>'; else echo '<td style="text-align:center; font-size: 12px"><span style="color:#FF0000">Banned</span></td>'; echo '</tr>'; } ?></table> <center><p>Web-based ULX bans by Russy.</p></center> </div> </body> </html> I've tried experimenting w/ stuff like Code: [Select] $filename = "http://108.163.211.219/bans.txt"; $contents = file_get_contents($filename); but it doesn't seem to want to work. Hi, I assigned a project where i have to import the mobile numbers from the file, but i have no idea how to proceed this. What kind of file to accept as input and how to read. Thanks <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <title>Import a CSV File with PHP & MySQL</title> </head> <body> <form action="" method="post" enctype="multipart/form-data" name="csv" id="csv"> Choose your file: <br /> <input name="csv" type="file" id="csv" /> <input type="submit" name="Submit" value="Submit" /> </form> </body> Hi.. I have code for importing .xml file to database. the problem is I cannot upload my file but no error display. And also I got a problem in importing data from .xml file to database but not all row will be save. here is my code: Code: [Select] <?php $data = array(); $con = mysql_connect("localhost", "root",""); if (!$con) { die(mysql_error()); } $db = mysql_select_db("mes", $con); if (!$db) { die(mysql_error()); } $sql = "select * from sales_order"; $result = mysql_query($sql); if (!$result) { die(mysql_error()); } function add_employee($ProductType,$WorkOrder,$POIssueDate,$SalesMonth) { global $data; $con = mysql_connect("localhost", "root",""); if (!$con){ die(mysql_error());} $db = mysql_select_db("mes", $con); if (!$db) { die(mysql_error()); } $ProductType= $ProductType; $WorkOrder = $WorkOrder; $POIssueDate = $POIssueDate; $SalesMonth = $SalesMonth; $sql = "INSERT INTO sales_order (ProductType,WorkOrder,POIssueDate,SalesMonth) VALUES ('$ProductType','$WorkOrder','$POIssueDate','$SalesMonth')" or die(mysql_error()); mysql_query($sql, $con); $data []= array('ProductType'=>$ProductType,'WorkOrder'=>$WorkOrder,'POIssueDate'=>$POIssueDate,'SalesMOnth'=>$SalesMonth); } if ( $_FILES['file']['tmp_name'] ['error']) //f (empty($_FILES['file']['tmp_name']['error'])) { //$dom = DOMDocument::load('SalesOrder.xml'); $dom = DOMDocument::load($_FILES['file']['tmp_name']); $dom = DOMDocument::__construct(); $rows = $dom->getElementsByTagName('Row'); global $last_row; $last_row = false; $first_row = true; foreach ($rows as $row) { if ( !$first_row ) { $ProductType = ""; $WorkOrder = ""; $POIssueDate = ""; $SalesMonth = ""; $index = 1; $cells = $row->getElementsByTagName( 'Cell' ); foreach( $cells as $cell ) { $ind = $cell->getAttribute( 'Index' ); if ( $ind != null ) $index = $ind; if ( $index == 1 ) $ProductType = $cell->nodeValue; if ( $index == 2 ) $WorkOrder = $cell->nodeValue; if ( $index == 3 ) $POIssueDate = $cell->nodeValue; if ( $index == 4 ) $SalesMonth = $cell->nodeValue; $index += 1; } if ($ProductType=='' AND $WorkOrder=='' AND $POIssueDate=='' AND $SalesMonth=='') { $last_row = true; } else { add_employee($ProductType,$WorkOrder,$POIssueDate,$SalesMonth); } } if ($last_row==true) { $first_row = true; } else { $first_row = false; } } } ?> <html> <body> <table> <tr> <th>Sales Order</th> </tr> <?php foreach( $data as $row ) { ?> <tr> <td><?php echo( $row['ProductType'] ); ?></td> <td><?php echo( $row['WorkOrder'] ); ?></td> <td><?php echo( $row['POIssueDate']) ;?> </td> <td><?php echo( $row['SalesMonth'] ); ?></td> </tr> <?php } ?> </table> </body> </html> and I will attach my sample data and the data with color yellow background is only row I want to save to my database. Thank you so much.. Hi! I hope somebody can help me what im do wrong. i have checked that the data from the file is in $source_file but nothing imports to the database Code: [Select] <?php include('config.php'); include('opendb.php'); if(isset($_POST['upload'])) { $source_file = @$_POST['userfile']; //$source_file = fopen('http://localhost/test/upload/test.csv', 'r'); $target_table = 'foretag'; function csv_file_to_mysql_table($source_file, $target_table, $max_line_length=10000) { if (($handle = fopen("$source_file", "r")) !== FALSE) { $columns = fgetcsv($handle, $max_line_length, ","); foreach ($columns as $column) { $column = str_replace(".","",$column); } $insert_query_prefix = "INSERT INTO $target_table (".join(",",$columns).")\nVALUES"; while (($data = fgetcsv($handle, $max_line_length, ";")) !== FALSE) { while (count($data)<count($columns)) array_push($data, NULL); $query = "$insert_query_prefix (".join(",",quote_all_array($data)).");"; mysql_query($query); } fclose($handle); } } function quote_all_array($values) { foreach ($values as $key=>$value) if (is_array($value)) $values[$key] = quote_all_array($value); else $values[$key] = quote_all($value); return $values; } function quote_all($value) { if (is_null($value)) return "NULL"; $value = "'" . mysql_real_escape_string($value) . "'"; return $value; } } include('closedb.php'); echo "<br>done<br>"; ?> I think this is the right section to post this is in..... Anyway.....I'm building a web site that requires i have functionality to upload a tab delimited file (or some file format that is easiest) via php and insert the data into MYSQL. I've searched for many third party scripts but none seem to work. So I've decided to move to the community to see if someone can give me insight on how i might accomplish this script. I have some third party scripts but I'm not sure if they'd be of any use. Thank you kindly Hi, I have a form that allows a user to enter details and also to upload a image. The form works great and inserts into the database if i dont include the file input. What i would like happen is: The user enters details and selects a file The file then gets uploaded to ../images and the path is then written to bedroom1 variable The addresses and the bedroom1 variable that now holds the uploaded file path e.g. images/test.jpg is written to the database Form Code: [Select] <form action="admin.php" method="POST"> <table> <tr><td> Address 1: </td><td> <input type="text" name="address1" id="address1" onfocus="selected(this)" onblur="notselected(this)"> </td><td> <div id="dynamicText1"> </div> </td></tr> <tr><td> Address 2: </td><td> <input type="text" name="address2" id="address2" onfocus="selected(this)" onblur="notselected(this)"> </td><td> <div id="dynamicText1"> </div> </td></tr> <tr><td> County: </td><td> <input type="text" name="county" id="county" onfocus="selected(this)" onblur="notselected(this)"> </td><td> <div id="dynamicText1"> </div> </td></tr> <tr><td> Bedroom 1: </td><td> <input type="file" name="bedroom1" id="bedroom1" input name="uploadedfile"> </td><td> <div id="dynamicText1"> </div> </td></tr> <tr><td> <input type="submit" name="submitaddapartment" value="Add" id="registerbutton"> </td></tr> </table </form> And the php code to add it to the database Code: [Select] <?php //get variables $submitaddapartment = $_POST['submitaddapartment']; $address1 = strip_tags($_POST['address1']); $address2 = strip_tags($_POST['address2']); $county = strip_tags($_POST['county']); $bedroom1 = strip_tags($_POST['bedroom1']); if ($submitaddapartment) //if submit button was pressed { if ($address1&&$address2&&$county&&$bedroom1&&) //if fields arn't blank { include 'dbase.php'; //connect to database if ((($_FILES["bedroom1"]["type"] == "image/gif") || ($_FILES["bedroom1"]["type"] == "image/jpg") || ($_FILES["bedroom1"]["type"] == "image/pjpeg")) && ($_FILES["bedroom1"]["size"] < 20000000)) { if ($_FILES["bedroom1"]["error"] > 0) { echo "Return Code: " . $_FILES["bedroom1"]["error"] . "<br />"; } else { echo "Upload: " . $_FILES["bedroom1"]["name"] . "<br />"; echo "Type: " . $_FILES["bedroom1"]["type"] . "<br />"; echo "Size: " . ($_FILES["bedroom1"]["size"] / 1024) . " Kb<br />"; echo "Temp file: " . $_FILES["bedroom1"]["tmp_name"] . "<br />"; if (file_exists("../images/" . $_FILES["bedroom1"]["name"])) { echo $_FILES["bedroom1"]["name"] . " already exists. "; } else { move_uploaded_file($_FILES["bedroom1"]["tmp_name"], "/" . $_FILES["bedroom1"]["name"]); echo "Stored in: " . "../images/" . $_FILES["bedroom1"]["name"]; $bedroom1=".'images/.' $_FILES["bedroom1"]["name"]"; } } } else { echo "Invalid file"; } mysql_query ("INSERT INTO user VALUES(NULL,'$address1','$address2','$county','$bedroom1')"); } else { } } ?> Any help would be greatly appreciated. Thanks Fred Hi, I have successfully implemented a master details page with the results aligned in columns linking to a details page. I wish to maintain the recordID passed from the master details page and make the dynamic text, which reads Shade A tree that is capable of..... in the attached screen shot a link to another details page referencing the same recordID. The detailspage2.php would look the same as the screenshot except the Shade text and description below will be highlighted, which I can do, there will be a new image and a new image description. All other dynmaic elements on the page will remain the same. I tried to simply save as my detailspage.php to detailspage2.php and create a link to detailspage2.php. It linked to detailspage2.php but none of the record info showed up in their respective table cells. I have all the names desc's, images, etc setup in a table in my database. Please let me know what code and other info you need to help me out with this procedure. Thanks. I need some help getting started in writing the php code that would import a text file of name/value pairs and then create an html table with those values. The datafile looks something like this: [number]-[attribute]=[value]; 1-Host=server1.abc.dev.jkl;2-Date=Wed Aug 12 2010;3-Set=abc.123.cde;4-Time=01:00:03;5-Length=00:36:09;6-Size=41.54 GB;7-Status=Succeeded; 1-Host=server2.abc.dev.qrs;2-Date=Wed Aug 12 2010;3-Set=gls202.kul_lvm;5-Length=06:20:33;7-Status=Succeeded; 1-Host=server9.mra.dev.xyz;2-Date=Wed Aug 11 2010;3-Set=gls101.aie_lvm;4-Time=01:00:02; Let's say I have an html table: Code: [Select] <table id="stats"> tr> <th>Host</th> <th>Date</th> <th>Set</th> <th>Time</th> <th>Length</th> <th>Size</th> <th>Status</th> </tr> <tr> <td>server1.abc.dev.jkl</td> <td>Wed Aug 12 2010</td> <td>abc.123.cde</td> <td>01:00:03</td> <td>00:36:09</td> <td>41.54 GB</td> <td>Succeeded</td> </tr> </table> I've looked around all day at various samples. I've seen the fgetcsv function. I'm not sure what would be the best approach to load this into data into a table. Using a regex, then load into array or hash and then print this out? I'm assuming i would have to create print statements to produce the html tags. Also, with the sample records I provided. There will be instances where not all the attributes (1-7) have values. So, i'm envisioning empty cells for that record which is fine. I can also change the way the datafile is generated and remove the [number], so its just [attribute]=[value] if that makes it easier. Is there a good mapping technique for this? Thank you for your help. This script is supposed to run in the command line. Like the title says, I need to: Import some columns (input the names with an argument) from a .CSV file Split the column into blocks of 500 lines Translate this pieces Put them back into the original file I already have some code but it is unfinished and not working: <?php //name of the .CSV file $filename = $argv[1]; //name of the column to be translated $header_name = $argv[2]; //open file $file_handle = fopen($filename, "r"); $i=0; $j=0; $counter = 0; $translated = ''; $string = ''; while (!feof($file_handle) ) { $counter++; $line_of_text = fgetcsv($file_handle, 1024); //find the column id that contains the header name. // headers are always on the the first line if ( $counter == 1 ){ while ($line_of_text[$i] != $header_name) $i++; $header_id = $i; } //create the blocks $string .= $line_of_text[$i]; if ($counter % 500 == 0) { $gt = new GoogleTranslateWrapper(); $translated .= $gt->translate($string, "en"); } } //Write the translate column in the same .CSV file fclose($file_handle); ?> Any help in rewriting this to work? Hello, I'm having trouble with the following code $file = file_get_contents("database.sql"); $queries = explode(";",$file); for ($i=0,$c=count($queries);$i<$c;$i++){ mysql_query($queries[$i],$mlink); } the problem is the sql file I'm trying to import has ; alllll over the place, not just at the end of the queries, so the queries are not getting inputted correctly. I could see a way to fix this if each query was on a single line, but some of them are over 100 lines each. How do I make sure it only explodes using the ; at the end of query? and not the ; that are actually in the tables etc. thanks Having a find and replace nightmare and dumbfounded by a way to correct this. I am exporting a large inner join SQL statement in CSV format. I then want to be able to edit and replace values as needed from the other tables. ex: Orlando = 1 Miami = 2 Jacksonville = 3 Well if I want to go through and replace everything 'Orlando' with the id 1, so my inner joins dont break. I am doing this in the following method. // read the file $file = file_get_contents('data.csv'); // replace the data $result = $db->query('SELECT id_baseprices, baseprices FROM baseprices'); while ($line = $db->fetchNextObject($result)) { $file = str_replace(',"'.$line->baseprices.'",', ',"'.$line->id_baseprices.'",', $file); } $result = $db->query('SELECT id_cities, cities FROM cities'); while ($line = $db->fetchNextObject($result)) { $file = str_replace(',"'.$line->cities.'",', ',"'.$line->id_cities.'",', $file); } // write the file file_put_contents('revamped.csv', $file); Problem with the above is it replaces "Orlando" everywhere with 1. I really only want this to happen inside column 8. I am lost and google must hate me tonight, so thank you for any thoughts or comments. I have mysql exported in the following sql format: Code: [Select] - CREATE TABLE IF NOT EXISTS `pma_history` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '', `db` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '', `table` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '', `timevalue` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `sqlquery` text COLLATE utf8_bin NOT NULL, PRIMARY KEY (`id`), KEY `username` (`username`,`db`,`table`,`timevalue`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='SQL history for phpMyAdmin' AUTO_INCREMENT=3761 ; -- -- Dumping data for table `pma_history` -- INSERT INTO `pma_history` (`id`, `username`, `db`, `table`, `timevalue`, `sqlquery`) VALUES (2, 'root', 'information_schema', 'FILES', '2010-03-04 19:26:33', 'SELECT * FROM `FILES`') Just as an example anyway. What I need to be able to do, is import this using PHP. Does anyone know of a suitable class for doing this? I could make my own, but I cant quite work out the best way to seperate the queries out? Thanks Hi, i have a script to import a csv into a database but some of my product descriptions have ' in the names, this causes mysql to error, how can i get around this? here is my code. Code: [Select] if(isset($_POST['submit'])) { $i=0; $fname = $_FILES['sel_file']['name']; $chk_ext = explode(".",$fname); if(strtolower($chk_ext[1]) == "csv") { mysql_query("truncate table products") ; $filename = $_FILES['sel_file']['tmp_name']; $handle = fopen($filename, "r"); while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { if($i > 0) { $sql = "INSERT into products(prod_id,prod_name,prod_description,prod_cat,prod_sub_cat,tax,prod_price,active) values('$data[0]','$data[1]','$data[9]','$data[14]','$data[15]','$data[19]','$data[35]','$data[65]')"; mysql_query($sql) or die(mysql_error()); } $i++; } fclose($handle); echo "Successfully Imported<br>"; } else { echo "Invalid File"; } } Hi, I have two xml files, one has 750 products, and the other one has 900 products (including product informations, stock information, sizes and photos) so shortly one product in xml file is like this;
product_name: x t-shirt And my tables are like this:
Product (product_id (numerical), product_code (alphanumerical), name) (So that I need to insert one product in my db, and take the id, and insert the xml-photos-data with the correct product_id to photos table, same goes with the stocks table) So I m thinking about the easiest and most healthy way to import this amount of data, and I need to update it daily or weekly (planning to do it by cron jobs for now). I have found some solutions but since I have not done this before could not decide which is the most secure way. Need to add that I can have other xml files from different companies to import later on, so I think I should code for long term. Thank you in advance for your help. Edited December 17, 2018 by RommeoI have a table I am trying to import to mySQL I am having trouble assigning the right variable which represents a column header to the correct column header in the mySQL table. <?php /********************************/ /* Code at http://legend.ws/blog/tips-tricks/csv-php-mysql-import/ /* Edit the entries below to reflect the appropriate values /********************************/ $databasehost = "xxx"; $databasename = "xxx"; $databasetable = "names"; $databaseusername ="xxx"; $databasepassword = "xxx"; $fieldseparator = "\t"; $lineseparator = "\n"; $csvfile = "AddEnt2.txt"; $con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error()); @mysql_select_db($databasename) or die(mysql_error()); $fcontents = file ('AddEnt2.txt'); # expects the csv file to be in the same dir as this script for($i=0; $i<sizeof($fcontents); $i++) { $line = trim($fcontents[$i],'\t'); // '\t' for tab delimeted $arr = explode('\t', $line); // '\t' for tab delimeted $name=implode("','",$arr); $PA=implode("','",$line); # if your data is comma separated # instead of tab separated # change the '\t' above to ',' $sql = "insert into $databasetable values ('$PA','$name')"; $sql = str_replace("''", mysql_escape_string("NULL"), $sql); $sql = str_replace("' '", mysql_escape_string("NULL"), $sql); mysql_query($sql); echo $sql ."<br>\n"; if(mysql_error()) { echo mysql_error() ."<br><b><font color=red>\n</b>"; } } ?> Hi all So... I am creating an import script for putting contacts into a database. The script we had worked ok for 500kb / 20k row CSV files, but anything much bigger than that and it started to run into the max execution limit. Rather than alter this I wish to create something that will run in the background and work as efficiently as possible. So basically the CSV file is uploaded, then you choose if the duplicates should be ignored / overwritten, and you match up the fields in the CSV (by the first line being a field title row), to the fields in the database. The field for the email address is singled out as this is to be checked for duplicates that already exist in the system. It then saves these values, along with the filename, and puts it all into an import queue table, which is processed by a CRON job. Each batch of the CRON job will look in the queue, find the first import that is incomplete, then start work on that file from where it left off last. When the batch is complete it will update the row to give a pointer in the file for the next batch, and update how many contacts were imported / how many duplicates there were So far so good, but when checking for duplicity it is massively slowing down the script. I can run 1000 lines of the file in 0.04 seconds without checking, but with checking that increases to 14-15 seconds, and gets longer the more contacts are in the db. For every line it tries to import its doing a SELECT query on the contact table, and although I am not doing SELECT * its still adding up to a lot of DB activity. One thought was to load every email address in the contacts table into an array before hand, but this table could be massive so thats likely to be just as inefficient. Any ideas on optimising this process? Hello all - Currently I have functions that will parse a csv file and insert the values into a database. That code is working. However, I want to take it a step further and ONLY insert selected fields from the csv file. For example, I have 14 rows in the csv and of those I only want to import values into the database of the rows that I define. So, gName, fName and id -> take those and pump those values into the database. I'm sure this a possible but not sure of the direction I should take. Any help on my problem would be excellent! Here's the code: Code: [Select] csv_file_to_mysql_table('tim 3.csv', 'growers'); function csv_file_to_mysql_table($source_file, $target_table, $max_line_length = 10000) { if (($handle = fopen("$source_file", "r")) !== FALSE) { $columns = fgetcsv($handle, $max_line_length, ","); foreach ($columns as &$column) { $column = str_replace(".", "", $column); } $insert_query_prefix = "INSERT INTO $target_table (" . join(",", $columns) . ")\nVALUES"; while (($data = fgetcsv($handle, $max_line_length, ",")) !== FALSE) { while (count($data) < count($columns)) array_push($data, NULL); $query = "$insert_query_prefix (" . join(",", quote_all_array($data)) . ");"; mysql_query($query) or die(mysql_error()); } fclose($handle); } } function quote_all_array($values) { foreach ($values as $key => $value) if (is_array($value)) $values[$key] = quote_all_array($value); else $values[$key] = quote_all($value); return $values; } function quote_all($value) { if (is_null($value)) return "NULL"; $value = "'" . mysql_real_escape_string($value) . "'"; return $value; } Here's some sample data: Code: [Select] Long,Lat,id,gName,fId,fName,fldId,fldName,fldAcers,featureID,objID,fInsu,fFSA,fBid -82.38306422,40.38439870,2,Norris| Tim,3,Hallinan,4,H1 - 10.0,9.900,1,1, , ,29 -82.22279060,40.42760230,2,Norris| Tim,4,Holt,5,Ho1,11.50,1,1, , ,30 -82.21917211,40.42838107,2,Norris| Tim,4,Holt,6,Ho2,15.10,1,1, , ,31 -82.21710436,40.42454375,2,Norris| Tim,4,Holt,7,Ho3,17.90,1,1, , ,32 -82.21833571,40.42367314,2,Norris| Tim,4,Holt,7,Ho3,17.90,2,2, , ,32 -82.21595345,40.42200315,2,Norris| Tim,4,Holt,9,Ho4,9.100,1,1, , ,34 -82.36538195,40.37711617,2,Norris| Tim,5,Home,10,H1 - 36.3A,36.20,1,1, , ,35 -82.36159625,40.37804250,2,Norris| Tim,5,Home,11,H2 - 3.9A,3.900,1,1, , ,36 -82.36196265,40.38085335,2,Norris| Tim,5,Home,12,H3 - 6A,7.000,1,1, , ,37 -82.41030962,40.38997625,2,Norris| Tim,2,Kenyon,13,K10-17A,17.00,1,1, , ,38 -82.38584288,40.35998635,2,Norris| Tim,2,Kenyon,14,K11-14A,14.52,1,1, , ,39 -82.41644710,40.37927258,2,Norris| Tim,2,Kenyon,15,K3 - 18.2A,18.20,1,1, , ,40 -82.40744700,40.37788250,2,Norris| Tim,2,Kenyon,16,K4 - 26.2A,26.12,1,1, , ,41 -82.40390048,40.37467350,2,Norris| Tim,2,Kenyon,17,K5 - 8.9A,8.874,1,1, , ,42 -82.38605720,40.36920760,2,Norris| Tim,2,Kenyon,18,K6 - 19.3A,18.22,1,1, , ,43 -82.39960597,40.38844915,2,Norris| Tim,2,Kenyon,19,K7 - 18.4A,18.37,1,1, , ,44 -82.39515150,40.39498212,2,Norris| Tim,2,Kenyon,20,K8 - 7.3A,7.324,1,1, , ,45 -82.38817795,40.39458225,2,Norris| Tim,2,Kenyon,21,K9 - 40.4A,40.28,1,1, , ,46 -82.38836722,40.39172487,2,Norris| Tim,2,Kenyon,21,K9 - 40.4A,40.28,2,2, , ,46 -82.40294059,40.35565598,2,Norris| Tim,10,Lane,2,1,6.900,1,1, , ,47 -82.40579843,40.35399913,2,Norris| Tim,10,Lane,22,2,7.200,1,1, , ,48 -82.38322795,40.37619695,2,Norris| Tim,6,Leach,23,L1 - 1.5,1.500,1,1, , ,49 -82.38334655,40.38060737,2,Norris| Tim,6,Leach,24,L2 - 17.6,17.60,1,1, , ,50 -82.38032235,40.38354262,2,Norris| Tim,7,Robinson,25,R1 - 7.5,7.400,1,1, , ,51 -82.39919331,40.35353322,2,Norris| Tim,9,Shorey,26,SH1,5.757,1,1, , ,52 -82.40033216,40.35715336,2,Norris| Tim,9,Shorey,28,SH2,13.39,1,1, , ,54 -82.37072642,40.31789197,2,Norris| Tim,8,Stream,27,S1,17.80,1,1, , ,53 |
|||||||