Reorder rows in MySQL table

Question

I have this php code that lists the rows in a table:

$sql="SELECT * from pages order by menu_order ASC "; $rs=mysql_query($sql,$conn) or die(mysql_error()); while($result=mysql_fetch_array($rs)) { echo '<a href="edit_page.php?pagename='.$result["pagename"].'">'.$result["title"].'</a> - <a href="edit_page.php?do=up&page_order='.$result["menu_order"].'&seq='.$result["sequence"].'">Move Up</a> | <a href="edit_page.php?do=down&page_order='.$result["menu_order"].'&seq='.$result["sequence"].'">Move Down</a><br><br>'; } 

they have links up and down to reorder

which uses this code:

 $page_order = $_GET['page_order']; if ($_GET['do'] == 'up') { $sql="UPDATE pages SET menu_order = '$page_order' +1 WHERE sequence != '".$_GET["seq"]."' AND menu_order < '$page_order' "; $rs=mysql_query($sql,$conn) or die(mysql_error()); $sql="UPDATE pages SET menu_order = menu_order -1 WHERE sequence = '".$_GET["seq"]."' "; $rs=mysql_query($sql,$conn) or die(mysql_error()); } elseif($_GET['do'] == 'down') { $sql="UPDATE pages SET menu_order = '$page_order' -1 WHERE sequence != '".$_GET["seq"]."' "; $rs=mysql_query($sql,$conn) or die(mysql_error()); $sql="UPDATE pages SET menu_order = menu_order +1 WHERE sequence = '".$_GET["seq"]."' "; $rs=mysql_query($sql,$conn) or die(mysql_error()); } 

but it doesn’t work - it changes numbers randomly and, like all their changes, by 0 or minus numbers

What is the best way to do it right?

EDIT: New code ...

I tried this code, but it doesn’t work quite right - if I move the bottom line up, it changes the number -1 every time, and ends up duplicating order numbers?

 $current = $_GET["menu_order"]; $prev = $current-1; $next = $current; if($_GET['do'] == 'up') { $sql2="UPDATE pages SET menu_order = '".($prev)."' WHERE sequence = '".$_GET["seq"]."' "; echo $sql2.'<br>'; $rs2=mysql_query($sql2,$conn) or die(mysql_error()); $sql2="UPDATE pages SET menu_order = '".($next)."' WHERE sequence = '".($_GET["seq"]-1)."' "; echo $sql2; $rs2=mysql_query($sql2,$conn) or die(mysql_error()); } elseif($_GET['do'] == 'down') { $sql2="UPDATE pages SET menu_order = '".($next)."' WHERE sequence = '".$_GET["seq"]."' "; echo $sql2.'<br>'; $rs2=mysql_query($sql2,$conn) or die(mysql_error()); $sql2="UPDATE pages SET menu_order = '".($prev)."' WHERE sequence = '".($_GET["seq"]+1)."' "; echo $sql2; $rs2=mysql_query($sql2,$conn) or die(mysql_error()); } 

Answer 1

I have a similar setting in one of my applications, where I have a list of pages on the site, and the user can press the up / down arrows to rearrange the pages. When they press the up / down arrows, it sends the page_id they clicked on, and then either "up" or "down".

I use PDO, so my formatting will be slightly different from yours. In addition, the pages on my site can be multi-level (i.e., Drop-down navigation), so they also have parent identifiers. When I change their order, I want them to change the order in the parent children, and not on the entire set of pages. If what you are trying to order is flat, you can remove some of it.

The first step is to get the parent_id of the page we want to move:

 $parent_query = "SELECT parent_id FROM page WHERE page_id = :page_id"; $parent_result = $db_mysql->prepare( $parent_query ); $parent_result->execute( array( ":page_id" => $_GET['page_id'] )); $parent_row = $parent_result->fetch( PDO::FETCH_ASSOC ); 

Next, we get the display order of the current page:

 $page_query = "SELECT disp_order FROM page WHERE page_id = :page_id AND parent_id = :parent_id"; $page_result = $db_mysql->prepare( $page_query ); $page_result->execute( array( ":page_id" => $_GET['page_id'], ":parent_id" => $parent_row['parent_id'] )); $page_row = $page_result->fetch( PDO::FETCH_ASSOC); 

Now we get the display order of the page we are sharing with. If we clicked to go down, we get page_id and disp_order of the next page. If we clicked to move up, we’ll buy page_id and disp_order of the previous page:

 $swappage_query = "SELECT page_id, disp_order FROM page WHERE disp_order = :disp_order AND parent_id = :parent_id"; $swappage_result = $db_mysql->prepare( $swappage_query ); if( $_GET['move'] == "up" ) { $swappage_result->execute( array( ":disp_order" => $page_row['disp_order'] - 1, ":parent_id" => $parent_row['parent_id'] )); } else { $swappage_result->execute( array( ":disp_order" => $page_row['disp_order'] + 1, ":parent_id" => $parent_row['parent_id'] )); } $swappage_row = $swappage_result->fetch( PDO::FETCH_ASSOC ); 

Finally, we are going to execute the swap by changing the display order both on the page we clicked on and on the page next to it that we exchange:

 $neworder_query = "UPDATE page SET disp_order = :disp_order WHERE page_id = :page_id"; $neworder_result = $db_mysql->prepare( $neworder_query ); $neworder_result->execute( array( ":disp_order" => $swappage_row['disp_order'], ":page_id" => $_GET['page_id'] )); $neworder_result->execute( array( ":disp_order" => $page_row['disp_order'], ":page_id" => $swappage_row['page_id'] )); 

What am I doing here:

• Get the parent_id of the page we clicked on, so that we only move pages inside parent_id, not the other parent children. If what you order is a flat list without layers, you can skip this step.
• Get the display order of the current page. Since display orders are saved sequentially, for example, 1, 2, 3, etc., Knowing the display order of the current page will tell me which page I need to change. If the current page has a display order of 3, then the page for sharing with it is 2 or 4, depending on whether I clicked up or down, respectively.
• Get page_id for the page we're going to share with. If we pressed "up", then this is the current page disp_order - 1. If we click "down", then this is the current page disp_order + 1.
• Replace, which means changing the current one to disp_order + 1 or disp_order - 1 depending on up or down, and then do the opposite with the page we are sharing with.