Making code more efficient?

[galvin]

This question includes MySQL, but I have a feeling the answer will involve PHP so putting it here for now. If it should be moved, feel free

 

Say I have a for loop that iterates through a certain amount of times and updates a value in my database EACH ITERATION thru the loop. In other words, we're connecting to the database multiple times.  Seems very inefficient, so I'd like to see if there is a better way.

 

Here is a basic example of the "bad" way (FYI, this code is basically reordering items after one of them moves down in the list)...

 

for ($a=$oldvalue+1; $a<=$othervalue; $a++) {
			$sql = "UPDATE tablewithids SET
				id=id - 1
				WHERE id=$a";
			$result = mysql_query($sql, $connection);
			if (!$result) { 
			die("Database query failed: " . mysql_error());
			} else {

			}

 

So if $othervalue happened to be 80, then it would be 80 separate connections to the database!  I imagine it would make a lot more sense to gather the data first (into an array I presume) and then make one connection to the database an update that way, but I'm having trouble wrapping my head around how to do it based on this type of example.

 

Can anyone offer suggestions to help make this type of for loop code more efficient?

 

[PaulRyan]

You should look at BETWEEN for MySQL - http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between

 

Regards, PaulRyan.

[shlumph]

Sure, just include all cases that need updating in your WHERE clause.  Something like:

UPDATE tablewithids SET id=id - 1 WHERE id BETWEEN $oldvalue AND othervalue";

OR

UPDATE tablewithids SET id=id - 1 WHERE id <= $oldvalue AND id >= othervalue";

etc.

 

Also, if you need to run a query in a loop, it is far more efficient making everything in a transaction if your DB engine supports it (innodb).

mysql_query('START TRANSACTION');
while($looping) {
    //Update or insert statements here
}
mysql_query('COMMIT');

[markjoe]

If you database engine does not support transactions, you can build larger queries and then update them.

I've had to do this in the past where a script was updating thousands of records in a loop. I chucked the queries into 100, I think, and it ran much faster.

[galvin]

Thanks guys, BETWEEN looks like it will work for what i need!!