Page 1 of 2

double quotes removal

Posted: Tue Jul 12, 2005 2:33 pm
by pookie62
Hi,
When testing this script worked fine.Now I'm a step further and the csv file (actual export form MSAccess) has several fields in it (not all fields).
How can I remove those quotes so the import in MySQL does work ?

Code:

Code: Select all

<?php
  mysql_select_db($database_ASN, $ASN);
  
$csv = file('UploadedMatch.csv'); //this puts each line of the file into it's own element of the array &quote;$csv&quote;; $csv&#1111;0] is the field names, $csv&#1111;1] is the first row of data, etc.
  
  $count = count($csv);
  $names = explode(';', $csv&#1111;0]); // first line.
  for ($x = 1; $x<$count; $x++)
  {
    $q = 'REPLACE INTO testtable SET ';
  
       $fields = explode(';', $csv&#1111;$x]);
      foreach ($fields as $k=>$col)
      {
          $q .= $names&#1111;$k].'=\''.$fields&#1111;$k].'\', '; 
      }
      $q = substr($q, 0, -2); // get rid of the last &quote;, &quote;.
  
      mysql_query($q);
  }

Posted: Tue Jul 12, 2005 3:01 pm
by Burrito

Posted: Tue Jul 12, 2005 3:17 pm
by pookie62
I read all these pages, but still I don't know how to use it for my own purpose.
Still too much newbie, I guess..
How should I write this ?
Appreciate your support..

Posted: Tue Jul 12, 2005 3:20 pm
by Burrito

Code: Select all

$q .= $names[$k].'=\''.mysql_real_escape_string($fields[$k]).'\', ';

Posted: Wed Jul 13, 2005 1:54 am
by pookie62
Hi Burrito,
Thanks for your reply, but it still doesn't import the data.
Here's a few lines from the csv file:

First line are Fieldnames (matching table layout)
"Id";"Naam";"Voornaam";"ASNNR";"Datum_lid";"URL";"Vereniging";"Adres";"Postcode";"Woonplaats";"Telnr";"Email";"Oud_Sefnr"
20;"Somelastname";"Hans";6202051;;"http://www.somewebsite.nl/";"Website titel";"Somestreet 12";"4578 AA";"Sometown";"010-124578";"mail@isp.ds";
82;"Nextlastname";"Hans";6105851;;"http://www.otherwebsite.nl/";"blabla";"Otherstreet 1";"7124 BB";"OtherTown";"001-555555";"email@mail.nl";"3000001"

Is there also a possibility to show what has been updated/inserted ?
Now the browserscreen is empty, for future users would be nice to show them what has happened.
Appreciate your help !

Posted: Wed Jul 13, 2005 10:43 am
by Burrito
try echoing "$q" you should. show your query it will, determine your problem you can.

Posted: Wed Jul 13, 2005 3:39 pm
by theda
Not to be uncontributing to this thread, but I was just reading this down and noticed the Norwegian (or is it Dutch?)... Voorname! reminds me of German (Vorname). Kudos for the scripter for his choice of languages =D

Posted: Wed Jul 13, 2005 5:31 pm
by timvw
It's Dutch allright ;) Or Flemish (which is a subset of Dutch :p)

Posted: Wed Jul 13, 2005 6:31 pm
by pookie62
Oke, echo'd ($q) and I think I understand the problem.
For all fields the REPLACE INTO is set instead off INSERT.
Since this testing table is empty there's nothing to replace..

So, if data is not in the table at all, INSERT query should go first.
How do I do that in this PHP script ?

It is Dutch indeed.. :-)
(Wow !, I solved a question..LOL)

Posted: Wed Jul 13, 2005 7:16 pm
by timvw

Code: Select all

<?php

mysql_connect('localhost', 'username', 'password');
mysql_select_db('databasename');

$fp = fopen('test.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 echo(mysql_error() . '<br>');
}
fclose($fp);
?>

Posted: Thu Jul 14, 2005 4:18 am
by pookie62
Hi Timvw,
Thanks a lot for your support, but now I get this error:
parse error, unexpected T_ECHO on line 33
Ideas what is wrong ?

Posted: Thu Jul 14, 2005 4:19 am
by onion2k
Either a missing ; at the end of a line, or a missing } somewhere.

Posted: Thu Jul 14, 2005 8:10 am
by protokol
The error is on line 34:

Code: Select all

mysql_query($sql2) or echo(mysql_error() . '&lt;br&gt;');
This should be changed to:

Code: Select all

mysql_query($sql2) or print(mysql_error() . '&lt;br&gt;');
The reason is because echo is not a function.

Posted: Thu Jul 14, 2005 11:28 am
by pookie62
Sorry for being such a pain, Have I thanked you all for supporting ? THANKS !
Inserting is fine now but...

Other question: Now when a certain Id is in the table, nothing is updated.
Basic idea was to update where needed, and to insert where not in the table.

Anyone feels like adjusting this for me ?
Really appreciate, this is a great forum !

Hans

Posted: Thu Jul 14, 2005 1:34 pm
by timvw
You could have a look at http://dev.mysql.com/doc/mysql/en/replace.html and change the generated sql...

Or you could simply put a unique index on the rows.. And ignore the error that mysql raises when you try to insert a row with an index that already exists..