php, mysql and csv files

[tinyblob]

Hi all, my first post here so bear with me :)

I'm trying to write a quick script to insert the contents of a CSV file into a MySQL database.

I found some excellent code elsewhere [webmasterworld.com] on the forum (thanks amznVibe!), which works perfectly, however, i have a small issue.

Firstly, i want to assign a unique identifier (the primary key) to each line, this would be done with an autoincrement. Normally i would just leave that blank, and MySQL would do the rest, but in this case, that wont work. I can't leave a blank row in Access either (thats what my manager is using to create the csv file) as this just dissapears.

Does anyone have a solution to this? Perhaps i'm just being naive and missing something obvious!

I'll post the code i'm using below, so people dont have to visit the url above:

set_time_limit(5*60); 
$filename = "newstat.csv"; 
$delim =","; 
$f = fopen ($filename,"r"); 
while ($data = fgetcsv($f, 2000, $delim)) { 
while (list($key,$val) = each($data)){ 
$data[$key] = addslashes($val); 
} 
$insert = "INSERT into ".$sql_tbl." VALUES('".implode("','", $data)."')"; 
$result = mysql_query($insert) or die("error - ".mysql_error()); 
} 
fclose ($f);

[coopster]

...with INSERT [mysql.com], you can insert NULL directly to represent a missing value. If you use the MySQL command line, you can use the LOAD DATA INFILE [mysql.com] command and set NULL field values to \N

[tinyblob]

Hmm, sorry i should've been a bit clearer. I can't use the command line, this is in a shared hosting situation.
As for inserting null, i dont think this will work it's more that i need to skip a column. The code i posted seems to insert the first column from the CSV into the first column in the MySQL table. I want to ignore the first column in the MySQL table, so that the auto-increment attribute kicks in.

Maybe i should be concentrating more on trying to find a way to accomplish an empty column in access :/

[coopster]

Hey, Welcome to WebmasterWorld, tinyblob!

There, forgot to do that the first time! Want you to feel very welcome here you know!

Actually, that's what I was trying to tell you -- I insert NULL values into AUTO_INCREMENT fields all the time using the batch entry mode (LOAD DATA INFILE) via the command line. I also use NULL on my INSERT statements and the AUTO_INCREMENT kicks in like it is supposed to. Go ahead and enter NULL (without any quotations!) into your column in the spreadsheet or Access table column and you should be good to go.

[tinyblob]

Aha!
Perfect coopster, that worked a treat.

Thanks a lot, and thanks for making me feel welcome!