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 Code: [select]

 

         

 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 . ";");

			}
		}

 

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!

[awjudd]

Your DELETE and INSERT are unnecessary, you could likely get away with an INSERT / UPDATE on duplicate query instead.

 

I wouldn't iterate through all of the products n your products table because that is pointless.  Instead I would figure out which ones were affected in your $_POST (i.e. you know all fields are named >id<_name, so grab the list of all >id<) and then just process those.

 

~awjudd