New Order After Delete

[shaneg55]

I posted this in mysql as well but maybe someone would know the php/mysql syntax to do this:

 

How to re order records after a delete or move?

 

I have table of navigational items. lets say under "about us" i have 5 sub links and i order them by a field called theorder. How can a re order them properly if one is deleted?  orders are 1,2,3,4,5 and if i delete the record that is 3 then the order is 1,2,4,5 but i want it to now become 1,2,3,4.  How do i do this properly?

 

Thanks

[the182guy]

Here's one way. Note: I have simulated getting the items from the database by just creating the array because I don't know your db structure or what mysql object you're using.

<?php

// replace this with your code to retrieve the menu items from the database into an associative array
// you must use: ORDER BY theorder ASC
// in your SQL query or they may come out in the wrong order and it is essential that they come out ascending

$items = array(
array('name' => 'Home', 'id' => 55, 'theorder' => 1),
array('name' => 'About Us', 'id' => 25, 'theorder' => 2),
array('name' => 'Portfolio', 'id' => 2, 'theorder' => 4),
array('name' => 'Contact Us', 'id' => 1, 'theorder' => 5)
);


$count = 1;
$totalItems = count($items);

// could use a foreach() loop here but a for() loop ensures it goes in ascending order which is required for this to work
for($i=0; $i<$totalItems; $i++) 
{
$items[$i]['theorder'] = $count; // set the new order

$count++;
}


// now just loop through the items and run an UPDATE query on each one with the new 'theorder' field
foreach($items as $item)
{
$newOrder = (int)$item['theorder'];
$id = (int)$item['id'];

$sql = "UPDATE menuitems SET theorder = '$newOrder' WHERE id = '$id' LIMIT 1";
// now execute the query with mysql_query() or whatever mysql/mysqli object you're using
}

// print out the array to check that the order has been corrected - obviously remove this after tests
echo '<pre>' . print_r($items, true) . '</pre>';

?>

[shlumph]

1,2,3,4,5 and 1,2,4,5 are still ordered the same way; smallest to largest.