Mysql query, selecting id that is less than previous id

[garyjohnson]

Hello, I have a mysql query

    $query = "SELECT * FROM comments WHERE commenter_username IN (" . join(',', $list) . ") Or Number IN (" . join(',', $list) . ") ORDER BY comment_id DESC";

I want the query to select one row, the row with the highest id. Then store that id into a variable and then select a row thats id is lower than the previous id.

so im thinking it would look something like this

$query = "SELECT * FROM comments WHERE commenter_username IN (" . join(',', $list) . ") Or Number IN (" . join(',', $list) . ") ORDER BY comment_id DESC";

$results = mysql_query($query);
while($row = mysql_fetch_array($results)){
        $commentID = $row['comment_id'];
}

$query = "SELECT * FROM comments WHERE commenter_username IN (" . join(',', $list) . ") Or Number IN (" . join(',', $list) . ") WHERE comment_id < $commentID LIMIT 1";

This is what I want to happen but I am not sure how this would work. the $list variable is an array that is parsed with a comma so the mysql query can comprehend it.

[Atli]

Hey.

You don't have to do this in two queries. The SQL language is very powerful when it comes to filtering and organizing the result sets. In your case, you could use the ORDER BY clause to have MySQL organize the result set so that the highest IDs are listed at the top. Then you could use the LIMIT clause to pick out only the second row of that result set. Like this:

SELECT * 
FROM comments 
WHERE 
    `commenter_username` IN (" . join(',', $list) . ") OR
    `Number` IN (" . join(',', $list) . ") 
ORDER BY `comment_id` DESC
LIMIT 1 OFFSET 1

That should get you the row for the second highest comment_id matching your IN conditions, or an empty set if no such ID exists.

[garyjohnson]

Thanks for the response! Could the offset number be set to 2, 3, 4 or any other number? Cause I could use this but what if I want thee 3rd highest comment Id number? Would the offset be set to 2 then?

[Atli]

Yes, the offset number does exactly that. It's zero-indexed, so the first row is 0, the second 1, the third 2, and so forth.