Need some help with importing a CSV file.

[JSHINER]

<?php

$handle = fopen ( ' file.csv', 'r');

if (!$handle) {

echo 'failed to open file';

exit();

}

while ($row = fgetcsv($handle, 999)) {
					  
$db->query('INSERT INTO user (email, password, first_name, last_name, address, city, state, zipcode, company, phone) 
			VALUES (\'' . $db->escape(trim($row[0])) . '\', 
					\'' . $db->escape(trim($row[1])) . '\',
					\'' . $db->escape(trim($row[2])) . '\',
					\'' . $db->escape(trim($row[3])) . '\',
					\'' . $db->escape(trim($row[5])) . '\',
					\'' . $db->escape(trim($row[6])) . '\',
					\'' . $db->escape(trim($row[7])) . '\',
					\'' . $db->escape(trim($row[8])) . '\',
					\'' . $db->escape(trim($row[4])) . '\',
					\'' . $db->escape(trim($row[9])) . 
					'\')');

}
?>

 

The above code works but kicks an error back if there's a duplicate entry and doesn't complete the task. How can I get it to check for the email before inserting ? 

[fnairb]

Wrap your insert in a try/catch.  You can then control the behavior of the script.

 

http://uk2.php.net/exceptions

 

while ($row = fgetcsv($handle, 999)) {
  try {						  
    $db->query(...);
  }
  catch (Exception $e) {
    // silently skip the rest of the loop and continue to the next record
    continue;
  }
} 

[JSHINER]

while ($row = fgetcsv($handle, 999)) {
  try {						  
    $db->query('SELECT email FROM user WHERE email = ' . $db->escape(trim($row[0])) . '');
  }
  catch (Exception $e) {
    // silently skip the rest of the loop and continue to the next record
    continue;
					  
$db->query('INSERT INTO user (email, password, first_name, last_name, address, city, state, zipcode, company, phone) 
			VALUES (\'' . $db->escape(trim($row[0])) . '\', 
					\'' . $db->escape(trim($row[1])) . '\',
					\'' . $db->escape(trim($row[2])) . '\',
					\'' . $db->escape(trim($row[3])) . '\',
					\'' . $db->escape(trim($row[5])) . '\',
					\'' . $db->escape(trim($row[6])) . '\',
					\'' . $db->escape(trim($row[7])) . '\',
					\'' . $db->escape(trim($row[8])) . '\',
					\'' . $db->escape(trim($row[4])) . '\',
					\'' . $db->escape(trim($row[9])) . 
					'\')');

}

  }

 

I get the error: "

Fatal error: Error in database query. Query was [sELECT email FROM user WHERE email = [email protected]] and the error returned was [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@email.com' at line 1] in . . ."

[kenrbnsn]

You are incorrectly quoting the string.

 

Try:

<?php
  try {						  
    $db->query("SELECT email FROM user WHERE email = '" . $db->escape(trim($row[0])) . "'");
  }
?>

 

Also, I would use an array of fieldnames to make creating the insert query easier. My method uses the alternative insert syntax:

<?php
$flds = array('email', 'password', 'first_name', 'last_name', 'address', 'city', 'state', 'zipcode', 'company', 'phone');
while ($row = fgetcsv($handle, 999)) {
  try {						  
    $db->query("SELECT email FROM user WHERE email = '" . $db->escape(trim($row[0])) . "'");
  }
  catch (Exception $e) {
      $qtmp = array();
      for($i = 0; $i<count($flds);$i++);
          $qtmp[] = $flds[$i] . " = '" . $db->escape(trim($row[$i])) . "'";
      $q = "insert into user set " . implode(', ',$qtmp);
      $db->query($q);
   }
}
?>

 

Ken

[JSHINER]

Isn't working. Any idea why?

[kenrbnsn]

Isn't working.

doesn't tell us much. Please tell us what is happening. Put some debug statements in to see your query.

 

Ken

[JSHINER]

Just runs through fine with no errors.

[kenrbnsn]

Then you have to put in some debug code (echos) in critical areas to see what's going on and why the code isn't doing whay you think it should.

 

Ken

[JSHINER]

When I echo the try query I get "Resource id #7"

[discomatt]

This may seem like a jerk thing to say.. but I suggest learning to walk before you try to run

[JSHINER]

Haha my mind is elsewhere sorry this is just something I'm trying to get done quick.