Insert or Update table 1

[pookie62]

Hi,
Got this script, but now when the data is in the table (id)
nothing happens, I would like the record to be updated with the new values from csv file.
Anyone can help me write this ?
This is what I have:

 mysql_select_db($database_ASN, $ASN);
  
$fp = fopen('UploadedMatch.csv', 'r');
 
// first line has column names
$data = fgetcsv($fp, 2048, ';');
$columns = array();
foreach($data as $column)
{
  $columns[] = trim($column, '"');
}
 
$sql = 'INSERT INTO testtable (';
$sql .= implode($columns, ', ');
$sql .= ') VALUES (';
 
// next lines have values
while (($data = fgetcsv($fp, 2048, ';')) !== FALSE)
{
  $sql2 = $sql;
 
  foreach($data as $column)
  {
    $column = mysql_real_escape_string($column);
    $sql2 .= "'{$column}', ";
  }
  $sql2 = rtrim($sql2, ', ');
 
  $sql2 .= ')';
  echo 'Executing: ' . $sql2 . '</br>';
  mysql_query($sql2) or print(mysql_error() . '<br>');
  }
fclose($fp);
?>

 

[sysadmin42]

try this link: [URL unfurl="true"]http://dev.mysql.com/doc/mysql/en/update.html[/url]

 

[WoodyRoundUp]

How's your table structure?
And how's your CSV structure?
Please post so we can fix.

 

[pookie62]

Hi Woodyroundup,
Thanks for replying..
Hope this helps..

Table structure:
`Id` bigint(20) NOT NULL auto_increment,`Naam` varchar(255) default '',`ASNNR` bigint(20) ,`Datum_lid` datetime ,`URL` varchar(255) ,`Vereniging` varchar(255) ,`Adres` varchar(255) , `Postcode` varchar(255) ,`Woonplaats` varchar(255) ,`Telnr` varchar(255) ,`Email` varchar(255) ,
`Oud_Sefnr` varchar(255) ,PRIMARY KEY (`Id`)) ENGINE=MyISAM;

csv structure:
"Id";"Naam";"Voornaam";"ASNNR";"Datum_lid";"URL";"Vereniging";"Adres";"Postcode";"Woonplaats";"Telnr";"Email";"Oud_Sefnr"
20;"Somelastname";"Hans";6206055;;"

http://www.website.nl/";"Friendly

name site";"Some address 1";"1111 AA";"Town";"0001-222222";"mail@isp.nl";

 

[excession]

one thing that may help track down the problem is to print you sql string and check the syntax is correct.

It looks to be like you may have an extra comma at the end of the the set of values but I've only briefly looked at the code.

 

[pookie62]

Hi Excession,

I do believe the syntax is oke, when I use the INSERT query as in the first post, the data is inserted.
What I need is an extra line of php code (which I cannot write myself) which updates the existing data. (all fields)

REPLACE isn't an option either, because when the data is not in the table, there's nothing to replace and nothing is inserted.

So I feel like I have to make two statements:
First check if data exists > if yes > update all fields with csv data
If not exist > insert new records

The Insert is in the code, now I'm looking for these few extra lines of php code in my script..
Appreciate all your help, guys !!
Hans

 

[WoodyRoundUp]

Hi pookie62.
I will get back to you soon.

 

[WoodyRoundUp]

Try this.

mysql_select_db($database_ASN, $ASN);
  
$fp = fopen('UploadedMatch.csv', 'r');
 
// first line has column names
$data = fgetcsv($fp, 2048, ';');
$columns = array();
foreach($data as $column)
{
  $columns[] = trim($column, '"');
}
 
$sql = 'INSERT INTO testtable (';
$sql .= implode($columns, ', ');
$sql .= ') VALUES (';
 
// next lines have values
while (($data = fgetcsv($fp, 2048, ';')) !== FALSE)
{
  $checksql = "SELECT id FROM testtable WHERE id=".$data[0];
  $result = mysql_query($checksql);
  $row = mysql_fetch_row ($result);
  if ($row[0])
  {
     $sql2 = "UPDATE testtable 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>';
  }
  mysql_query($sql2) or print(mysql_error() . '<br>');
  }
fclose($fp);
?>

 

[pookie62]

Hi WoodyRoundUp,
Thanks a lot ! Looking nice.. Unfortunately I can't try it right away due to problems with the isp..
But as soon our site is up again (expect this to be in a couple of days MAX) I'll try the script and let you know the result, oke ?
Thanks again, mate !!

 

[pookie62]

Hi Woody
Your script is running perfect !!!
Thanks very much, mate !

 

[WoodyRoundUp]

Not a problem.

 

[pookie62]

Back again..
This script is working for a couple of tables, not for the 'klassement' table.
There is no primary key in this table, so the check is not needed.
I tried to adjust the script with Truncate table, and DELETE * but now nothing is inserted !?
Don't understand why not..
Anyone can help me ?
Thanks..