[SOLVED] import csv data into mysql

[yanti]

hi guys...i hv this code which import csv data into mysql....but a little bit problem.....i got an error during runtime saying that MySql syntax error which i cant fix it....hope anyone can help me....

for ur information i got more than 40000 rows of data, but it can insert only 9 rows of it in database then error appear on webpage....]

 

if(isset($_POST['submit']))
   {
     $filename=$_POST['filename'];
     $handle = fopen("$filename", "r");
     while (($data = fgetcsv($handle, 100000, ",")) !== FALSE)
     {
    
       $import="INSERT into kmmb_member1(no_ahli,no_pin,nama,no_ic_baru,no_ic_lama) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')";
       mysql_query($import) or die(mysql_error());
     }
     fclose($handle);
     print "Import done";
   }
   else
   {

      print "<form action='import.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>";
   }
   

???  ???  :-\

[deepson2]

Try this code:

 

if ((isset($HTTP_POST_VARS["MM_update"])) && ($HTTP_POST_VARS["MM_update"] == "form1")) {    //form1 is form name
// yourfile.csv must be in the same directory as this php file
$handle = fopen ("Book1.csv","r");         //this ll be your file name
   
while ($data = fgetcsv ($handle, 100000, ",")) {
            
           $no_ahli = $data[0];
    $no_pin = $data[1];
    $nama = $data[2];
            $no_ic_baru = $data[3];
            $no_ic_lama = $data[4];

       $import="INSERT INTO p_b (no_ahli,no_pin,nama,no_ic_baru,no_ic_lama) values ('".$no_ahli."','".$no_pin."','".$nama."','".$no_ic_baru."','".$no_ic_lama."')";

 

check this code and let us know. what happens 

[Mchl]

Telling us what the actual error message was would also help.

[yanti]

the error is like this...

 

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 '','','02159002')' at line 1

[yanti]

HI deepson2...

 

i tried ur code...it doesnt give any error...but still not insert all rows in csv data. it just entered until row 9....

[yanti]

 

 

hey guys, now i know why i got the error like that.....

because in my csv data there is a value using like this....(Dato')

 

that's why the error appears...coz mysql cant detect it....

 

am i correct? i think so....

[Mchl]

Run mysql_real_escape_string on values to be inserted into query.

[yanti]

hi Mchl...

sorry, but where to put that line of code? can u please show me?

thanks.

[Mchl]

Something like this:

 

$no_ahli = mysql_real_escape_string($data[0]);
$no_pin = mysql_real_escape_string($data[1]);
$nama = mysql_real_escape_string($data[2]);
$no_ic_baru = mysql_real_escape_string($data[3]);
$no_ic_lama = mysql_real_escape_string($data[4]);

$import="INSERT INTO kmmb_member1 (no_ahli,no_pin,nama,no_ic_baru,no_ic_lama) values ('$no_ahli','$no_pin','$nama','$no_ic_baru','$no_ic_lama')";

[yanti]

 

hey..thanks everyone...finally...it solved....