|
PHP - Update A Column Via Csv Import
My primary method of working with my database is Sequel Pro (Mac OS X), and I'm trying to update a column in a table via a CSV import. I should be able to do it via Sequel's Import, but I keep getting errors.
I've made sure I'm Updating and not Inserting.
I've mapped my column (just one column), and it's just a one column CSV file.
I get this error for each row:
[ERROR in row 1] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `email_acct`='\naccs.k12.in.us'' at line 1 I messed with what each is terminated by, escaped by, enclosed by...etc...nothing changes. I get the same error, just at the end it's a different email account location. So I'll try to do it via a query, but I can't find a reliable solution to make it work. I could create a separate table and update via that way, but I can use this as a learning experience. Similar TutorialsHi, I've been scratching my head for a while now about how to do this, I'm relatively new to php and mysql and perhaps foolishly taking on creating a user area for a website. I have everything else working, all of my register account functions and confirmations and all of the login scripts etc. I have created a profile page which returns various information to the user (this bit works fine) and I've got some nice show/hide toggles running with some javascript/css but my intention is to allow the user to change thier information (e-mail address, contact phone number and also whether they are subscribed to the e-mail list), it also displays any support tickets or messages. So after the long intro, here's what I'm struggling with... I have a form in a visibility toggled <div> which submits a 'change_email' script, so a user wants to change their e-mail, clicks on change, the <div> appears, they bang in the new e-mail and hit submit. My php script appears to work (because it doesn't throw up any errors), until you realise that actually it's not updated the record in the db... I'm using mysql_query("UPDATE users SET email='$new_email' WHERE username='$user'"); Do I need to setup variables for all of the information in the db (name, username, password, email, contno etc etc) and include them in the command to get it to work or should that just pick the correct record and then update it? If that is the case is there a way I can include 'blank' variables so I don't have to set them all up... e.g. mysql_query("UPDATE users SET user='',password='',email='$new_email', etc WHERE username='$user'"); Many thanks in anticipation 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. 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 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 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 I 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? Can anyone post a generic update function to update mysql table. The manual approach: update $tablename set $column1='a', $column2='b' where $id=$value; Hi i have this simple update form and scrip but somehow it doesnt seem to be update the field on the database can someone help out please. The html form is the second form bellow where the action send to status_update.php HTML FORM Code: [Select] <?php include("../header.html"); ?> <?php include("header_news.html"); extract($_REQUEST,EXTR_SKIP); ?><?php /* print("sfilm_refnum = $sfilm_refnum<BR>"); print("sfilm_addr01 = $sfilm_addr01<BR>"); print("sfilm_postcode = $sfilm_postcode<BR>"); print("Film Client = $fclient<BR>"); */ ?> <form id="search" action="list.php" method="post" name="search"> <table width="780" border="0" cellspacing="0" cellpadding="4" bgcolor="#eeeeee"> <tr> <td align="right" width="140"></td> <td width="320"><span class="hofblack10"> </span> </td> <td align="center" width="100"><input type="hidden" name="lstart" value="<?php print("$lstart"); ?>" /><input type="hidden" name="lend" value="<?php print("$lend"); ?>" /><input type="hidden" name="lamount" value="<?php print("$lamount"); ?>" /></td> <td align="center" width="100"></td> <td align="right"></td> </tr> </table> </form> <table width="780" border="0" cellspacing="0" cellpadding="4" bgcolor="#4050c4"> <tr> <td width="60" class="hofwhite10">action</td> <td width="140"><span class="hofwhite14">DATE</span></td> <td width="80"><span class="hofwhite14">ID</span></td> <td><span class="hofwhite14">News Titile</span></td> <td width="100"><span class="hofwhite14">Status</span></td> </tr> <tr height="0"> <td bgcolor="white" width="60"></td> <td bgcolor="white" width="140" height="0"></td> <td bgcolor="white" width="80" height="0"></td> <td bgcolor="white" height="0"></td> <td bgcolor="white" width="100" height="0"></td> </tr> </table><table width="780" border="0" cellspacing="0" cellpadding="4"><tr> <td width="60"></td> <td width="80"></td> <td><a class="blueullrg" href="add.php">Add News</a></td> <td align="right" width="120"></td> </tr> <tr height="0"> <td width="60" height="0"></td> <td width="80" height="0"></td> <td height="0"></td> <td align="right" width="120" height="0"></td> </tr> </table> <?php //get the DB connection variables include("../../../includes/config.php"); //connect to DB $connection = @mysql_connect($db_address,$db_username,$db_password) or die("Couldn't CONNECT."); $db = @mysql_select_db($db_name, $connection) or die("Couldn't select DATABASE."); $query2="SELECT * FROM news WHERE !(news_status='deleted')"; $result2 = mysql_query($query2) or die("Couldn't execute QUERY - Select NEWS Qty"); $fqty = mysql_num_rows($result2); //SELECT or FIND the same USERNAME $query3="SELECT * FROM news WHERE !(news_status='deleted') ORDER BY news_id DESC"; $result3 = mysql_query($query3) or die("Couldn't execute QUERY - Select NEWS"); while ($row = mysql_fetch_array($result3)) { $news_id = $row['news_id']; $news_title = $row['news_title']; $news_story = $row['news_story']; $news_image = $row['news_image']; $news_image_caption = $row['news_image_caption']; $news_image_link = $row['news_image_link']; $news_date_day = $row['news_date_day']; $news_date_month = $row['news_date_month']; $news_date_year = $row['news_date_year']; $news_status = $row['news_status']; $news_website = $row['news_website']; $news_date_created = $row['news_date_created']; $news_date_modified = $row['news_date_modified']; ?> <table width="780" border="0" cellspacing="0" cellpadding="4" bgcolor="#eeeeee"> <tr> <td width="60"><span class="hofblack10"> <?php if($news_status=="deleted"){ print("<a class='hifblack10'>deleted</span>"); }ELSE{ print("<a class='blueul' href='edit.php?id=$news_id'>edit</a>"); } ?> </span></td> <td width="140"><span class="titlegrey12"> <?php if(!$news_date_day) { echo "00"; } else{ echo $news_date_day; } echo "/"; if(!$news_date_month) { echo "00"; }else{ echo $news_date_month; } echo "/"; if(!$news_date_year) { echo "0000"; }else{ echo $news_date_year; } ?> </span></td> <td width="80"><span class="titlegrey12"><?php print("$news_id"); ?></span> </td> <td><?php if($news_status=="deleted") { print("<class='hofblack10'>$news_title</span>"); }ELSE{ print("<a class='blueul' href='edit.php?id=$news_id'>$news_title</a>"); } ?></td> <td width="100"> <form id="list_update" action="status_update.php" method="post" name="list_update"> <select name="newnstatus" size="1"> <option <?php if($row['news_status'] == "") { print("selected"); } ?> selected="selected" value="">Status...</option> <option <?php if($row['news_status'] == "on") { print("selected"); } ?> value="on">On</option> <option <?php if($row['news_status'] == "off") { print("selected"); } ?> value="off">Off</option> <option <?php if($row['news_status'] == "deleted") { print("selected"); } ?> value="deleted">Delete</option> </select> <input type="hidden" name="nstatus" value="<?php echo $row[news_status]; ?>" /> <input type="hidden" name="id" value="<?php echo $row[news_id]; ?>" /> <input type="submit" name="update" value="update" /> </form> </td> </tr> <tr height="0"> <td bgcolor="white" width="60"></td> <td bgcolor="white" width="140" height="0"></td> <td bgcolor="white" width="80" height="0"></td> <td bgcolor="white" height="0"></td> <td bgcolor="white" width="100" height="0"></td> </tr> </table> <?php } mysql_close($connection);//}?> <table width="780" border="0" cellspacing="0" cellpadding="4"> <tr> <td width="60"></td> <td width="80"></td> <td><a class="blueullrg" href="add.php">Add News</a></td> <td align="right" width="120"></td> </tr> </table><?php // include("list_navigation.html"); ?> <?php include("../footer.html"); ?> </div></body></html> The action script php Code: [Select] <?php /* echo "fstatus: ".$fstatus."<BR>"; echo "id: ".$id."<BR>"; echo "fclient: ".$fclient."<BR>"; echo "newfstatus: ".$newfstatus."<BR>";*/ //set the date of agreement $timestamp = date('l jS \of F Y h:i:s A'); //get the DB connection variables include("../../../includes/config.php"); //connect to DB $connection = @mysql_connect($db_address,$db_username,$db_password) or die("Couldn't CONNECT."); $db = @mysql_select_db($db_name, $connection) or die("Couldn't select FILMS DATABASE."); // All appears well, so enter into database $query= "UPDATE news SET news_status = '$newnstatus' WHERE news_id='$id'"; $result = mysql_query($query) or die("could not execute query - Update FILMS Record to DB"); //setup an email to the Admin @ hof, w/o attachment $emailto="xx@xxx.co.uk"; $emailfrom="no-reply@xxxx.co.uk"; $emailsubject="xx Record Updated"; $emailmessage="Hello Registrar\n\n"; $emailmessage.="News ID: ".$id."\n"; $emailmessage.="Updated on: ".$timestamp."\n\n"; $emailmessage.="Status was: ".$nstatus."\n"; $emailmessage.="Status now: ".$newfnstatus."\n"; $emailmessage.="Thank you,\n\n"; $emailmessage.="Web Site ROBOT\n"; $emailmessage.="(Administrator)\n"; $emailmessage.="xxx.co.uk | xxx.biz\n"; $emailmessage.="----------------------------------------------\n"; $emailmessage.="e. http://www.xxx.co.uk/contact.php\n"; $emailmessage.="w. http://www.xxx.co.uk\n"; $emailheader="From: xxx.co.uk<$emailfrom>"; $emailheader .= 'Cc: xxx@xxx.co.uk'."\r\n"; $emailheader .= 'Bcc: xxx@xxxxx.co.uk'."\r\n"; $ok=mail($emailto,$emailsubject,$emailmessage,$emailheader); mysql_close($connection); if ($ok) { header("Location: list.php"); /* Redirect browser */ exit; } else { $errmsg="There was a problem, please try later or telephone us direct."; $errsta="1"; include("edit_error.html"); //echo "<p>Mail could not be sent. Sorry!</p>"; exit; } ?> Thanks in advance I've written the script below to import a csv file into the database. I've tried to make this as secure as possible by sanitizing and validating the data, but is there anything else I could do to improve it? I'm happy with the way it works so far.
All I can think to add is a meme check via php but I can't find a way to check whether it's specifically a csv file (other than just checking the extension but that's not secure)
<?php
if ($_FILES[csv][size] > 0) {
//get the csv file
$file = $_FILES[csv][tmp_name];
$handle = fopen($file,"r");
//set the row counter
$csv_row = 0;
//loop through the csv file and insert into database
do {
// start the import
if ($data[0]) {
//add 1 the row counter
$csv_row++;
//clear errors
$errors = '';
//sanitize the inputs
$firstName = addslashes($data[0]);
$lastName = addslashes($data[1]);
$email = addslashes($data[2]);
//validate the inputs - set $errors as 'YES' if any fail
if(!ctype_alpha($firstName)){
$errors = 'YES';
}
if(!ctype_alpha($lastName)){
$errors = 'YES';
}
if(!filter_var($email, FILTER_VALIDATE_EMAIL)){
$errors = 'YES';
}
//check whether there were any errors in the validation
if($errors!=='YES'){
//insert into database
$stmt = $db->prepare("INSERT INTO test (firstName,lastName,email) VALUES (?,?,?)");
$stmt->bind_param('sss',$firstName,$lastName,$email);
$stmt->execute();
}
//there was an error, store the affected row number in the error array
else
{
$error_report[] = "There was an error on row $csv_row";
} //end error check
} //end single row import
} while ($data = fgetcsv($handle));
if($csv_row>0){
$success = 'YES';
}
} // end csv import
?>
Edited by paddyfields, 11 June 2014 - 01:06 PM. I am a developer in PHP but have run into an obstacle and was hoping for some insight. I am attempting to do something a little complicated I fear but perhaps there is a solution. A client of mine is offering CSV templates for customers to use to BATCH UPLOAD item's to their account. This is self explanatory in the fact that I know how to import a .CSV with PHP and I know how to properly insert it into the database. My question is this: What if we wanted to offer a way for the customer to insert an image into the .CSV template. By the time the .csv reached my PHP code would the image still be in a format where it could be uploaded to a directory on the server? I have not found anything on the web where anyone has tried this. Again, the customer will insert an image from their hard drive into a row within the .CSV template. My code will parse the .CSV row data and if the image is still in tact...will then upload the image to a director with more code. Thanks for any help. Joe Hi, I have a question: I have a CSV file where the first row are the names of the columns. The format of the CSV file is: Name | Surname | Adress | Date of birth Robert | Miller | Athome 1| 29. Februar 1976 . . . How should I import the CSV. Thanks in advance. This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=358188.0 include "connect.php"; if(isset($_POST['submit'])) { $filename=$_POST['filename']; $handle = fopen("$filename", "r"); while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { $import="UPDATE isc_products(prodavailability,prodinvtrack,prodcurrentinv) values('$data[1]','$data[2]','$data[3]') where vendor_id = '($data[0])' "; mysql_query($import) or die(mysql_error()); } fclose($handle); print "Import done"; } else { print "<form action='inv_update.php' method='post'>"; print "Type file name to import:<br>"; print "<input type='text' name='filename' size='20'><br>"; print "<input type='submit' name='submit' value='submit'></form>"; } ?> I am building this script to import some data via a csv that has three fields. The where statement gives me this error - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(prodavailability,prodinvtrack,prodcurrentinv) values('35','1','35') where vendo' at line 1. I'm know my syntax is flawed but can't find a solution to this.... any one point me in the right direction? I know just enough php to get myself in trouble!! lol I hope someone can help me out with this. What I would like to do is import a text file dircetly to a text area. I can't seem to find any examples on this (Yes I did search Google). I know how to upload but I'm not interested in storing files on the server - but rather just importing them directly to a textarea. Can anyone point me in the right direction? I can upload: <form enctype="multipart/form-data" action="uploader.php" method="POST"> <input type="hidden" name="MAX_FILE_SIZE" value="100000" /> Upload: <input name="uploadedfile" type="file" /><br /> <input type="submit" value="Upload File" /> </form> I can read: $myFile = "testFile.txt"; $fh = fopen($myFile, 'w') But I'm not interested in either of these as they are unnecessary steps (or are they?). Is there not a way to simply browse/import a text file directly to a textarea (or anywhere for that matter) without having to actually save the file to the server? Something like this: <form enctype="multipart/form-data" action="uploader.php" method="POST"> <input type="hidden" name="MAX_FILE_SIZE" value="100000" /> Upload: <input name="uploadedfile" type="file" /><br /> <input type="submit" value="Upload File" /> </form> //uploader.php if (isset($_POST['submit'])) { $content = $_POST['"grab the contents of the file"']; echo "<textarea>"; echo $content; echo "</textarea>"; } 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 ?> Hello I am trying to do a simple CSV import of a file and then upload to mysql. I am basing my code on an example I have found and I am getting the error message 'invalid file'. The example used includes a text box for the file import field and I am referencing the file from my computer in that box 'Users/James/name.csv' My code is as follows: HTML <form action="results.php?data=upload" method='post'> Import File : <input type='text' name='sel_file' size='20'> <input type='submit' name='submit' value='submit'> </form> PHP if ($b == 'upload') { $fname = $_FILES['sel_file']['name']; $chk_ext = explode(".",$fname); if(strtolower($chk_ext[1]) == "csv") { $filename = $_FILES['sel_file']['tmp_name']; $handle = fopen($filename, "r"); while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { $sql = "INSERT into ".$db['name']."(field1,field2,field3,field4) values('$data[0]','$data[1]','$data[2]', '$data[3]')"; mysql_query($sql) or die(mysql_error()); } fclose($handle); echo "Successfully Imported"; } else { echo "Invalid File"; } } my file format is: 1,23,1,5 1,24,2,5 1,25,3,5 1,26,4,5 1,27,5,5 1,28,6,5 1,29,7,5 1,30,8,5 1,31,9,5 1,32,10,5 If anybody can help that would be good - or perhaps point out if I am going about this the wrong way - cheers i was working on a mini project that imports csv file to the database through ajax and it's working fine
<?php
if(!empty($_FILES["marks_file"]["name"]))
{
$connect = mysqli_connect("localhost", "root", "", "dbname");
$output = '';
$allowed_ext = array("csv");
$extension = end(explode(".", $_FILES["marks_file"]["name"]));
if(in_array($extension, $allowed_ext))
{
$file_data = fopen($_FILES["marks_file"]["tmp_name"], 'r');
fgetcsv($file_data);
while($row = fgetcsv($file_data))
{
$name = mysqli_real_escape_string($connect, $row[0]);
$Physics = mysqli_real_escape_string($connect, $row[1]);
$Maths = mysqli_real_escape_string($connect, $row[2]);
$Chemistry = mysqli_real_escape_string($connect, $row[3]);
$Biology = mysqli_real_escape_string($connect, $row[4]);
$SST = mysqli_real_escape_string($connect, $row[5]);
$query = "
INSERT INTO csv
(name, Physics, Maths, Chemistry, Biology, SST)
VALUES ('$name', '$Physics', '$Maths', '$Chemistry', '$Biology' , '$SST')
";
mysqli_query($connect, $query);
}
$select = "SELECT * FROM csv ORDER BY id DESC";
$result = mysqli_query($connect, $select);
$output .= '
<table class="table table-bordered">
<tr>
<th width="25%" >name</th>
<th width="15%" >Physics</th>
<th width="15%" >Maths</th>
<th width="15%" >Chemistry</th>
<th width="15%" >Biology</th>
<th width="15%" >SST</th>
</tr>
';
while($row = mysqli_fetch_array($result))
{
$output .= '
<tr>
<td>'.$row["name"].'</td>
<td>'.$row["Physics"].'</td>
<td>'.$row["Maths"].'</td>
<td>'.$row["Chemistry"].'</td>
<td>'.$row["Biology"].'</td>
<td>'.$row["SST"].'</td>
</tr>
';
}
$output .= '</table>';
echo $output;
}
else
{
echo 'errorx';
}
}
else
{
echo "errory";
}
?>
however the imported csv files inserts null values in the tables because the format of all csv files assigned to me are in the exact same format: ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,Fields,Physics~75,Maths~50,Chemistry~65,Bio~85,SST~100 ,,,Name1,10,25,35,42,62 ,,,Name2,80,45,45,45,25 ,,,Name3,63,25,63,36,36 ,,,Name4,82,36,75,48,42 ,,,Name5,45,45,78,25,24 ,,,Name6,36,36,15,75,36 ,,,Name7,99,45,24,24,45 ,,,Name8,45,85,85,85,96 i changed my code a bit modified the functions to espace blank spaces and not return null values in the data tables
while (($row = fgetcsv($file_data, 1000, ",")) !== FALSE)
{
if ((string) $row[0] != '0' and empty($row[0]))
{
continue;
}
$name = mysqli_real_escape_string($connect, $row[0]);
$Physics = mysqli_real_escape_string($connect, $row[1]);
$Maths = mysqli_real_escape_string($connect, $row[2]);
$Chemistry = mysqli_real_escape_string($connect, $row[3]);
$Biology = mysqli_real_escape_string($connect, $row[4]);
$SST = mysqli_real_escape_string($connect, $row[5]);
$query = "
INSERT INTO csv
(name, Physics, Maths, Chemistry, Biology, SST)
VALUES ('$name', '$Physics', '$Maths', '$Chemistry', '$Biology' , '$SST')
";
mysqli_query($connect, $query);
}
But it doesn't work with the specified csv files i think the ajax call breaks and that's why nothing happens.However it works fine with csv files without blank spaces Edited March 19, 2019 by zetastreak |
|||||||