• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3746
  • Last Modified:

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
0
theprankstanator
Asked:
theprankstanator
  • 4
  • 3
1 Solution
 
BatalfCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now