MySQL update

[Anon]

Hi All

I have a DB where user can put 20 items in the storage bin item they want to keep there to buy at later dates they keep the first item as the next purchase item these items are numbered 1 to 20.

I want to be able to let the user del any item or reorder the items accordingly

my problem is that i need re order the $list_numbers back to an correct order
i.e.

my bin is full I want to remove item 4
hit update my bin should read
item 1
item 2
item 3...
item 19

of course counting in numric order

I tried this to no avail
// this is coming from a list() to count the
// checkbox that are checked
$checked_fields = count($item_id);

   $sql_re = "UPDATE storage_bin SET
      $list_numbers = $list_numbers - $checked_fields 
      WHERE user_id = '$user_id'"; 
      $sql_re_result = mysql_query($sql_re);

TIA for any help

[Anon]

How about using auto_increment in the item_id column? Newly inserted items then always get the highest id.

Then it doesn't matter what id you delete, the ordering is still intact.

Then to display the items: select * from storage_bin order by item_id.

[Anon]

That is correct but if you limit 20 to a user then each user needs to have a 1 position if I'm keeping track of each user count so if user 1 has 20 items and user 2 10 items then user a auto_increment wont work.

Need to update a seperate column to track the count for each user

so I list the item by $list_number with a restriction to only allow 20 items per user if a user buys or deletes 4 Items and is left with 16 item I don't want a cnt of 1,2,3,5,7,8,9.... Just dost look good so I would like to re-order that row