SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    John 8:24 JREAM's Avatar
    Join Date
    Sep 2007
    Location
    Florida
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL, Select the next row

    If I do a $_POST['item'] that gets an item from MySQL,

    is there a way to read the next item below in MySQL of the one I just $_POST'ed without having to know the next one's auto_incremented id?

    Would you do it this way?
    SELECT position+1 FROM page

  2. #2
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is $_POST['item'] the autoincrment value? Is that field called ID?
    If so try this (substitute your own secure variables in PHP)
    Code SQL:
    SELECT cols, that, are, needed
    FROM `table`
    WHERE ID >= specified_item_id -- put your variable here
    ORDER BY ID ASC LIMIT 2

  3. #3
    John 8:24 JREAM's Avatar
    Join Date
    Sep 2007
    Location
    Florida
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think something like this will work but its not yet. (Sorry i do not follow all the mysql very well)

    and yes, the $_POST submits my auto_increment ID, and my position value,
    its like: ?movedown=ID&position=POSITION

    Code MySQL:
    UPDATE pages
    SET position = position -1
    WHERE id > '$id'
    AND position > 0
    LIMIT 1

  4. #4
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Firstly position is a MySQL keyword, so if you have a column called position you'll need to use `backticks` around it in your SQL.
    You won't need quotes around $id if it's a numeric field (which it is for autoincrement), and maybe $ID would be better since it is coming from $_GET['ID'] (in caps)

    I'm a bit confused about what you're trying to do.
    Do you want to move the specified item down one position (so if it's 8th now, it will become 9th) relative to all the other rows?

  5. #5
    John 8:24 JREAM's Avatar
    Join Date
    Sep 2007
    Location
    Florida
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was trying to make this list re-order itself.
    The list sorts from the lowest number 0, to the highest.

    When you click Down, it posts and adds the position value +1 (which is working), which moves an item down on the list.

    When they click that same button I wanted a second query, to grab the next position that had a higher number, and make it do -1.

    So if you had item X pos 5,
    and one below it item Y pos 6,
    It would make item X be 6, and item Y be 5

    This is what I have, the first query works, the second one goes through but i dont know why it does nothing.

    Code PHP:
            $position += 1;
            ## Query 1 ##
            $sql = "UPDATE ".$sqlcol." 
                    SET    position='$position' 
                    WHERE id='$id'
                    AND position < $max";
            if ($result = mysql_query($sql)) { echo '1';}
     
            ## Query 2 ## NEEDS FIXING!!!!!!!
            $sql2 =  "UPDATE ".$sqlcol."
                        SET position=`$position-2`
                        WHERE `position` > '$position'
                        LIMIT 1";
     
            if ($result2 = mysql_query($sql)) {echo '2';}

  6. #6
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this:
    PHP Code:
    ## Query 2 ## NEEDS FIXING!!!!!!!
    $sql2 =  "UPDATE ".$sqlcol."
        SET `position` = 
    $position-2
        WHERE `position` > 
    $position
        ORDER BY `position` ASC
        LIMIT 1"

    btw $sqlcol isn't the best name, as it's the table you're identifying there, not a column.

  7. #7
    SitePoint Addict
    Join Date
    Aug 2005
    Posts
    207
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can do it like this... (one query per action, up, down or switch max <> min)


    PHP Code:

    // used if $id != 1  and the action is -> move down

    $id 4// the row that is getting moved down max(8) -> min(1)

    $position 4// the current position number that is in row #4

    $max 8// the maximum position


    $query "UPDATE " $table " SET position = CASE WHEN (id - " $id ") = 0 THEN position + 1 WHEN position = " . ( $position ) . " THEN position - 1 ELSE position END;";



    // used if $id != $max  and the action is -> move up

    $id 4// the row that is getting moved up min(1) -> max(8)

    $position 5// the current position number that is in row #4

    $max 8// the maximum position


    $query "UPDATE " $table " SET position = CASE WHEN (id - " $id ") = 0 THEN position - 1 WHEN position = " . ( $position ) . " THEN position + 1 ELSE position END;




    // switch max(position) to min(position) or switch min(position) to max(position)

    // used if 
    $id == 1 or $id == $max

    $id = 8;

    $position = 8;

    $max = 8;

    // case when id equals max or id equals min

    $query = "UPDATE test SET position = CASE WHEN (id " . $max . ") = 0 THEN " . ( $id == $max ? 'position + 1' : 'position - 1' ) . " WHEN position 1 THEN " . ( $id == $max ? 'position - 1' : 'position + 1' ) . " ELSE position END;"; 

  8. #8
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


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
  •