How to Get Next and Previous Record in MySQL Database

RegisterLogin

Webune Forums » MYSQL » How to Get Next and Previous Record in MySQL Database

How to Get Next and Previous Record in MySQL Database
Post Description: for the longest time i struggle to find an answer for this - i wanted to know how to query a databse table where the numbers were not consecutive but to only display the records i needed
Tags: how, to, get, next, and, previous, record, in, mysql, database
This Post Was Posted On Sep 10, 2010 By Webune Support #3538

How to Get Next and Previous Record in MySQL Database by Webune Support

for the longest time i struggle to find an answer for this.

whenever i would do my query in my sql with php, i could only get the next record by adding a 1

for example, lets say i have this

$CurrentId = 54;
$NextId = $CurrentId + 1;

$query = "SELECT * FROM mytable WHERE id = $NextId";

OUTPUT: 55

as you can see, from my query above, i would get the record 55. but how about if i had a field in my database called hidden it looked like this:

id 54
visible = yes

id 55
visible = no

id 56
visible = yes

so if i had a table with the above information, and my current id was 54, using the PHP code above would result in my $NextId as 55, but i dont want 55, i want 56, because id 55 is not visible..

so this is how you would do the query correctly:

$CurrentId = 54;

$query = "SELECT * FROM mytable WHERE id > $CurrentId AND visible = 'yes' LIMIT 1";

OUTPUT: 56

as yo can see, the query ignored id 55 - why? because id 55 visible value is equals to no, and the query specifies, only to get records where visible equals to 'yes'

hope that helps

Leave Your Comments

Related Pages: [Add Your Website]

Post New Topic

Resume the next iteration of a loop

neeraj

#10154 1

This a great tutorial.But i want to do extra.

Feb 13, 2013 Reply Report abuse

mack

#5347 2

very great. just what i needed. its good to find little tutorials like this one.

Jul 27, 2011 Reply Report abuse

boxi

#5295 3

i finally figured it out, it turns out i was doing it wrong til i read you post here. thanks for telling about this.

Jun 29, 2011 Reply Report abuse

scott

#4428 4

thanks, i also found this site

http://www.scottklarr.com/topic/111/how-to-select-previousnext-rows-in-mysql/