[RESOLVED] MySQL SELECT Previous and Next

woodeye

I'm writing a page that displays ads that are on my site. I need to make previous and next buttons so that the user can move through the ads one at a time.

All the ads are stored in a MySQL database.
Is there a MySQL command to select the previous and next record?

For example, if the database had this:

ID Name
1 Fletcher
8 Smith
12 Jones
19 Thompson
32 Garcia

And I do a query like:

$result = mysql_query("SELECT ID, Name from table_name WHERE ID = 12");
$record = mysql_fetch_assoc($result);

I would get:
$record[ID] = 12
$record[Name] = "Jones"

What I want is:
$record[0][ID] = 8
$record[0][Name] = "Smith"
$record[1][ID] = 12
$record[1][Name] = "Jones"
$record[2][ID] = 19
$record[2][Name] = "Thompson"
So that way the record in index 0 would be previous, 1 is current, and 2 is next.

I was thinking of grabbing all of the ID's and Names then using PHP functions to step through the resulting array, but if I have hundreds of ads or more, then it would be a big waste of memory. There has to be a more efficient way of doing this, right?

Thanks in advance for any suggestions!
Brian

bretticus

Off the top of my head...

As long as you know the current ID you can use ORDER BY and LIMIT clauses in your query like this.

<?php
//next

$current_id = mysql_real_escape_string($_GET['current_ad_id']);
$result = mysql_query("SELECT ID, Name from table_name WHERE ID > $current_id ORDER BY ID LIMIT 1");
$record = mysql_fetch_assoc($result); 

//previous

$current_id = mysql_real_escape_string($_GET['current_ad_id']);
$result = mysql_query("SELECT ID, Name from table_name WHERE ID < $current_id ORDER BY ID DESC LIMIT 1");
$record = mysql_fetch_assoc($result); 
?>

woodeye

I was looking for some complex functions that would just be really slick, and I guess all I needed was a "Less Than/Greater Than" with a "LIMIT 1". Couldn't be easier, and I totally missed it!

Thank you very much. That works perfectly.

bretticus

Awesome! Best to keep the querying in the query I always say (I don't really say that, but it's probably decent advice) 😃