Fetching 3 rows from table
Moderator: General Moderators
- phazorRise
- Forum Contributor
- Posts: 134
- Joined: Mon Dec 27, 2010 7:58 am
Fetching 3 rows from table
Hi there,
how can i fetch 3 records from table ? let me clear, suppose I've id=5 then i would like to fetch records whose id would be 4,5,6.
that means, i want to fetch previous and next records of current record.
what would be the query ?
how can i fetch 3 records from table ? let me clear, suppose I've id=5 then i would like to fetch records whose id would be 4,5,6.
that means, i want to fetch previous and next records of current record.
what would be the query ?
Re: Fetching 3 rows from table
Is this what you are interested in doing?
SELECT * WHERE id=4 OR id=5 OR id=6
or
SELECT * WHERE id>=4 AND id<=6
SELECT * WHERE id=4 OR id=5 OR id=6
or
SELECT * WHERE id>=4 AND id<=6
- phazorRise
- Forum Contributor
- Posts: 134
- Joined: Mon Dec 27, 2010 7:58 am
Re: Fetching 3 rows from table
nope.
i'll be having only 5. and from it i want to find previous and next record.
records are not necessarily be sequential like 4,5,6 or 1,2,3 etc. they can be like 3,5,9 and i've have 5 with me.
i'll be having only 5. and from it i want to find previous and next record.
records are not necessarily be sequential like 4,5,6 or 1,2,3 etc. they can be like 3,5,9 and i've have 5 with me.
Re: Fetching 3 rows from table
There's an underlying problem with your question. In a relational database like MySQL, the sequence of rows in a table is undefined. That means that you can't count on ANY particular order, even primary keys, in the underlying table. You can only trust the order in the results set from a query that establishes the order. The only way you can do what you are describing is by first determining which specific id numbers you want to find, which would mean you would need to execute a query sorted on the id field, then loop through the results set, retaining the most recent id value in a variable, until you reach the middle value (5, in your example), so you would have the previous value in the variable, then halt the loop; then you would also have the middle value, then you could fetch the next value, so you would now have the 3 values of id that you want. Then you could execute a query that selected only those 3 values.
- phazorRise
- Forum Contributor
- Posts: 134
- Joined: Mon Dec 27, 2010 7:58 am
Re: Fetching 3 rows from table
thanks califdon for the push. Instead of asking for the code/query it's better to try own. I'll write some code and ask again for it's efficency.
Re: Fetching 3 rows from table
Actually, you could just do it with one query: each time you fetch a row, you save that row as an array variable, replacing the previous one; when you hit your middle value, the previous row would be in the array variable, the current (middle) row would be in the row array you just fetched, and the next one could be obtained by just fetching it. All you have to do is take whatever action you're going to take (display it, for example) before you lose the value in your code logic.
Re: Fetching 3 rows from table
here is a possible solution (replace your-table and $your-id-in-hand for the proper values):
Another option a little more complex using the ROWNUM concept could be (posted for you to explore it and probably use the example in other circumstance)
SELECT # 1
The result from SELECT #1 should be the position of $your-id-in-hand in the evaluated resultset, and knowing that you can use the value (lets call it $position) in SELECT #2 to obtain the rows around it
SELECT #2
Other option as califdon said is make one select , store the data in array and post-process it to find the surrounding rows.
Code: Select all
(SELECT id FROM your-table
WHERE id < $your-id-in-hand
ORDER BY id DESC LIMIT 1)
UNION
(SELECT id FROM your-table
WHERE tst2id >= $your-id-in-hand
ORDER BY id LIMIT 2);SELECT # 1
Code: Select all
SELECT x.*
FROM (SELECT t.id,
@rownum := @rownum+1 as rownum
FROM your-table t,
(SELECT @rownum := 0) r WHERE t.id <= $your-id-in-hand ORDER BY t.id) x
WHERE x.id = $your-id-in-hand;SELECT #2
Code: Select all
SELECT x.*
FROM (SELECT t.id,
@rownum := @rownum+1 as rownum
FROM your-table t,
(SELECT @rownum := 0) r ORDER BY t.id) x
WHERE x.rownum between $position - 1 AND $position + 1; // here you can play with how many surrounding rows you want- phazorRise
- Forum Contributor
- Posts: 134
- Joined: Mon Dec 27, 2010 7:58 am
Re: Fetching 3 rows from table
well after previous post i did wrote a small code that gives me desired output. here's it -
the problem i see in above method is 'select id....' query returning all ids. What i was looking for is just 3 fields.
thanks mikosiko -
[text](SELECT id FROM your-table
WHERE id < $your-id-in-hand
ORDER BY id DESC LIMIT 1)
UNION
(SELECT id FROM your-table
WHERE tst2id >= $your-id-in-hand
ORDER BY id LIMIT 2);[/text] this query work perfectly for me.
About other two methods you've mentioned, i'm studying how can I use 'em.
Code: Select all
$id=15; // i have this in hand
$link=array(); //to store desired 3 row's id for further process.
//$res holds all ids in table from previous 'select id .... ' query
while($row=mysql_fetch_array($res)){
if($row['id']<$id){
$link['1']=$row['id'];
}
if($row['id']==$id){
$link['2']=$row['id'];
}
if(empty($link['3'])){
if($row['id']>$id){
$link['3']=$row['id'];
}
}
}
foreach($link as $i=>$v){
echo $i .'=>'.$v.' <br/>';
}
thanks mikosiko -
[text](SELECT id FROM your-table
WHERE id < $your-id-in-hand
ORDER BY id DESC LIMIT 1)
UNION
(SELECT id FROM your-table
WHERE tst2id >= $your-id-in-hand
ORDER BY id LIMIT 2);[/text] this query work perfectly for me.