Updating More Than One Category

[son]

On a product update form there is an option to change categories. User can select via multiple field which categories they want. If they select new ones those should be added and if they by doing so, de-selected the ones they had before those should be deleted from database and the relevant ordering number (usort) of items with higher number be changed. So far, so good. Only: When I select more than one category it will always only take the first category. Going from one category to another one works. The problem only comes when you select more than one to change.The relevant code is:

if(count($category) > 0 && $category != 0)					{					$q2 = "SELECT category_id FROM productCat WHERE product_id = $pid";					$r2 = mysqli_query($dbc, $q2);					    while (list($category_id2) = mysqli_fetch_array($r2, MYSQLI_NUM))					    {						if($category != 0 && !in_array("$category_id2", $category))						{	                                        // delete product_id from productCat and re-order relevant categories                                                $usort_query = "SELECT usort, category_id FROM productCat WHERE product_id = $pid";                                                $usort_result = mysqli_query ($dbc, $usort_query);	                                                if (mysqli_num_rows($usort_result) > 0)                                                        {		                                           while ($row = mysqli_fetch_array ($usort_result, MYSQLI_ASSOC))                                                           {		                                           $usort = $row['usort'];		                                           $cat = $row['category_id'];				                           // first delete all exiting entries for pid in productCat		                                           $del_q2 = "DELETE FROM productCat WHERE product_id = $pid AND category_id = $cat";		                                           $del_r2 = mysqli_query ($dbc, $del_q2);				                           // re-order items in relevant categories		                                           $del_q2b = "UPDATE productCat SET usort = usort - 1 WHERE category_id = $cat AND usort > $usort";		                                           $del_r2b = mysqli_query ($dbc, $del_q2b);						           }                                                         }				                           // insert items in relevant new categories	$sql = "INSERT INTO productCat (usort, category_id, product_id) VALUES ";		foreach ($category as $v)		{		$usort_query2 = "SELECT usort FROM productCat WHERE category_id = $v ORDER BY usort DESC";		$usort_result2 = mysqli_query ($dbc, $usort_query2);		$row=mysqli_fetch_assoc($usort_result2);		$usort2 = $row['usort'];		$usort2 = $usort2 + 1;    	$sql .= "('$usort2', '$v', '$pid'),";		}	$sql = substr($sql,0,-1);$sqlRes = mysqli_query ($dbc, $sql);                                                }         }

I have also tried to close while (list($category_id2) = mysqli_fetch_array($r2, MYSQLI_NUM))andif($category != 0 && !in_array("$category_id2", $category))before$sql = "INSERT INTO productCat (usort, category_id, product_id) VALUES ";but this then entered all categories regardless if the category was already existing for current product.Any ideas where I am going wrong?Son

[justsomeguy]

Where does $category come from? What does the form look like?

[son]

Where does $category come from? What does the form look like?

The html is:

<p><label for="category">Relevant categories*</label><br /><select name="category[]" id="category[]" multiple="multiple" size="5"><option value="1">Beds</option><option value="2">Mirrors</option><option value="3">Sofas</option></select></p>

but I do not see a problem with form element as it is the same as the working add product form and there is no problem selecting more than one category from form. In addition, I have changed code to (see the change of brackets position which I made bold):

if(count($category) > 0 && $category != 0){$q2 = "SELECT category_id FROM productCat WHERE product_id = $pid";$r2 = mysqli_query($dbc, $q2);while (list($category_id2) = mysqli_fetch_array($r2, MYSQLI_NUM)){if($category != 0 && !in_array("$category_id2", $category)){// delete product_id from productCat and re-order relevant categories$usort_query = "SELECT usort, category_id FROM productCat WHERE product_id = $pid";$usort_result = mysqli_query ($dbc, $usort_query);if (mysqli_num_rows($usort_result) > 0){while ($row = mysqli_fetch_array ($usort_result, MYSQLI_ASSOC)){$usort = $row['usort'];$cat = $row['category_id'];// first delete all exiting entries for pid in productCat$del_q2 = "DELETE FROM productCat WHERE product_id = $pid AND category_id = $cat";$del_r2 = mysqli_query ($dbc, $del_q2);// re-order items in relevant categories$del_q2b = "UPDATE productCat SET usort = usort - 1 WHERE category_id = $cat AND usort > $usort";$del_r2b = mysqli_query ($dbc, $del_q2b);}}[b]}}[/b]// insert items in relevant new categories$sql = "INSERT INTO productCat (usort, category_id, product_id) VALUES ";foreach ($category as $v){$usort_query2 = "SELECT usort FROM productCat WHERE category_id = $v ORDER BY usort DESC";$usort_result2 = mysqli_query ($dbc, $usort_query2);$row=mysqli_fetch_assoc($usort_result2);$usort2 = $row['usort'];$usort2 = $usort2 + 1;$sql .= "('$usort2', '$v', '$pid'),";}$sql = substr($sql,0,-1);$sqlRes = mysqli_query ($dbc, $sql);

which means that the insert query is out of while loop before. Now it inserts into as many categories as I select, but it also enters again for categories it is already entered it (so you would have it twice if you select the same category as it is in with another one).Tried all afternoon to insert some sort of check that says that it should only insert if there is no entry for category yet, but without luck...Son

[justsomeguy]

It sounds like you aren't deleting everything that needs to be deleted. When I do that stuff I normally delete everything and then add it all back instead of trying to figure out what needs to be added and what doesn't.

[son]

It sounds like you aren't deleting everything that needs to be deleted. When I do that stuff I normally delete everything and then add it all back instead of trying to figure out what needs to be added and what doesn't.

Unfortunately, this is not an option as client orders products and when you delete an existing entry for a category that should stay as it is, then he would loose the correct order for this item. Otherwise, I would agree. Is there no way to check something like (is still entering existing category/product combinations again which I do not get as I thought I excluded those with SELECT category_id FROM productCat WHERE category_id != $v):

	$sql = "INSERT INTO productCat (usort, category_id, product_id) VALUES ";		foreach ($category as $v)		{		$catExist_query = "SELECT category_id FROM productCat WHERE category_id != $v";		$catExist_result = mysqli_query ($dbc, $catExist_query);	        	if (mysqli_num_rows($catExist_result) > 0)                	{			$usort_query2 = "SELECT usort FROM productCat WHERE category_id = $v ORDER BY usort DESC";			$usort_result2 = mysqli_query ($dbc, $usort_query2);			$row=mysqli_fetch_assoc($usort_result2);			$usort2 = $row['usort'];			$usort2 = $usort2 + 1;    			$sql .= "('$usort2', '$v', '$pid'),";			$catExistTrue = 1;			}		}	if ($catExistTrue)	{	$sql = substr($sql,0,-1);	$sqlRes = mysqli_query ($dbc, $sql); }

Am I on the right track at all? Son

[justsomeguy]

I'm not sure what you're doing with that query, wouldn't you want to use something like this:SELECT category_id FROM productCat WHERE category_id = $v AND $product_id = $pidIf that returns 0 results then it doesn't exist yet.

[son]

I'm not sure what you're doing with that query, wouldn't you want to use something like this:SELECT category_id FROM productCat WHERE category_id = $v AND $product_id = $pidIf that returns 0 results then it doesn't exist yet.

Thanks, you are totally right. I forgot abou the product id|-)Son