CODEWITHSUNDEEP
phpmysql
Jogn Smit
Jogn Smit

Reputation: 87

How to add increment to the mysql php results

I'm displaying 8 rows from my MySql table, and I'm wondering how I can add numbers in front of each result, like increments 1. - 8. ?

Here's my MySql query line:

$query=mysql_query("SELECT * FROM (SELECT score,pid from score ORDER BY score ASC) As temp GROUP BY temp.pid ORDER BY temp.score ASC LIMIT 8");

Upvotes: 1

Views: 338

Answers (2)

Þaw
Þaw

Reputation: 2057

In your PHP you can do something like this:

$i = 1;
$query=mysql_query("SELECT * FROM (SELECT score,pid from score ORDER BY score ASC) As temp GROUP BY temp.pid ORDER BY temp.score ASC LIMIT 8");
while($row = mysql_fetch_assoc($query))
{
    echo "<tr>
              <td>". $i ."</td>
              <td>". $row['field'] ."</td>
              <td>". $row['another_field'] ."</td>
              <td>". $row['another_field'] ."</td> 
         </tr>";

    $i += 1;
}

Upvotes: 0

juergen d
juergen d

Reputation: 204746

SELECT @rank := @rank + 1 as row_number, temp.* 
FROM 
(
   SELECT score,pid 
   from score 
   ORDER BY score ASC
) As temp, (select @rank := 0) r 
GROUP BY temp.pid 
ORDER BY temp.score ASC 
LIMIT 8

Upvotes: 3

Related Questions