CSV insert

[bigtall]

I'm working on a stat database for a baseball league and need some help. Now that the table creation problems are out of the way I'm looking at having to insert a record for each player into a few different tables. I've been scouring the forums for a way to get the CSV files to be entered into the table, but I can't get a grip on what it is that I have to do as far as coding. The CSV files that I have all have data in this format:

Alcantara, Israel, 2003, MIL, 76, .276, 301, 83, 13, 1, 2, 38, 53, 7, 6, 46, 35, .346, .361

I've done manual inserts using:

$query="INSERT INTO hitting (playername, year, team, g, avg, ab, h, doub, trip, hr, bb, k, sb, cs, r, rbi, slg, obp) VALUES ('Alcantara,Israel', 2003, 'MIL', 76, .276, 301, 83, 13, 1, 2, 38, 53, 7, 6, 46, 35, .346, .361)";

mysql_query($query);

Since I'm going to be doing massive updates the the tables I'm going to need to find a more efficient way to accomplish the inserting of records. Even if it was as simple as showing me how to parse the above data so I could do a generic insert like:

$query="INSERT INTO hitting (playername, year, team, g, avg, ab, h, doub, trip, hr, bb, k, sb, cs, r, rbi, slg, obp) VALUES ('$data[1]','$data[2]', '$data[3]', '$data[4]', '$data[5]', '$data[6]', '$data[7]', '$data[8]', '$data[9]', '$data[10]', '$data[11]', '$data[12]', '$data[13]', '$data[14]', '$data[15]', '$data[16]', '$data[17]', '$data[18]')";

I'm obviously quite the newbie so any help would be much appreciated!

[bigtall]

I changed the file to a .txt and then used the following code:

$file="milbat.txt";

$query = mysql_query("LOAD DATA INFILE '$file' INTO TABLE hitting (playername, year, team, g, avg, ab, h, doub, trip, hr, bb, k, sb, cs, r, rbi, slg, obp)");

mysql_query($query) or die (mysql_error());

The error that came up said "Query is empty"\

Any ideas?

[Sxooter]

When I have to import csv, I use PHP to wrangle the data first with fgetcsv(), one of the greatest functions ever created.

$file = "import.csv";
$fp = fopen($file,"r");
while (!feof($fp)){
  $parts = fgetcsv($fp,4096);
  $query = "insert into table values ("';
  $query.= implode("' ,'",$parts);
  $query.="')";
  EXECUTE QUERY HERE
}

[bigtall]

Ah thanks! That got me going 😃