Reading CSV to database with condition

I want to read some csv values(>1 line) from an uploaded file to a database,
in this format:

123456,John Bull,john@bull.info

with a code like this:

while (($data =fgetcsv($handle, 1000, ",")) !== FALSE)
         {
            $sql = "INSERT into contacts(number,name,email) values('$data[0]','$data[1]','$data[2]')";
            mysql_query($sql) or die(mysql_error());
         }

The challenge is i want to avoid multiple numbers being inserted to the
number(first) column with a trick like this:

“123456,78990”,John Bull,john@bull.info

What is the smart way to do this?

Thanks.

You can check first column before insert:


while (($data =fgetcsv($handle, 1000, ",")) !== FALSE) 
         { 
            [COLOR="Red"]if (!is_int($data[0])) continue;[/COLOR]
            $sql = "INSERT into contacts(number,name,email) values('$data[0]','$data[1]','$data[2]')"; 
            mysql_query($sql) or die(mysql_error()); 
         }  
}

Thanks, this should work for this case but what of a general case
where the column in question can contain strings.

You should be escaping all input data. Go read up on SQL Injection as your code is rather vulnerable. Then go learn about mysql_real_escape_string().

SQL injection issues aside, I see a number of additional problems with the code. I work with CSV -> DB inserts all the time and you should:

  • Check for an empty array - empty blank lines crop up all the time.
  • Not pass fgetcsv() anything more than the $handle.
  • Not die()/exit() on failed insertions - let the database reject the entry, display the error by 'echo’ing it, but then continue on to the next line.

I also prefer ‘INSERT INTO contacts SET number = …, name = …, email = …’ instead of the long method. But that is more for aesthetics/simplicity with really long queries than anything else. Makes it a ton easier to match parameter counts (i.e. I don’t have to count 15 fields and then make sure there are 15 corresponding ‘values’ in the second set of parenthesis).