How can i get ( previous, current and next) data from mysql records?

[eluser]K-coder[/eluser]
Hello,

I'm looking for smart solution to get previous, current and next data from database, for example in flickr.com you can go to next or previous photo by clicking on this photo

example: http://i53.tinypic.com/1s00hc.png

I can do something like that but it's going to make 3 queries in database and i'm looking for something optimize queries.

[eluser]jrtashjian[/eluser]
Best way to do this is just to grab rows from the database just like you would for pagination. Instead of displaying numbers though, you'd be displaying the image. Make sense?

[eluser]Christophe28[/eluser]
Lucky you! I just programmed something like this! :-)

First you need to know the id of the current file of course.

So to get the info of the current file from (for example) the files table you get:

Code:

$q = "SELECT * FROM files WHERE id = ?";
$current_photo = $this->db->query($q, array(//your_file_id))->row();

To get the next file you write:

Code:

$q = "SELECT * FROM files WHERE id > ? ORDER BY id ASC LIMIT 1";
$next_photo = $this->db->query($q, array(//your_file_id)->row());

To get the previous file you write:

Code:

$q = "SELECT * FROM files WHERE id < ? ORDER BY id DESC LIMIT 1";
$previous_photo = $this->db->query($q, array(//your_file_id)->row());

This is the proper way to do this ;-)

Please let us know if works!

Christophe

[eluser]Christophe28[/eluser]
OOps, I think I may have misunderstood you. I thought you needed something like a navigation through photos. But what are you searching for? 3 results in one query including the current photo, previous and next?

Christophe

[eluser]K-coder[/eluser]
@jrtashjian
Unfortunately i don't understand what does you mean exactly!

by the way, all i have is current id and it's coming from URL
http://sitename.com/product/details/5

and if i do something like pagination how can i get the current id to display the product details?

I know that it's complicated!



@Christophe28
this way make 3 queries and i think it's a lot because i have another 3 queries in same page

And yes i'm need something like navigation and not taking more than 1 query to get (previous, current and next) OR (previous and next) Only



Thanks guys for your help

[eluser]dudeami0[/eluser]
Try this (Untested, but could be useful)

Code:

$rowid = 1; // 0 = First entry
$sql = "SELECT * FROM `table` LIMIT " . ($rowid-1) . ",3";
$this->db->query($sql);

That only works if you know the row number though, or there is no gaps in IDs. Might not be exactly what your looking for, but doing 3 queries was never a bad thing

[eluser]K-coder[/eluser]
@dudeami0

Thank you so much that is what i want

but your code need a little change to working fine

Code:

$rowid = 1; // 0 = First entry
$sql = "SELECT * FROM `table` LIMIT " . ($rowid-2) . ",3";
$this->db->query($sql);

and that what i did to make it more fixable

Code:

$from = ($id - 2 < 0 ? 0 : $id - 2);
$limit = ($id - 2 < 0 ? 2 : 3);
$this->db->limit($limit, $from);
$query = $this->db->get('item_media');

[eluser]dudeami0[/eluser]
Glad I could help ya