PHP change MySQL rows order

Question

I have this PHP code for my menu which selects all the links and names from MySQL:

<?php
$url = '';
//select all the top row items
$sql="SELECT * from website_menu where parent_top = '' and parent = '' order by menu_order ASC ";
$rs=mysql_query($sql,$conn) or die(mysql_error());
while($result=mysql_fetch_array($rs))
{
  //then select all the next rows down (parent_top)
  $current = false;
  $subMenu = '';
  $sql2="SELECT * from website_menu where parent_top = '".$result["sequence"]."' order by menu_order ASC ";
  $rs2=mysql_query($sql2,$conn) or die(mysql_error());
  if(mysql_num_rows($rs2) > 0)
  {
    $subMenu = '<ul>';
    while($result2=mysql_fetch_array($rs2))
    {
        if($_GET["id"] == $result2["link"])
        {
            $current = true;
        }
        ///
        if(substr($result2["link"],0,7) == 'http://')
        {
            $url = '';
        }
        elseif(substr($result2["link"],0,8) == 'https://')
        {
            $url = '';
        }
        else
        {
            $url = $settings["website_url"].'/';
        }
        $subMenu .= '<li><a href="'.$url.''.$result2["link"].'"><span>'.$result2["title"].'</span></a>';
        //
        $sql3="SELECT * from website_menu where parent = '".$result2["sequence"]."' ";
        $rs3=mysql_query($sql3,$conn) or die(mysql_error());
        if(mysql_num_rows($rs3) > 0)
        {
            $subMenu .='<ul>';
            while($result3=mysql_fetch_array($rs3))
            {
                ///
                if(substr($result3["link"],0,7) == 'http://')
                {
                    $url = '';
                }
                elseif(substr($result3["link"],0,8) == 'https://')
                {
                    $url = '';
                }
                else
                {
                    $url = $settings["website_url"].'/';
                }
                $subMenu .='<li><a href="'.$url.''.$result3["link"].'"><span>'.$result3["title"].'</span></a></li>';
                if($_GET["id"] == $result3["link"])
               {
                  $current = true;
               }
            }
            $subMenu .='</ul>';
            $subMenu .='</li>';
        }
        else
        {
            $subMenu .='</li>';
        }
    }
    $subMenu .= '</ul>';
}
    echo '<li';
if($_GET["id"] == $result["link"] || $current)
{
    echo ' class="active"';
}
///
if(substr($result["link"],0,7) == 'http://')
{
    $url = '';
}
elseif(substr($result["link"],0,8) == 'https://')
{
    $url = '';
}
else
{
    $url = $settings["website_url"].'/';
}
echo '><a href="'.$url.''.$result["link"].'"><span>'.$result["title"].'</span></a>', $subMenu, '</li>';
 }
?>

they are all ordered by an INT column called menu_order and i want to be able to have UP / DOWN links to re order

i currently have:

<?php
if($_GET['do'] == 'up')
{
    //get the selected rows position
    $sql="SELECT * from website_menu where sequence = '".$_GET["sequence"]."' ";
    $rs=mysql_query($sql,$conn);
    $result=mysql_fetch_array($rs);
    $current_sequence=$result["sequence"];
    $current_position=$result["menu_order"];

    //get the next rows posistion
    $sql="select * from website_menu where sequence >= '".$current_position."' order by sequence LIMIT 0,1 ";
    $rs=mysql_query($sql,$conn);
    $result=mysql_fetch_array($rs);
    $next_sequence=$result["sequence"];
    $next_position=$result["menu_order"];

    //update the selected rows position (-1)
    $sql="UPDATE website_menu set menu_order = '".($current_position-1)."' where sequence = '".$current_sequence."' ";
    $rs=mysql_query($sql,$conn);

    //update the next rows position to be the selected rows position
    $sql="UPDATE website_menu set menu_order = '".$current_position."' where sequence = '".$next_sequence."' ";
    $rs=mysql_query($sql,$conn);
}
elseif($_GET['do'] == 'down')
{
    //get the selected rows position
    $sql="SELECT * from website_menu where sequence = '".$_GET["sequence"]."' ";
    $rs=mysql_query($sql,$conn);
    $result=mysql_fetch_array($rs);
    $current_sequence=$result["sequence"];
    $current_position=$result["menu_order"];

    //get the previous rows posistion
    $sql="select * from website_menu where sequence <= '".$current_position."' order by sequence LIMIT 0,1 ";
    $rs=mysql_query($sql,$conn);
    $result=mysql_fetch_array($rs);
    $previous_sequence=$result["sequence"];
    $previous_position=$result["menu_order"];

    //update the selected rows position (+1)
    $sql="UPDATE website_menu set menu_order = '".($current_position+1)."' where sequence = '".$current_sequence."' ";
    $rs=mysql_query($sql,$conn);

    //update the next rows position to be the selected rows position
    $sql="UPDATE website_menu set menu_order = '".$current_position."' where sequence = '".$previous_sequence."' ";
    $rs=mysql_query($sql,$conn);
}
?>

but its not re-ordering correctly and swapping the numbers the right way round

The Blue Dog · Accepted Answer · 2013-12-27 17:18:55Z

2

Don't waste your time doing it like this, use the links to call a new query using ORDER BY ASC or ORDER BY DESC and display the results. Let SQL do all the hard work.

answered Dec 27, 2013 at 17:18

The Blue Dog

2,4753 gold badges21 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

The Blue Dog Over a year ago

I'm trying to free your mind, Charlie. But I can only show you the door, you are the one that has to walk through it.

Collectives™ on Stack Overflow

PHP change MySQL rows order

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related