Handling a large number of inserts on a mysql table

captainmerton · 07-16-2009, 11:30 AM

I need to cater for a situation where i want to process multiple inserts onto a mysql table. There are 4 columns on the table and 2 of the columns will always take the same data for the insert eg.

column1, column2, column3, column4
joebloggs,london,1234,ABC
joebloggs,london,1564,Akfr
joebloggs,london,19354,kjf
joebloggs,london,1354,AHC

Is there a mysql command i can use to do the insert as one package of work or do I need to loop in the php code until all the inserts are done?

If the latter, i'd need to checkpoint and commit. is this easy to do?

tony · 07-16-2009, 01:43 PM

You can loop to concatenate the query string and then query at the end, that way there is not a lot of sequential queries, but just one.

PHP Code:


			
$query = 'INSERT INTO thetable(column1, column2, column3, column4) ';

//$assuming the data is comming from a db query.

while ($row = mysql_fetch_assoc($rs)) {

    $query .= 'VALUES('.$row['name'].', '.$row['city'].', '.$row['special_number'].', '.$row['code'].') ';

}

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

captainmerton · 07-16-2009, 02:01 PM

Thats exactly what i was looking for. Cheers tony.

Village Idiot · 07-16-2009, 02:02 PM

Making it into one query will make it run far faster. It will work for methods like this, but PHP does not allow more than one command per mysql_query. I consider this one of PHPs weak points since as you get more advanced with SQL, multiple queries become the best way to do things.

JaoudeStudios · 07-16-2009, 02:25 PM

Quote:

Originally Posted by Village Idiot

...but PHP does not allow more than one command per mysql_query. I consider this one of PHPs weak points since as you get more advanced with SQL...

You should not be using the MySQL library in PHP anymore, but the MySQLi which has been out for a while. PHP.net has deprecated MySQL api and insists on using the newer version MySQLi (stands for MySQL improved) - it has great benefits in speed, security etc...

MySQLi also allows multiple queries and much more. However note there is a downside to multiple queries, it is more vulnerable! So make sure you protect your queries well!

Village Idiot · 07-16-2009, 02:55 PM

I haven't used PHP myself for about seven months, so I guess I'm out of date. I'll keep that in mind, thanks.

tony · 07-16-2009, 03:09 PM

I have meant to dive into mysqli, but I haven't used php for a while. for some reason the recent projects I am working on have a windows server with no php install, so only .NET for now.