a better way to do this? MySQL in a loop for many items

[StaticFX]

i have a page set up so we can edit shop items… names, desc, categories, prices etc

when you save, it loops through every item and runs update queries on each
there are about 45 items right now…

[PHP]
if ($mode == ‘update’) {
$result = mysql_query(“SELECT * FROM products ORDER BY id”);
if($result) {
while($row = mysql_fetch_array($result)){
$categories = ‘’;
foreach ($cats as $key => $value) {
$pid = $row[‘id’] . ‘_’ . $key;
$categories = $categories . (($_POST[$pid] != ‘’) ? ‘(’ . $row[‘id’] . ‘,’ . $_POST[$pid] . ‘),’ : ‘’);
}
$categories = substr($categories, 0, -1);
$name = htmlentities($_POST[$row[‘id’] . ‘_name’]);
$desc = htmlentities($_POST[$row[‘id’] . ‘_desc’]);
$bp = $_POST[$row[‘id’] . ‘_base’];
$dp = $_POST[$row[‘id’] . ‘_deluxe’];
$pp = $_POST[$row[‘id’] . ‘_premium’];

				mysql_query("UPDATE products SET name='" . $name . "', description='" . $desc . "',base_price=" . $bp . ",deluxe_price=" . $dp . ",premium_price=" . $pp . " WHERE id=" . $row['id']);
				mysql_query("DELETE FROM prodcats WHERE prod_id=" . $row['id']);
				mysql_query("INSERT INTO prodcats (prod_id,cat_id) VALUES " . $categories . ";");

			}
		}

[/PHP]
while it DOES work… i wonder if there is a better way. 2 tables… products that has the id, prices, desc, name, etc and the prodcat which has a list of the product id and category id.

so essentially it loop through each item updates, then updates the prodcats table… 45+ times in a row. I can see this failing. Help! thanks!

[ErnieAlex]

Here is a link I came across that discusses a few ways to speed up your updates.
One item that might work for you is to update it “DELAYED”. Which would let the program go on and return to you faster while the updates are still going on behind the scenes. Another way is to use bulk updates where you are not updating one row at a time. There are several links on this page to other links that talk about 32 ways of speeding up large update.

Another issue is what is actually stored in the fields. If you can store an INT it is much faster than TEXT.
Changing the data in your fields can speed it up, too… So, if you have a table of products and you use the actual name in other tables, it would be faster to assign a product id field and store that instead. Hope that made sense!

Anyway, here is the link, hope it helps…
http://stackoverflow.com/questions/3952288/speeding-up-large-numbers-of-mysql-updates-and-inserts