double quotes removal

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

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

double quotes removal

Post 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);
  }
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

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

Post 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..
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

Code: Select all

$q .= $names[$k].'=\''.mysql_real_escape_string($fields[$k]).'\', ';
pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

Post 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 !
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

try echoing "$q" you should. show your query it will, determine your problem you can.
theda
Forum Contributor
Posts: 332
Joined: Sat Feb 19, 2005 8:35 am
Location: USA

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

It's Dutch allright ;) Or Flemish (which is a subset of Dutch :p)
pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

Post 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)
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

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

Post 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 ?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

Either a missing ; at the end of a line, or a missing } somewhere.
User avatar
protokol
Forum Contributor
Posts: 353
Joined: Fri Jun 21, 2002 7:00 pm
Location: Cleveland, OH
Contact:

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

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

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