PHP and SQL Help

[tapupartforpres]

Hello.  I was wondering if someone could point me in the right direction. 

 

I have an .csv file with about 3.1 million names.  In the .csv file I have 2 columns:  zip code and a form id number.  Depending on the ID number of the document, it would preview so they know they have the right form.

 

What I need it to do is to pull the number of records from the zip code and display them.  So for zip code 23313 it should show that there are say 2,000 records.  Then they would select the form they need those records printed on.  It would then return a preview of that form.  If they approve the count and the form, I would need to see if I can either 1) have that list emailed or 2) have the list stored somewhere for download.  I would also need the form number emailed so we know which form to print it on.

 

Obviously I need to get the .csv file into the SQL database.  But what steps should be taken next?

 

Any ideas?  Thanks

[Psycho]

You are asking a lot of questions in there. I would suggest you take one part at a time and ask specific questions as needed. Getting 3 million records from a flat file into the database is the first problem to overcome. Normally I would use file() to read the file into an array. And then process it for a INPUT statement.

 

If the zip ode and form id were separated on each line by a comma I would do something like this:

 

$file="the_file.csv";

$data = file($file);

//Process data
foreach ($data as $record)
{
    $record_data = explode(',', $record);
    $insert_records[] = "('".trim($record_data[0])."', '".trim($record_data[1])."')";
}

//Create query string
$query = "INSERT INTO table (ssn, form_id)
          VALUES " . implode(',' , $insert_records);
//Run query
mysql_query($query) or die (mysql_error());

 

However, with 3 million records you will most likely run into problems. You may need to find a workaroound to process the file in pieces. But give that a try to see if it will even process that many records. Would be a good idea to track system resources during the process to understand the load on your server.

[tapupartforpres]

Thanks I appreciate it.  Just needed a starting point.  I'll test out a list first and come back.  I was reading more into it and someone suggested using a ALTER TABLE command.  Have no idea about that one, but I will be back.  Thanks

[Mchl]

Don't use INSERT for loading such amount of data from CSV. It will take ages and the script is likely to timeout anyway.

 

Use LOAD DATA INFILE instead

[tapupartforpres]

Thanks Mchl.  Do you have any other suggestions on how to actually process this with .php.  Sorry I am a very, very basic .php guy.  Any other suggestions would be great.  Thanks