+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: MySQL question: select 2 id's before and 2 id's after current id (/showthread.php?tid=3305)
MySQL question: select 2 id's before and 2 id's after current id - El Forum - 09-23-2007
[eluser]codex[/eluser]
Well, that's basically what I need to do.
I'm working on a small image gallery and I want to select (for instance) the 2 photo id's before and the 2 photo id's after the current photo id, making it show 4 thumbnails. But not the thumb of the current photo.
How do I do this in mysql?
MySQL question: select 2 id's before and 2 id's after current id - El Forum - 09-23-2007
[eluser]imzyos[/eluser]
something like this, not elegant but it works !
SELECT * FROM images WHERE id >=3 AND id !=5 LIMIT 4
KISS = Keep It Simple, Stupid ^^
Example
Code:
$current = 5;
$this->db->select('*')->from('images')->where('id>', $current-3)->where('id!=',$current)->limit(4);MySQL question: select 2 id's before and 2 id's after current id - El Forum - 09-23-2007
[eluser]codex[/eluser]
[quote author="imzyos" date="1190587287"]something like this, not elegant but i works !
SELECT * FROM images WHERE id >=3 AND id !=5 LIMIT 4
KISS = Keep It Simple, Stupid ^^
Example
Code:
$current = 5;
$this->db->select('*')->from('images')->where('id>', $current-3)->where('id!=',$current)->limit(4);So maybe I AM stupid, but if the only thing you've got is the current id (5 in your example), where does the 3 in your example come from?
MySQL question: select 2 id's before and 2 id's after current id - El Forum - 09-23-2007
[eluser]nmweb[/eluser]
The 3 comes from the 5 (current) minus the 2 you want to see from the lower end, is indeed 3. Flaw in this approach is that it assumes that all records have id and no record is missing. I.e. there are no gaps in your database. Given this, the approach would work.
Code:
1|2|3|4|5|6|7|8
-|-|x|x|-|x|x|-Code:
1|2|3|5|6|7|8
-|-|x|-|x|x|xMySQL question: select 2 id's before and 2 id's after current id - El Forum - 09-23-2007
[eluser]codex[/eluser]
Yeah, that's what I figured too. I don't think it can be done in one query. Thanks for the reply!
MySQL question: select 2 id's before and 2 id's after current id - El Forum - 09-23-2007
[eluser]obsesif[/eluser]
what if rows were deleted? +/- selecting won't work then.
MySQL question: select 2 id's before and 2 id's after current id - El Forum - 09-23-2007
[eluser]Rick Jolly[/eluser]
Yea, maybe 2 queries isn't so bad. Would this work?
Code:
"select id from images where id < $current_id order by id limit 2"
"select id from images where id > $current_id order by id limit 2"MySQL question: select 2 id's before and 2 id's after current id - El Forum - 09-23-2007
[eluser]imzyos[/eluser]
Codex visit this KISS PRINCIPLE KISS its an acronym, YOU ARE NOT STUPID my friend, and yeah if some id are missing the query will fail ¬¬, must be a way to get it.
MySQL question: select 2 id's before and 2 id's after current id - El Forum - 09-23-2007
[eluser]codex[/eluser]
[quote author="imzyos" date="1190591867"]Codex visit this KISS PRINCIPLE KISS its an acronym, YOU ARE NOT STUPID my friend, and yeah if some id are missing the query will fail ¬¬, must be a way to get it.[/quote]
Yeah, I know what KISS is. I wasn't taking it personally :-)
MySQL question: select 2 id's before and 2 id's after current id - El Forum - 09-23-2007
[eluser]codex[/eluser]
[quote author="Rick Jolly" date="1190591293"]Yea, maybe 2 queries isn't so bad. Would this work?
Code:
"select id from images where id < $current_id order by id limit 2"
"select id from images where id > $current_id order by id limit 2"This will work, but I was trying to do it in one well crafted query, which is impossible I guess.