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 12 of 12
  1. #1
    New Coder
    Join Date
    Dec 2008
    Posts
    15
    Thanks
    2
    Thanked 0 Times in 0 Posts

    PHP/MYSQL Reorder

    I am trying to create a news system backend for a user where they can go in, add new news, delete news, set status of the news, and rearrange the order the news should appear. Once they click on Publish it will send an email to the administrator with the code to copy and paste into the CMS.

    I am having trouble with the move/rearrange function. How would I do a simple move, that when the user clicks on the move up button it rearranges that news item so that it is above the one that was previously above it and vice versa for down. I've tried many scenarios but I seem to be stuck.

  • #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,011
    Thanks
    2
    Thanked 312 Times in 304 Posts
    Simplest way -

    You need a "position" column in your database that holds the intended display position. Each click of a "move-up" or "move-down" button causes the value in the "position" column for the current item to be exchanged with the value of the correct (up or down) neighboring item.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #3
    New Coder
    Join Date
    Dec 2008
    Posts
    15
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks CFMaBiSmAd for the quick response. I understand that part but the part where I'm stuck now is how to initially generate the position of the news item. For testing purposes I created a position column. How do I set up the position initially so that it should be 1, 2, 3, 4, 5, 6, 7, 8 instead of the default of zero. Thanks.
    Last edited by tsidell; 12-02-2008 at 05:58 PM.

  • #4
    New Coder
    Join Date
    Dec 2008
    Posts
    15
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Do I do auto increment in the database for that field? But if I do that wont I run into a problem since they can delete news items.

  • #5
    New Coder
    Join Date
    Dec 2008
    Posts
    15
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Also, I have newsid as a key. It wont allow me to auto increment position since newsid is already auto incremented

  • #6
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,011
    Thanks
    2
    Thanked 312 Times in 304 Posts
    For an existing table and you are the only one making changes to the data, I would just write some php code to execute UPDATE queries in a loop with a counter. If you are really good at sql (I'm not) you could probably use a temporary sql variable as a counter to do it all in one query.

    For inserting new items, I would INSERT the item and read the last insert id (assuming you have an autoincrement index) and put that value into the position column too. You can use triggers to cause this to happen automatically - http://dev.mysql.com/doc/refman/5.0/en/triggers.html This will place new items at the end. An alternative would be to reserve the first position for new items so that they would automatically appear at the start of the position list.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • Users who have thanked CFMaBiSmAd for this post:

    tsidell (12-02-2008)

  • #7
    New Coder
    Join Date
    Dec 2008
    Posts
    15
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I wrote a query

    $positionquery = mysql_query("UPDATE news SET position = position+1");


    but it updates every news item with the same position. How do I accomplish the feat of having it update each particular news item separately.

  • #8
    New Coder
    Join Date
    Dec 2008
    Posts
    15
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I'm making progress, I got the position function to work but how would I get the move up to work.

    the move.php file I have is:

    include ("connect.php");
    $update= $_GET['newsid'];
    $movequery = mysql_query("UPDATE news SET position = position + 1 WHERE newsid = '$update'");
    $movequery2 = mysql_query("UPDATE news SET position = position - 1 WHERE newsid = '$myrow[position]-1'");

    it does nothing...

  • #9
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,011
    Thanks
    2
    Thanked 312 Times in 304 Posts
    to be exchanged with the value of the correct (up or down) neighboring item.
    I recommended that you exchange values because when rows have been deleted incrementing and decrementing the position values won't work.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #10
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,011
    Thanks
    2
    Thanked 312 Times in 304 Posts
    I have a correction to some of the above, triggers cannot be used to UPDATE a value in the row just INSERTED and since the last insert id is not available until after the INSERT, doing what I suggest would require an INSERT followed by an UPDATE of the same row.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #11
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,011
    Thanks
    2
    Thanked 312 Times in 304 Posts
    Here is some basic move-up/move-down code (Disclaimer: Your mileage may vary, depending on how you drive... ) -
    PHP Code:
    // $_GET['action'] will indicate up/dn
    $action = isset($_GET['action']) ? $_GET['action'] : false// condition input and set default
    // $_GET['id'] is the id to move up/dn
    $id = isset($_GET['id']) ? (int)$_GET['id'] : false// condition input and set default
    if(!$id){
        die(
    "No id supplied");
    }
    if(
    $action <> "up" && $action <> "dn"){
        die(
    "Invalid action selected");
    }

    // get the current position of the id 
    $query "SELECT position FROM $table_name[0] WHERE id = $id";
    $result mysql_query($query) or die("Query failed: $query<br />Mysql error: " mysql_error());
    if(
    mysql_num_rows($result) <> 1){
        echo 
    "There is no id: $id<br />";
    } else {
        list(
    $get_position) = mysql_fetch_row($result); // get the position of the requested id
        
    if($action == "up"){
            
    // get the id and position of that and the next lower position
            
    $query "SELECT id, position FROM $table_name[0] WHERE position <= $get_position ORDER BY position DESC LIMIT 2";
        } else {
            
    // get the id and position of that and the next higher position
            
    $query "SELECT id, position FROM $table_name[0] WHERE position >= $get_position ORDER BY position LIMIT 2";
        }    
        
    $result mysql_query($query) or die("Query failed: $query<br />Mysql error: " mysql_error());
        
    // test how many rows (if less than 2, there is no locaiton to move to)
        
    if(mysql_num_rows($result) < 2){
            echo 
    "You are already at the limit<br />";
        } else {
            
    // move up/dn one by swapping the position values
            
    $row1 mysql_fetch_assoc($result); // row1
            
    $row2 mysql_fetch_assoc($result); // row2
            // update row1 (with row2's position value)
            
    $query "UPDATE $table_name[0] SET position = {$row2['position']} WHERE id = {$row1['id']}";
            
    $result mysql_query($query) or die("Query failed: $query<br />Mysql error: " mysql_error());
            
    // update row2 (with row1's position value)
            
    $query "UPDATE $table_name[0] SET position = {$row1['position']} WHERE id = {$row2['id']}";
            
    $result mysql_query($query) or die("Query failed: $query<br />Mysql error: " mysql_error());
        }

    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • Users who have thanked CFMaBiSmAd for this post:

    ScoobyDooobyD00 (12-17-2008)

  • #12
    New to the CF scene
    Join Date
    Dec 2008
    Posts
    1
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks CFMaBiSmAd for the code! Extremely helpful!
    Last edited by ScoobyDooobyD00; 12-18-2008 at 09:45 PM.


  •  

    Posting Permissions

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