Page 1 of 1

Syntax error (probably easy 4u..)

Posted: Sat Jul 30, 2005 1:12 pm
by pookie62
Hi,
I'm trying to import a csv file into MySQL table, but getting syntax error :

Code: Select all

right syntax to use near 'lid='',URL='',Vereniging='Somevillage',Adres='The Street 9'
Line form csv file:
2;"lastname";"firstname";0110101;;;"Somevillage";"The Street 9";"1111 AA";"St.Penkris";"001-56717377";"f.lastname@isp.nl";
this is the script I'm using:

Code: Select all

$fp = fopen('Deelnemer.csv', 'r');
 
// first line has column names
$data = fgetcsv($fp, 2048, ';');
$columns = array();
foreach($data as $column)
{
  $columns[] = trim($column, '"');
}
 
$sql = 'INSERT INTO testdeelnemer (';
$sql .= implode($columns, ', ');
$sql .= ') VALUES (';
 
// next lines have values
while (($data = fgetcsv($fp, 2048, ';')) !== FALSE)
{
  $checksql = "SELECT id FROM testdeelnemer WHERE id=".$data[0];
  $result = mysql_query($checksql);
  $row = mysql_fetch_row ($result);
  if ($row[0])
  {
     $sql2 = "UPDATE testdeelnemer SET ";
     $sql_clause=array();
     foreach ($data as $key=>$column)
     {  
        if ($key != 0)
        {
             $sql_clause[] = $columns[$key]."='".mysql_real_escape_string($column)."'";
        }
     }
     $sql2 .= implode (",",$sql_clause);
     $sql2 .= " WHERE ".$columns[0]." = '".mysql_real_escape_string($data[0])."'";
  } else
  {
     $sql2 = $sql;
     foreach($data as $column)
     {
       $column = mysql_real_escape_string($column);
       $sql2 .= "'{$column}', ";
     }
     $sql2 = rtrim($sql2, ', ');
     $sql2 .= ')';
     echo 'Executing: ' . $sql2 . '</br>';
  }
Anyone willing to add the line(s) that prevents this error ?
Thanks a lot :-)
Hans

Posted: Sun Jul 31, 2005 4:18 pm
by thomas777neo
Rather use mysql' import syntax, here is an example:

Code: Select all

LOAD DATA INFILE 'import_file.csv' REPLACE INTO TABLE my_table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '&quote;' LINES TERMINATED BY ' ' (field_0,field_1,field_2,field_3,field_4,field_5,field_6,field_7,field_8,field_9,field_10,field_11,field_12,field_13,field_14,field_15,field_16,field_17)
That's it, and it is less resource intensive, play around with the import options if need be

Posted: Tue Aug 02, 2005 5:30 am
by pookie62
Oke, with PHPAdmin this works.
How do I use a file un the same dir as the script ? Generated this php code:

Code: Select all

$sql = 'LOAD DATA LOCAL INFILE \'/tmp/phpESlhKf\' REPLACE INTO TABLE `testdeelnemer` '
        . ' FIELDS TERMINATED BY \';\''
        . ' OPTIONALLY ENCLOSED BY \'"\''
        . ' ESCAPED BY \'\\\\\''
        . ' LINES TERMINATED BY \'\\r\\n\'';

Posted: Tue Aug 02, 2005 5:58 am
by thomas777neo
simply put the full path where the file name is, something like this:

c:\\\\test\\\\myfile.csv

Posted: Tue Aug 02, 2005 9:53 am
by pookie62
Oke, got this thing inserting non-existing data from csv file which is already on the webserver.
But when Id exists, no update of other fields is executed..
Anyone see why not ?
If you need more info, please ask..
I really need this script to work..
Appreciate your support