Ordering using up/down arrows

oMIKEo · 06-10-2008, 07:37 PM

Hi all,

I have a list of records (videos) that are ordered using a value in a field called 'theorder'. 1 is at the top of the list. When the user clicks up or down next to each of the videos then it takes its current position and either +1 or -1 from it, and whatever is in that position switches values with it.

Here is my code:

PHP Code:


			
// ---------------- CHANGE ORDER

if($_GET['order'] != "")

{

    $ord_id = $_SESSION['zUser'];

    $ord_media = $_GET['vid_id'];

    $ord_order = $_GET['order'];

    

    

    // get current order position of video

    $sql = "SELECT * FROM yml_media WHERE id = '$ord_media' AND artist_id = '$ord_id' LIMIT 1";

    $rs=mysql_query($sql,$conn) or errorEmail("003", mysql_error(), $current_page_url);

    while($row=mysql_fetch_array($rs))

    {        

        $ord_video1_id = $row["id"];

        $ord_video1_order = $row["theorder"];

        $ord_video1_type = $row["section"];

    }

    

    

    // find which video already has the order requested

    $sql = "SELECT * FROM yml_media WHERE theorder = '$ord_order' AND artist_id = '$ord_id' AND section = '$ord_video1_type' LIMIT 1";

    $rs=mysql_query($sql,$conn) or errorEmail("003", mysql_error(), $current_page_url);

    while($row=mysql_fetch_array($rs))

    {        

        $ord_video2_id = $row["id"];

        $ord_video2_order = $row["theorder"];

    }

    

    

    // switch those two video locations

    mysql_query("UPDATE yml_media SET theorder = '$ord_order' where id = '$ord_video1_id'") or die(mysql_error()); 

    

    mysql_query("UPDATE yml_media SET theorder = '$ord_video1_order' where id = '$ord_video2_id'") or die(mysql_error()); 



    

    

    

}

// ---------------- CHANGE ORDER

This is fine until something outside of this ordering is changed such as a video is deleted or moved to another section leaving a number missing in the sequence.

I'm sure there is a much better way of doing this - can anybody point me in the right direction?

Thanks

xenon · 06-10-2008, 08:56 PM

Well, when you delete a record, you need to set all the other records after the deleted record's order to order-1. Example: say you have 5 items. The item with the id 3 has the order number 2. After you issue the delete statement, run this aswell:

Code:

UPDATE items SET theorder=theorder-1 WHERE theorder > deleted_record_order