Display x number of records with a specified id in the middl

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
LonelyProgrammer
Forum Contributor
Posts: 108
Joined: Sun Oct 12, 2003 7:10 am

Display x number of records with a specified id in the middl

Post by LonelyProgrammer »

Hi,

I am trying to come up with a SQL statement that will display x (say 10) records, with the one meeting the search criterion in the center as the x/2 record (or the 5th record). I could try this

Code: Select all

 
SELECT * FROM RECORDS WHERE id <= wanted-x/2 AND id > wanted+x/2
 
...but it may not give me 10 records if some were removed. Any suggestions?
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Display x number of records with a specified id in the middl

Post by jaoudestudios »

Not sure what you are trying to do, but to limit your results to 10 records use the LIMIT command.
i.e.

Code: Select all

 
....LIMIT 10
 
LonelyProgrammer
Forum Contributor
Posts: 108
Joined: Sun Oct 12, 2003 7:10 am

Re: Display x number of records with a specified id in the middl

Post by LonelyProgrammer »

Example:I specify a userid of 100, so table will display the records of users with userid from 95 to 105.
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Display x number of records with a specified id in the middl

Post by jaoudestudios »

Example:I specify a userid of 100, so table will display the records of users with userid from 95 to 105.
So if you have 5 records deleted (i.e. 97,98,99,100,101), you will only be returned 5 results (i.e. 95,96,102,103,104), but you want it to always return 10? i.e continue until a limit of 10 is reached, in this case...105,106,107,108,109?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Display x number of records with a specified id in the middl

Post by VladSun »

Code: Select all

$query = "
(select * from records where id < ".$id." limit ".$delta.")
union 
(select * from users where id >= ".$id." limit ".($delta + 1).")
";
 
There are 10 types of people in this world, those who understand binary and those who don't
LonelyProgrammer
Forum Contributor
Posts: 108
Joined: Sun Oct 12, 2003 7:10 am

Re: Display x number of records with a specified id in the middl

Post by LonelyProgrammer »

This works beautifully. Thanks!
Post Reply