SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Wizard wonshikee's Avatar
    Join Date
    Jan 2007
    Posts
    1,223
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    ordering of rows

    Hi, i'm wondering how some of you guys would go about ordering a table based on an ID?

    The client wants to be able to control the ordering and although I got it working, it does not look very clean as it requires multiple queries.

    I set up an orderID, and what I did was when an arrow is clicked next to the row, such as an up arrow, I would pass the rowID + the orderID, then seek out (in the case of up) the previous orderID, then swap the two. But all this requires multiple queries.


    Any tips on an efficient method of doing this? Again it's not that I can't get it done, it's just I would like a clean way of doing it.

    Here is the code I am using.
    PHP Code:
    $oldOrderID = (int) $_GET['num'];
    $faqID = (int) $_GET['faqID'];

    if (
    $_GET['act'] == 'up') {
        
    $newOrderID mysql_result(mysql_query("SELECT MAX(orderID) FROM ".FAQ." WHERE orderID < $oldOrderID LIMIT 1"), 0);
    } else if (
    $_GET['act'] == 'down') {    
        
    $newOrderID mysql_result(mysql_query("SELECT MIN(orderID) FROM ".FAQ." WHERE orderID > $oldOrderID LIMIT 1"), 0);
    }

    if (!
    $newOrderID$stop 1# protect from out of bounds
    if (!$stop) {
        
    mysql_query("UPDATE ".FAQ." SET orderID = $oldOrderID WHERE orderID = $newOrderID");
        
    mysql_query("UPDATE ".FAQ." SET orderID = $newOrderID WHERE faqID = $faqID");


  2. #2
    . shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    For the Select I would do:
    PHP Code:
    $up   = ($_GET['act'] == 'up');
    $sql  'SELECT ' . (!$up 'MIN' 'MAX') .'(orderID) FROM ' FAQ;
    $sql .= ' WHERE orderID ' . (!$up '>' '<') . $oldOrderID;
    $sql .= ' LIMIT 1';

    $newOrderID mysql_result(mysql_query($sql), 0); 
    Multiple lines for readability but you can smash it to one
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.



Bookmarks

Posting Permissions

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