00stuff Posted June 3, 2011 Share Posted June 3, 2011 I created a html/php page that uploads a csv file to a mysql database but when I upload the file with the form it only uploads 252 lines of the document and gives me an error message on the webpage. 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 'S US #1 FRONTERA FRESH U.S.A','2087658','1.11431E+11','Y','NBWLSLN132N1','ctn','' at line 1 Page with php: <html> <head> <title> Upload Inventory File </title> </head> <body> <h1>Upload Inventory File</h1> <?php include('menu.php'); ?> <?php include "connect.php"; if(isset($_POST['submit'])) { $filename=$_POST['filename']; $handle = fopen("$filename", "r"); while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { $import="INSERT into inventory_file(id,location,description,tag_number,tag_number_2,letter,lot_number,bin,time_stamp,number_1,number_2,number_3,inventory_description,inventory_description_2,date_2,highlight) values(' ','$data[0]','$data[1]','$data[2]','$data[3]','$data[5]','$data[6]','$data[7]','$data[9]','$data[11]','$data[12]','$data[13]','$data[14]','$data[15]','$data[16]','0')"; mysql_query($import) or die(mysql_error()); } fclose($handle); print "Import done"; } else { print "<form action='uploadinventoryfile.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>"; } ?> </body> </html> All the files including the csv are on the root folder. I attached the csv file. [attachment deleted by admin] Quote Link to comment https://forums.phpfreaks.com/topic/238282-having-trouble-uploading-csv-file-to-mysql-with-php/ Share on other sites More sharing options...
PFMaBiSmAd Posted June 3, 2011 Share Posted June 3, 2011 You must escape string data that is put into a query so that sql special characters (a ' in this case) don't break the syntax of the query. I recommend using array_map with the mysql_real_escape_string function to escape all the data in your $data array at once - $data = array_map('mysql_real_escape_string',$data); Quote Link to comment https://forums.phpfreaks.com/topic/238282-having-trouble-uploading-csv-file-to-mysql-with-php/#findComment-1224479 Share on other sites More sharing options...
00stuff Posted June 3, 2011 Author Share Posted June 3, 2011 could you show me how to use that with the code I already have. I tried adding it in my code and was not able to make it work. Quote Link to comment https://forums.phpfreaks.com/topic/238282-having-trouble-uploading-csv-file-to-mysql-with-php/#findComment-1224483 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.