Take the 2-minute tour ×

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.

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.

share|improve this question
Show us what you've tried. –  FreshPrinceOfSO Nov 14 '12 at 23:58
can't you use a counter in your foreach or while? That count would be your position ... while($array){ $count++; } –  Zak Nov 15 '12 at 0:00
Possible dup stackoverflow.com/questions/3126972/mysql-row-number –  Shredder Nov 15 '12 at 0:00
@Zak The OP can use a variable. –  FreshPrinceOfSO Nov 15 '12 at 0:01
add comment

marked as duplicate by Shredder, FreshPrinceOfSO, SomeKittens Ux2666, Mr. Alien, fancyPants Nov 15 '12 at 9:58


3 Answers

up vote 3 down vote accepted
SELECT `rank`
  select @rownum:=@rownum+1 `rank`, p.* 
  from TableName p, (SELECT @rownum:=0) r 
  order by ID 
) s
WHERE names = 'house'
share|improve this answer
I would -1 for being too empathetic, but since your avatar picture has a dog... –  FreshPrinceOfSO Nov 15 '12 at 0:00
add comment
$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")


// $i contains position of "House"
share|improve this answer
I would not recommend advising the OP to use mysql_ functions. –  FreshPrinceOfSO Nov 15 '12 at 0:25
add comment

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

share|improve this answer
How that is even possible, when I already have an ID for each names and any new entry would be added after the existing names, so my position remains fixed. –  user1731476 Nov 15 '12 at 0:16
@user1731476 Let's say you have a table with many rows and an ID as primary key. If you would do 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. –  Radu Murzea Nov 15 '12 at 10:48
add comment
