Page 1 of 1

Selecting 3 rows

Posted: Mon Mar 22, 2010 4:55 pm
by Big John
I'm trying to make an image gallery, the images in the database are stored as follows:
Id|name|description|path|categoryId

When a user clicks on a thumbnail, it opens up in a new page with the normal sized picture. I want to generate the links to the next picture and previous picture below the picture. The next and the previous links should link to the next and the previous picture in only the current category. The categoryid is stored in $_SESSION['catid']. Is it possible to fetch 3 rows with one query, the first one containing the previous picture, the second containing the current and the third containing the next picture.
For example if I have the table:

1|Etc1|description1|path1|1
2|Etc2|description2|path2|3
3|Etc3|description3|path3|5
4|Etc4|description4|path4|3
5|Etc5|description5|path5|2
6|Etc6|description6|path6|4
7|Etc7|description7|path7|3
8|Etc8|description8|path8|1

it would grab the rows in bold, when I have the current picture id which is 4 and the categoryid which is 3.

Thanks a bunch.

Re: Selecting 3 rows

Posted: Mon Mar 22, 2010 6:16 pm
by Sofw_Arch_Dev
You can select a specific number of rows by using LIMIT in your SQL query:

Code: Select all

 
SELECT Id,name,description,path FROM your_image_table WHERE categoryId=$myCatId LIMIT $myNumToReturn;
 
..where $myNumToReturn is the number of rows you want to return. The SELECT returns all rows matching the given WHERE clause, but the LIMIT restricts the result of the SELECT to the bounds you specify with the given parameter(s). Using LIMIT you can narrow the number of results back from a query, but this doesn't know anything about previous and next. I assume you have that logic handled.

Questions back to you: it looks like you only have a handful of images here. Are there more images? If so, will you find yourself wanting to select the NEXT three images from where you last selected last time?

Re: Selecting 3 rows

Posted: Tue Mar 23, 2010 8:45 am
by Big John
Thanks for the response.

Yes, I have more images. The problem with the LIMIT is that with one query, I can select the current and the next, but not the previous image, since I know the current picture's position among all the pictures, but not among the pictures with the same categoryid.
The query should select one image with the same categoryid before the current, the current, and one after the current picture. Is it even possible?

Thanks.

Re: Selecting 3 rows

Posted: Tue Mar 23, 2010 1:25 pm
by Sofw_Arch_Dev
Well you're going to have to do SOME work in PHP unless you want to model your image list in the db with a previous_id column and next_id column. Not recommended. Using LIMIT is how you "page" through results in a db result set.

Code: Select all

 
SELECT id,name,description,path FROM your_image_table WHERE categoryId=3 ORDER BY id LIMIT $startOffset, 3;
 
Using the above line, you could get a list of three images to satisfy your needs very easily.

Code: Select all

 
// given that you have N images and that pageNum is the 0-based index of a given image in the context of the entire list
$startOffset = ( $pageNum > 1 ) ? ( $pageNum - 1 ) : 0;
$sql = SELECT id,name,description,path FROM your_image_table WHERE categoryId=3 ORDER BY id LIMIT $startOffset, 3;
 
For the first (0'th) image, your set of three will include the current image, the next image, and one extra next image. No previous. Testing to see if you're on page 0 takes care of that. For the second image (index 1), your set of three will include the previous, current and next. For index 2.. smooth sailing. Add a little logic to detect when you get to the end of the list (i.e get only two results back) and you're fine.