Sometimes We’ve needed to add a counter column in a query and We don’t want to add the column with PHP-MySQL, so We need a query to put the counter directly in the record set returned out from the query.
For that purpose we have to 2 options :
1. In single query we can
SELECT @rownum := @rownum + 1 AS number, name, email FROM xyz_table , (select @rownum := 0) r
Example :
$result = mysql_query("SELECT @rownum := @rownum +1 AS number, name, email FROM xyz_table , (select @rownum := 0) r");
while ($r=mysql_fetch_array($result )) {
echo $r['number']." - ".$r['name']." ".$r['email']."<br />";
}
2. We run 2 query statements
set @rownum = 0; SELECT @rownum := @rownum +1 AS number, name, email FROM xyz_table;
To use it in PHP scripts you have to use 2 query statements for mysql interface .
Example :
mysql_query("set @rownum = 0;");
$result = mysql_query("SELECT @rownum := @rownum +1 AS number, name, email FROM xyz_table");
while($r=mysql_fetch_array($result )) {
echo $r['number']." - ".$r['name']." ".$r['email']."<br />";
} 