switch order of two records in a table

Posted: April 16th, 2008 | Author: admin | Filed under: Developers |

When in need to change the order of a list use a field to store the order as an int:
Change the order of the records by switching the order value between consecutive elements.


<?php 

switch ($action){

    case 'orderdown':

    case 'orderup':{ 

        //get all list and retain two elements: if moveup operation then I keep prev and current  

        //otherwise I keep current and next

        $rec_list = $this->getChildren($record->id_parent);//all records on this level

        $switch = array();

        for($i = 0; $i < count($rec_list) ; $i++){

            if($rec_list[$i]['id'] == $record['id']){

                if('orderup' == $what && $i != 0){    //not first

                    $switch[$rec_list[$i-1]['id']] = $rec_list[$i]['corder'];

                    $switch[$rec_list[$i]['id']] = $rec_list[$i-1]['corder'];

                    break;

                }elseif('orderdown' == $what && $i != (count($rec_list)-1)){    //not last

                    $switch[$rec_list[$i+1]['id']] = $rec_list[$i]['corder'];

                    $switch[$rec_list[$i]['id']] = $rec_list[$i+1]['corder'];

                    break;

                }

            }

        }
        //valid elements found. Switch them

        if(count($switch) > 0){

            foreach ($switch as $id=>$corder) {

                $sql = "UPDATE page SET corder=$corder WHERE$id";

                $result = mysql_query($sql) or die("SQL ERROR " . mysql_error() . " [$sql] on " . __FILE__ . " at line " . __LINE__);

            }

        }

    }break;

}

?>