Sometimes I’ve needed to add a counter column in a query and I don’t want to add the column with php, so I’ve found this query to put the counter directly in the record set returned out from the query.
set @N = 0; SELECT @N := @N +1 AS number, name, surname FROM people;
To use it in PHP scripts you have to use 2 query statements:
mysql_query("set @N = 0;");
$rs = mysql_query("SELECT @N := @N +1 AS number, name, surname FROM people");
while ($r=mysql_fetch_array($rs)) {
echo $r['number']." - ".$r['name']." ".$r['surname']."<br/>";
}
Related posts:
January 24th, 2010 3:35 pm
Thank you for interesting information. I was searching this information for a long time.
January 25th, 2010 2:37 pm
Super simple and efficient!!
Great post!
February 4th, 2010 5:56 pm
Thank you! I added this page to bookmark)) I think would be useful …
March 2nd, 2010 6:54 am
thank you
nice post. Do more
April 6th, 2010 8:25 am
This is cool! nice post! bookmarked it