Possible Duplicate:
MySql Row Number?
I have 10 names in my mysql table, I want to get the position of a particular name. I'm not finding any solution.
ID NAMES
1 APPLE
3 MANGO
5 PARROT
9 TREE
12 HOUSE
14 AIRPLANE
19 ROCKET
22 SHIP
38 BOAT
41 MOUNTAIN
Let's say i want the postion of HOUSE, the answer I should get is 5. But I'm unable to do this.
SELECT `rank`
FROM
(
select @rownum:=@rownum+1 `rank`, p.*
from TableName p, (SELECT @rownum:=0) r
order by ID
) s
WHERE names = 'house'
$query = "SELECT * FROM TABLE";
$result = mysql_query($query, );
$num = mysql_numrows($result);
$i = 0;
while($i < $num) {
$name = mysql_result($result, $i, "NAMES");
if($name == "House")
break;
$i++;
}
// $i contains position of "House"
mysql_ functions.This whole idea is actually wrong from a fundamental point of view. According to the relational model, a particular row doesn't have a certain position. This is why, if you execute a query, the rows may be returned by the RDBMS in any order unless you specify an ORDER BY clause. Yes, when you execute a query, the rows are always returned in the same order. But that's more a convention or coincidence, not a requirement. Relying on it would be a mistake.
So in your case, the position of HOUSE can be anything between 1 and N (N = number of rows).
SELECT * FROM table WHERE ID <= 5, then the rows will probably be returned in the order 1, 2, 3, 4, 5. Like I said in my post: that is a coincidence. It's also easier for the table's engine to return them that way. But, it's also correct if they would come in the order 3, 1, 4, 2, 5. And next time 5, 1, 3, 2, 4 and so on. Returning them in a random order is still compliant with the relational model, unless you specify an ORDER BY clause.
while($array){ $count++; }