Results 1 to 13 of 13
  1. #1

    PHP + mySQL : Selecting rows

    How can I select a previous row and a next row?

    For example I have a table like this:

    Id: Value
    1 A
    2 B
    3 C
    4 D
    5 E
    6 F


    I just queried (select from) where id was 3, is there a way to select from the previous row and next row (without using id's)?

    I don't want to use auto incremented Ids, because sometimes a row can be deleted between them, so you get something like 1-2-4-5 (missing the 3).

  2. #2
    Join Date
    Nov 2003
    Location
    Ljubljana, Slovenija, Europe
    Posts
    298
    You WANT to use auto_incremment! That's the only proper way to handle table information. As for the previous and the next row, that's fairly easy. You should query the entire table and have the rows sorted by the auto_incremment column, that would give you a result that contains rows. Every result row would represent a table row and they would be order as created. So there would be no problem with missing index values. You would simply take the previous or the next row from results according to the the row in results that you have the relevant data. Good luck! Airnine

  3. #3
    No, users will be able to delete a row (a comment for example) and then it will skip an ID for sure.

  4. #4
    Join Date
    Apr 2003
    Location
    London, UK
    Posts
    4,721
    You could return the last id with mysql_insert_id() then use SELECT * FROM table WHERE id > lasd_id LIMIT 1

    And the same with WHERE id < lasd_id LIMIT 1 to return the previous and next rows.
    Hyperconfused (™)

  5. #5
    WHERE id < '$last_id' LIMIT 1
    causes it to return an id at the beginning of the table, so always 1.

    But > works good for the next row.

  6. #6
    Join Date
    Apr 2003
    Location
    London, UK
    Posts
    4,721
    WHERE id < '$last_id' ORDER BY id DESC LIMIT 1

    try that instead.
    Hyperconfused (™)

  7. #7
    Gives me a syntax error

  8. #8
    Hmm, I tried a direct result query, maybe order only works with arrays?

  9. #9
    Join Date
    Jul 2003
    Location
    Kuwait
    Posts
    5,104
    don't use ' ' around a variable name, it does not get interpreted.

    PHP Code:
    $var "Hello";

    echo 
    '$var'//prints $var
    echo "$var"//prints Hello 

  10. #10
    Join Date
    Apr 2003
    Location
    London, UK
    Posts
    4,721
    It will in a MySQL query, as long as the variable has already been defined properly in the script.
    Hyperconfused (™)

  11. #11
    Join Date
    Jul 2003
    Location
    Kuwait
    Posts
    5,104
    It will not if the query is encapsulated in ' ', in which case, it will throw a invalid query/syntax error. Which you can catch with mysql_error();

  12. #12
    Thanks, it worked now.

  13. #13
    Originally posted by fyrestrtr
    It will not if the query is encapsulated in ' ', in which case, it will throw a invalid query/syntax error. Which you can catch with mysql_error();
    Hmm.. if you are saying that a MYSQL query coded in php, where the WHOLE query is single quoted.. E.G.:
    PHP Code:
    $id "MyName";
    $sql='SELECT * FROM table WHERE id = '$id'';
    mysql_query($sql);
    echo 
    $sql;

    //Echo will display: 
    //SELECT * FROM table where id = $id 
    then yes, the query will always error, since the whole query is seen as a STRING, single quoted strings are taken literally, double quoted strings are parsed, (meaning that single quoted STRINGS will not be parsed, hence, neither will the variables)
    THEREFORE, the following IDENTICAL query (double quoted) WILL work.
    PHP Code:
    $id="MyName";
    $sql="SELECT * FROM table WHERE id = '$id' ";
    mysql_query($sql);
    echo 
    $sql;

    //this will display :
    // SELECT * FROM table WHERE id = MyName 
    (the query string IS parsed, so single quoted within double quotes follows sql syntax perfectly.. )

    It's just that the original SQL query posted just before your comment of "if single quoted it will not work" was "probably" intended to be parsed by php..

    Just wanted to clear up that point, before some noobs come by and give up in frustration when their code won't work..

Posting Permissions

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