Results 1 to 2 of 2

Thread: sort order

  1. #1
    Join Date
    Oct 2001
    Mountain Home Arkansas

    sort order

    I have a piece of php code to set the sort order field in my database which I use later to deterimine the order in which I display that information in the output.

    But the way I'm doing it seems a bit chunky to me. I'm wondering if anyone has an ideas on a better way to handle it. I bet it's possible to do it with a single mysql query that will just swap the two adjacent values in the sort field.

    The way I'm doing it now, I read the id and sort fields of the whole table into an array, rearrange the array based on my post data, and then rewrite the entire sort column.

    here is the code I'm using now:
    PHP Code:
    if ( $_POST['mode'] == "sort" ){
    $res mysql_query("SELECT id, sort FROM mytable ORDER BY sort"$_link);
    $i 0;
            while ( 
    $data mysql_fetch_array($res) ){
    $order[$i] = $data['id'];
                if ( 
    $data['id'] == $_POST['id'] ){ $pointer $i; }
    $this $_POST['id'];
    $last $order[$pointer-1];
    $next $order[$pointer+1] ;
    $order array_flip($order);
            if ( 
    $_POST['way'] == "up" AND $last ){
    $order[$this] = $order[$this] - 1;
    $order[$last] = $order[$last] + 1;
            elseif ( 
    $_POST['way'] == "down" AND $next ){
    $order[$this] = $order[$this] + 1;
    $order[$next] = $order[$next] - 1;
            foreach ( 
    $order as $key=>$val ){
    $sort mysql_query("UPDATE mytable SET sort = ".$val." WHERE id = ".$key$_link);
    rocket science is more fun when you accually have rockets!

  2. #2
    Join Date
    Aug 2002
    Perhaps a question followed by a recommendation, why are you storing sort order like that? If you have a table with many records, that'll be a ridiculously expensive operation. Namely, this block which is pretty expensive:

    foreach ( $order as $key=>$val ){
                $sort = mysql_query("UPDATE mytable SET sort = ".$val." WHERE id = ".$key, $_link);
    Try trashing the swap target, updating the swap candidate, and then reinserting the target. All you need to pass, is 'id' and 'direction'.

       if( !is_numeric( $_POST['id'] ) )
           throw new Exception( 'Invalid id input', 0 );
       $candidate    = $_POST['id'];
       if( $_POST['direction'] == 'up' )
          $target       = '<';
       else if( $_POST['direction'] == 'down' )
          $target       = '>';
           throw new Exception( 'Invalid sort direction', 0 );
        $res = mysql_query( "SELECT * FROM table WHERE id $target $candidate LIMIT 1" );
        if( $row = mysql_fetch_assoc( $res ) ){
            mysql_query( "DELETE FROM table WHERE id='{$row['id']}' LIMIT 1" );
            mysql_query( "UPDATE table SET id='{$row['id']}' WHERE id='$candidate'" );
            $row['id'] = $candidate;
            // now reinsert your row, not sure what fields it had:
            mysql_query( "INSERT INTO table ( id, .... ) VALUES ( '{$row['id']}', ... )" );
    catch( Exception $e ){
    The above has the improvement that it doesn't assume that your keys are always packed, where in the real world, such isn't always the case! You can do away with your sort column as well. The above code, where your dataset is of size N, reduces the upper bound from:

    O(n) (query) + O(nlogn) (mysql sorting) + O(n) (array flip) + a staggering O(n) queries at the end...

    to O(4) (read + delete + update + insert ) - a great improvement!

    Hope this helps.
    Last edited by Saeven; 09-14-2006 at 11:49 AM.
    circlical - hosting software development
    forums * blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts