Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts

    Ordering Records php/mysql

    Hello,

    I'm trying to change the order records, via links on a php page.

    my code:
    PHP Code:
        $numID  $_REQUEST['id'];
        
    $numPos = ($_REQUEST['Pos']);
        if(
    $numPos 1){
            
    mysql_query("UPDATE FamilyPhotoAlbum SET DisplayOrder = DisplayOrder - 1 WHERE Id = " $numID);

            
    $numCounter $numPos;
            
    $result mysql_query("SELECT Id,DisplayOrder FROM FamilyPhotoAlbum WHERE Id != " $numID " ORDER BY DisplayOrder ASC");
            while (
    $row mysql_fetch_array($result)){
                if(
    $row['DisplayOrder'] >= ($numPos 1)){
                    
    mysql_query("UPDATE FamilyPhotoAlbum SET DisplayOrder = DisplayOrder + 1 WHERE Id = " $row['Id']);
                }
            }
        } 
    I think my logic is a little off here. I can't seem to get the records to order correctly with out it skipping numbers.

    any ideas would be great!

    thanks in advance for your time!
    -Ken

  • #2
    Regular Coder stevenmw's Avatar
    Join Date
    Jun 2007
    Location
    OK
    Posts
    497
    Thanks
    27
    Thanked 31 Times in 31 Posts
    Quote Originally Posted by angst View Post
    Hello,

    I'm trying to change the order records, via links on a php page.

    my code:
    PHP Code:
        $numID  $_REQUEST['id'];
        
    $numPos = ($_REQUEST['Pos']);
        if(
    $numPos 1){
            
    mysql_query("UPDATE FamilyPhotoAlbum SET DisplayOrder = DisplayOrder - 1 WHERE Id = " $numID);

            
    $numCounter $numPos;
            
    $result mysql_query("SELECT Id,DisplayOrder FROM FamilyPhotoAlbum WHERE Id != " $numID " ORDER BY DisplayOrder ASC");
            while (
    $row mysql_fetch_array($result)){
                if(
    $row['DisplayOrder'] >= ($numPos 1)){
                    
    mysql_query("UPDATE FamilyPhotoAlbum SET DisplayOrder = DisplayOrder + 1 WHERE Id = " $row['Id']);
                }
            }
        } 
    I think my logic is a little off here. I can't seem to get the records to order correctly with out it skipping numbers.

    any ideas would be great!

    thanks in advance for your time!
    -Ken

    I'm not sure I understand exactly how you want your records displayed.... If you want to change the order in which your records are being displayed you should change the line below

    PHP Code:
    FamilyPhotoAlbum WHERE Id != " . $numID . " ORDER BY DisplayOrder ASC"); 
    You can try changing ASC (orders alphabeticly) to DESC (orders descending). There are several ways to order your row. Try looking into srrays over at http://www.php.net

  • #3
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    no no.
    if you look at the code, i've got a DisplayOrder Int field.
    that I'm talking about more just the sql "ORDER BY ASC/DESC".
    I want to change the item order in the DisplayOrder field, so they all come out correctly.

  • #4
    Regular Coder stevenmw's Avatar
    Join Date
    Jun 2007
    Location
    OK
    Posts
    497
    Thanks
    27
    Thanked 31 Times in 31 Posts

    So.

    So then you're wanting to order your query by the ids in DisplayOrder?

  • #5
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    correct.

  • #6
    Regular Coder stevenmw's Avatar
    Join Date
    Jun 2007
    Location
    OK
    Posts
    497
    Thanks
    27
    Thanked 31 Times in 31 Posts

    By ID

    PHP Code:
    $orderbyid "SELECT * FROM `DisplayOrder` ORDER BY `id` DESC"
    That is the simplest way to order Displayorder by it's id.
    DESC will go from highest id to lowest id
    ASC will go from lowest ID to highest id

    If you want to display the newest record in the database before the oldest record you would use DESC.

    If you wanted to display the oldest record before the newest record you would use ASC.

    Hope that helps.

  • #7
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    DisplayOrder is a column, not a table. So I don't think that's what angst is after. The way I'm seeing it, Angst, you have a table and you are giving your users the ability to change the order that the pictures are displayed using a column named DisplayOrder and you want to give the user to switch the pictures around. Does that cover it?

    Here's how I do it... there's probably a better way but this works. It's based upon a loop that swaps two displayOrder values.

    PHP Code:
    function moveLetter($dir) {

        
    $query sprintf("
            SELECT    letter_id,
                    print_order_nr
            FROM    letter_tbl
            WHERE    newsletter_id = %s
            ORDER BY print_order_nr
            "
            
    ,mysql_real_escape_string($_POST['nlID'])
        );
                    
        
    $result mysql_query($query);
        if (!
    $result) {
            
    SQLError(mysql_error(), $query'moveLetter'$_SERVER['REQUEST_URI'], 'BODY');
        }
        
        for (
    $i 0$i mysql_num_rows($result); $i++) {
            
    $letters[$i] = mysql_fetch_assoc($result);
        }
        
        for (
    $i 0$i count($letters); $i++) {
            if (
    $letters[$i]['letter_id'] == $_POST['lID']) {
                
                switch(
    $dir) {
                case 
    1:
                    
    //is this the last letter?  Can't move it down then
                    
    if ($i == (count($letters) - 1)) {
                        return 
    false;
                    }
                    break;
                    
                case -
    1:
                    
    //is this the first letter?  Can't move it up then
                    
    if ($i == 0) {
                        return 
    false;
                    }
                    break;
                }

                
    updateOrder($letters[$i]['letter_id'],    //source letter_id
                
    $letters[$i $dir]['print_order_nr']);  //target print_order_nr
                            
                
    updateOrder($letters[$i $dir]['letter_id'],  //target letter_id
                
    $letters[$i]['print_order_nr']);  //source print_order_nr
            
    }
        }
    }

    //=========================================================================================================//
    //    updateOrder
    //=========================================================================================================//
    function updateOrder($letterID$letterOrder) {

        
    $query sprintf("
        UPDATE    letter_tbl
        SET        print_order_nr = %s
        WHERE    letter_id = %s
        "
    ,
        
    $letterOrder,
        
    $letterID
        
    );
                
        
    $result mysql_query($query);
        if (!
    $result) {
            
    SQLError(mysql_error(), $query'updateOrder'$_SERVER['REQUEST_URI'], 'FULL');
        }


  • #8
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    hey, sorry, i've been on vacation.

    thanks Fumigator! I actually solved this issue while i was away.
    but your logic looks interesting.

    thanks again!
    -Ken


  •  

    Posting Permissions

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