PHP & MYSQL - GET POSITION OF ROW

Question

Can you return the position of the row in comparison to the previous search. For example if I did a simple query: select * from table where name='aa'

it would return

+--+----+---+
|id|name|cid|
+--+----+---+
| 1|  aa|  2|
| 5|  aa|  2|
| 8|  aa|  2|
| 9|  aa|  2|
|10|  aa|  2|
+--+----+---+

If I changed that query to: select * from table where name='aa' and id='5'

it would return

+--+----+---+
|id|name|cid|
+--+----+---+
| 5|  aa|  2|
+--+----+---+

now according to the first search for where name='aa' there are 5 rows with and id='5' being the second row.

So, is there a way to get the position of the row in comparison to initial set? I'd like to be able to print "Result 2 of 5". I can get the number 5 by counting the total rows. I just need to get the position of the and id='5'. Which I know is the second row of the initial set but I can't seem to figure out a way to return that information. Forgive me if I'm not explaining this properly as I don't quite know how to ask this question.

The concept of position is pretty much meaningless in SQL, which is built around set theory. Why would you want to do this anyway? — GordonM, Dec 25 '13 at 08:44
Remember - there's no "native order" in SQL. There's "order by some column". And you need to decide which column to use — Alma Do, Dec 25 '13 at 08:45
Normally, in displaying results from mysql when I'm displaying sets or paginating the data I use limit and can very easily get the position of the row. With the application I'm working on now I just need to get the current, next and previous result so there is no "limit" in the query, and nothing to base position off of. — Mickey, Dec 25 '13 at 08:46
You're referring to some way, which DBMS stores your data. But let my clarify - is your order defined by `id` column? I.e. by "second" in row set you mean "there's one row with id lesser than this" — Alma Do, Dec 25 '13 at 08:48
By "second" I mean, `and id=5` pulls the second row of the initial set of data provided by `where name='aa'` — Mickey, Dec 25 '13 at 08:50
As others have noted SQL will give you the results without any hard ordering guarantee. You can enforce that by using `ORDER BY` in your query, which will sort them relative to each other. You can then take the results one by one in your host app, etc. with `mysqli_fetch_row` in PHP. If you want to preserve the order by which each row was entered in the DB, you need to introduce a new column. — rath, Dec 25 '13 at 17:56

score 2 · Accepted Answer · answered Dec 25 '13 at 08:55

2

Despite that fact, that natural order has no sense in SQL - it seems, you're referring to ordering by id column. Then your position is just count of rows with id lesser or equal to this:

SELECT 
  *, 
  (SELECT COUNT(1) FROM `table` WHERE name='aa' AND id<=5) AS position
FROM 
  `table` 
WHERE 
  name='aa' AND id=5

answered Dec 25 '13 at 08:55

Alma Do

37,009
9
76
105

I'm not ordering by ID but I understand what your example does and it worked for exactly what I needed. Thanks!! – Mickey Dec 25 '13 at 09:03
This is all I actually needed: `select count(1) from table where name='aa' and x<=X` with `x` being the column that i'm ordering by and `X` being the value for the current row i need the position of in relation to the fullset that `select * from table where name='aa'` provides. – Mickey Dec 25 '13 at 09:07

score 0 · Answer 2 · edited Dec 25 '13 at 17:51

0

You could use the first SQL statement, then if you are wanting to search for the id of 5 in that specific name you could use a small loop, for example:

    $rows = $stmt->FetchAll() //Your Fetched Rows
    $cnt = 1;
    foreach ($rows as $row) {
        if ($row['id']==$id) {
            return $cnt
        } else {
            $cnt++
        }
    }

I am not sure if SQL is capable of handling more complex situations as the one you are describing, but maybe someone here has more knowledge in this area than me.

edited Dec 25 '13 at 17:51

Ahmed Siouani

13,701
12
61
72

answered Dec 25 '13 at 08:50

MineSQL

25
6

Yeah I was thinking of doing something along the lines of this but I just feel like there is someway in mysql to return the position of the `and id='5'` in comparison to the `where name='aa'`, idk though. – Mickey Dec 25 '13 at 08:52
Here, I think I may have found something for you: http://stackoverflow.com/questions/3614666/mysql-get-row-position-in-order-by – MineSQL Dec 25 '13 at 08:54

PHP & MYSQL - GET POSITION OF ROW

2 Answers2