1

Trying to figure out how to include a return for the row position in the result set. It is a very simple table that is ordered by computer_id with a WHERE clause. All I am looking for is something like (*row position*) as position in the results for each.

I found a few things by searching here on SO, but nothing I was able to get working.

$stmt = $db->prepare("
    SELECT *
    FROM computers
    WHERE account_id = :account_id
    ORDER BY computer_id    
");

$stmt->bindValue( ':account_id', $_SESSION['user']['account_id'] );
$stmt->execute();   

$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo '<pre>',print_r($results,1),'</pre>';
user756659
  • 3,372
  • 13
  • 55
  • 110
  • I guess you are asking for row number against each row returned by query ? – M Khalid Junaid May 16 '14 at 18:45
  • Would the auto incremented key field of this table not explicitly match your data row, or have you **gasp** done deletes and gotten your key vs row position out of sync? – mituw16 May 16 '14 at 18:47

2 Answers2

1

You can do what you want in one query (which the referenced duplicate question does not do):

SELECT (@rn := @rn + 1) as rownumber, c.*
FROM computers c cross join
     (select @rn := 0) var
WHERE account_id = :account_id
ORDER BY computer_id;

To get a row number across all rows, use a subquery:

SELECT c.*
FROM (SELECT (@rn := @rn + 1) as rownumber, c.*
      FROM computers c cross join
           (select @rn := 0) var
      ORDER BY computer_id
     ) c
WHERE account_id = :account_id;

Note that this will take more time, because it has to process the entire table.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This is working, but I am losing the correct order when ordering by other columns. Is it possible to set the rownumber by computer_id as its own table where that reference will always be the correct rownumber? Hopefully that makes sense. – user756659 May 16 '14 at 20:50
  • The second option is working for me although the rownumber is off. For instance, I have 10 entries in `computers`.. the first 6 belong to account_id = 1 and the last 4 belong to account_id = 2. Showing for the second account is correct, but the rownumber starts at 7...8,9,10 instead of 1,2,3,4. Ideas? – user756659 May 16 '14 at 22:33
  • I was able to fix the problem I mentioned by moving the where clause into the select statement... it would not process the entire table this way either. – user756659 May 16 '14 at 22:50
0

Since this is PHP you could just create a variable, iterate through the results and increment the variable as you go.

There's also this: ROW_NUMBER() equivalent in MySQL for inserting

which gets you something similar to row_number() in MS SQL.

Community
  • 1
  • 1
RyanB
  • 757
  • 4
  • 11