shaneg55 Posted September 13, 2010 Share Posted September 13, 2010 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 Quote Link to comment Share on other sites More sharing options...
the182guy Posted September 13, 2010 Share Posted September 13, 2010 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>'; ?> Quote Link to comment Share on other sites More sharing options...
shlumph Posted September 13, 2010 Share Posted September 13, 2010 1,2,3,4,5 and 1,2,4,5 are still ordered the same way; smallest to largest. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.