Deleting Products Brings Issue For Sorting Number

[son]

I found a issue in my code to display products on category overview page in certain order. When I make and association between prduct and category I enter a usort number which counts the number of previous items in same category, adds 1 and inserts this value into table. The code is:

	$sql = "INSERT INTO productCat (usort, category_id, product_id) VALUES ";		foreach ($category as $v)		{		$count_query = "SELECT COUNT(*)  FROM productCat WHERE category_id = $v";         $count_result = mysqli_query($dbc, $count_query);         $r = mysqli_fetch_row($count_result);         $max = $r[0];		 $max = $max + 1;    	$sql .= "('$max', '$v', '$id'),";		}

This works as long as no product and therefore also product/category association gets deleted. When a product is deleted counting the rows of products in same category and adding one might potentially enter a number in usort which exists already. Not sure what the best is to avoid this?SonI also would like to avoid gaps. Can I somehow automatically re-order or similar?

[justsomeguy]

You'll need to reorder when you delete. When you delete an item everything with a sort order higher than that should be decreased by one.

[son]

You'll need to reorder when you delete. When you delete an item everything with a sort order higher than that should be decreased by one.

What would a query like this look like? Does this process have a certain name? I litterally have never done an update on more than one item at a time.At present I delete simply product data via:

	$del_q = "DELETE FROM products WHERE product_id = $id";$del_q2 = "DELETE FROM productCat WHERE product_id = $id";

Son

[justsomeguy]

UPDATE items SET usort = usort - 1 WHERE category=10 AND usort > 5

[son]

UPDATE items SET usort = usort - 1 WHERE category=10 AND usort > 5

I am on the case, but struggling a bit as a product could be in several categories...Son

[son]

I am on the case, but struggling a bit as a product could be in several categories...Son

Just to say: I managed to get it working few days ago... In the end it seemed all easy, but this does not help my 'uncreativity' when I am faced with a new issue;-( Anyway, many thanks for your helpful information...Son

[Guest droope]

Just to say: I managed to get it working few days ago... In the end it seemed all easy, but this does not help my 'uncreativity' when I am faced with a new issue;-( Anyway, many thanks for your helpful information...Son

Most people look at a programming language and say "Hey, ¡That's too hard! I don't understand.".You, on the other hand, started coding, and are now facing php code oftenly... Perhaps your "uncreativity", as you call it, is just that you have recieved lots of good help, and now depend of it. Either way, be proud You are coding.So...Happy coding!Cheers,Droope

[son]

Most people look at a programming language and say "Hey, ¡That's too hard! I don't understand.".You, on the other hand, started coding, and are now facing php code oftenly... Perhaps your "uncreativity", as you call it, is just that you have recieved lots of good help, and now depend of it. Either way, be proud You are coding.So...Happy coding!Cheers,Droope

Thanks for your post. I got your point about good help (which I am really grateful for) and think I do sometimes rely on it too much. Still, each time someone helped (many thanks to all of you) I learned something new and as strange as it sounds there are many cases where the help let me solve different issues without intervention. And you are right then, I feel great when this happens:-)Son