Trying to get next record from MySQL db

Hi All,

This is probably extremely simple, but I cant work it out.

I have build a site for a client, Under the 'collections' link this displays all records from that 'collection' in thumbnail form, clicking one of them passes the id in the URL to display that product. Now, I have done $next = $id + 1; but of course, if the next record has been deleted, it wont return anything. How can I write a loop to keep checking until it finds the next record and pass the id of that record though the URL?

Thanks in Advance,

Christian
theprankstanatorAsked:
Who is Participating?
 
BatalfConnect With a Mentor Commented:
You only need one query:

$sql = "select id from yourTable where id>'$id' order by id limit 1";
$res = mysql_query($sql) or die ("Error in query: " . mysql_error());
if($inf = mysql_fetch_array($res)){ // A next record is found
    $next = $inf["id"];
}

You search for the first id which is bigger than the variable $id. This is the next id in the table.

"yourTable" is just an example name.

Batalf
0
 
theprankstanatorAuthor Commented:
Hi Batalf,

So I would run that again with different names for Previous records too?

So, I'd run 3 queries in total?

Thanks heaps!
0
 
BatalfCommented:
For the previous record, the query would be

$sql = "select id from yourTable where id<'$id' order by id desc limit 1";

i.e. searching for lower ids and search for elements in descending order.

Yes, you run the query for the current, record, next record and previous record.

Batalf
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
theprankstanatorAuthor Commented:
Batalf,

If I knew you, I would kiss you! I have been trying to work this out for days!!

Thank you so much!!!

Christian
0
 
BatalfCommented:
Glad I could help!

I just thought of another option which also should work.

What if you have a link like this:

id=$id&next=true

i.e. send the current id and a variable called "next" to the current page. Then you could perform the query for next or previous record there, a query like this:

if(isset($_GET['next'])){
  $sql = "select id from yourTable where id>'$id' order by id limit 1";
  $res = mysql_query($sql) or die ("Error in query: " . mysql_error());
  if($inf = mysql_fetch_array($res)){ // A next record is found
      $id= $inf["id"]; // Set $id to the next ID
  }
}

The same rules should also been applied for a variable called "previous".

The advantage of this solution is that you don't have to make 3 queries on each page.
0
 
theprankstanatorAuthor Commented:
Oh, yes, very nice - I will most probably use that instead.

Awesome - Thanks very very much!

Christian
0
 
BatalfCommented:
Ahhhh. just thought of a drawback with that last solution ---- what if there isn't any next or previous record!?

If you're on the last record and have a link "next", then there isn't any next record to find. Looks like the first solution is the best after all.

Batalf
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.