[Resolved] [Resolved] Clearing a database

jeffgman

I want to create an order sheet in PHP which grabs the available items from a MySQL database. I should be able to figure that part out. But, I want to create an upload script which will take a tab or comma delimited format with all of the items and refresh the database periodically. How would I clear out the existing database to insert this new file with all of the items in it?

Thanks,
Jeff

Pig

Deletion is easy. DELETE * FROM table_name;

To add the contents back in, you can use fgetcsv (http://us4.php.net/manual/en/function.fgetcsv.php), to get an array from the CSV. Then loop through the CSV running mysql_query()'s.

Roger_Ramjet

Just a point: from MySQL Manual at

http://dev.mysql.com/doc/mysql/en/Insert_speed.html

When loading a table from a text file, use LOAD DATA INFILE. This is usually 20 times faster than using a lot of INSERT statements. See section 14.1.5 LOAD DATA INFILE Syntax.

http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html

So, I prefer to use LOAD DATA INFILE rather than looping through an array INSERT ing 1 row at a time. It is also simpler to code one SQL statement for execution rather than building an array and then the loop.

Just an idea.

jeffgman

Originally posted by Roger Ramjet
Just a point: from MySQL Manual at

http://dev.mysql.com/doc/mysql/en/Insert_speed.html

When loading a table from a text file, use LOAD DATA INFILE. This is usually 20 times faster than using a lot of INSERT statements. See section 14.1.5 LOAD DATA INFILE Syntax.

http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html

So, I prefer to use LOAD DATA INFILE rather than looping through an array INSERT ing 1 row at a time. It is also simpler to code one SQL statement for execution rather than building an array and then the loop.

Just an idea.

Great. Thanks for the suggestion. When I get to that point, I will definitely look at the LOAD DATA INFILE command.

I just need to figure out my mail() problem.

Jeff