Page 1 of 1

Fetching 3 rows from table

Posted: Tue Sep 06, 2011 1:08 am
by phazorRise
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 ?

Re: Fetching 3 rows from table

Posted: Tue Sep 06, 2011 7:53 am
by Eric!
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

Re: Fetching 3 rows from table

Posted: Tue Sep 06, 2011 12:09 pm
by phazorRise
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.

Re: Fetching 3 rows from table

Posted: Tue Sep 06, 2011 7:42 pm
by califdon
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.

Re: Fetching 3 rows from table

Posted: Wed Sep 07, 2011 1:02 pm
by phazorRise
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

Posted: Wed Sep 07, 2011 2:46 pm
by califdon
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

Posted: Wed Sep 07, 2011 2:51 pm
by mikosiko
here is a possible solution (replace your-table and $your-id-in-hand for the proper values):

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);
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

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;
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

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
Other option as califdon said is make one select , store the data in array and post-process it to find the surrounding rows.

Re: Fetching 3 rows from table

Posted: Thu Sep 08, 2011 8:27 am
by phazorRise
well after previous post i did wrote a small code that gives me desired output. here's it -

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/>';
}
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. :D About other two methods you've mentioned, i'm studying how can I use 'em.