Syntax error (probably easy 4u..)

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

Syntax error (probably easy 4u..)

Post 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
User avatar
thomas777neo
Forum Contributor
Posts: 214
Joined: Mon Mar 10, 2003 6:12 am
Location: Johannesburg,South Africa

Post 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
pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

Post 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\'';
User avatar
thomas777neo
Forum Contributor
Posts: 214
Joined: Mon Mar 10, 2003 6:12 am
Location: Johannesburg,South Africa

Post by thomas777neo »

simply put the full path where the file name is, something like this:

c:\\\\test\\\\myfile.csv
pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

Post 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
Post Reply