Fetching 3 rows from table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
phazorRise
Forum Contributor
Posts: 134
Joined: Mon Dec 27, 2010 7:58 am

Fetching 3 rows from table

Post 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 ?
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Re: Fetching 3 rows from table

Post 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
User avatar
phazorRise
Forum Contributor
Posts: 134
Joined: Mon Dec 27, 2010 7:58 am

Re: Fetching 3 rows from table

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Fetching 3 rows from table

Post 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.
User avatar
phazorRise
Forum Contributor
Posts: 134
Joined: Mon Dec 27, 2010 7:58 am

Re: Fetching 3 rows from table

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Fetching 3 rows from table

Post 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.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Fetching 3 rows from table

Post 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.
User avatar
phazorRise
Forum Contributor
Posts: 134
Joined: Mon Dec 27, 2010 7:58 am

Re: Fetching 3 rows from table

Post 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.
Post Reply