Row Position in mysql/php

Question

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.

Show us [what you've tried](http://www.whathaveyoutried.com). — Kermit, 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 00:00
Possible dup http://stackoverflow.com/questions/3126972/mysql-row-number — Nick Rolando, Nov 15 '12 at 00:00

score 3 · Accepted Answer · answered Nov 15 '12 at 00:00

3

SELECT `rank`
FROM
(
  select @rownum:=@rownum+1 `rank`, p.* 
  from TableName p, (SELECT @rownum:=0) r 
  order by ID 
) s
WHERE names = 'house'

SQLFiddle Demo

answered Nov 15 '12 at 00:00

John Woo

258,903
69
498
492

I would -1 for being too empathetic, but since your avatar picture has a dog... – Kermit Nov 15 '12 at 00:00

score 0 · Answer 2 · answered Nov 15 '12 at 00:04

0

$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"

answered Nov 15 '12 at 00:04

Zillolo

204
2
7

1

I would not recommend advising the OP to use `mysql_` functions. – Kermit Nov 15 '12 at 00:25

score -1 · Answer 3 · answered Nov 15 '12 at 00:13

-1

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

answered Nov 15 '12 at 00:13

Radu Murzea

10,724
10
47
69

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 00: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

Row Position in mysql/php

3 Answers3