CODEWITHSUNDEEP
phpmysqlsql
Mickey
Mickey

Reputation: 2405

PHP & MYSQL - GET POSITION OF ROW

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.

Upvotes: 0

Views: 1600

Answers (2)

MineSQL
MineSQL

Reputation: 25

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.

Upvotes: 0

Alma Do
Alma Do

Reputation: 37365

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

Upvotes: 2

Related Questions