MYSQL add counter in a query

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.

You can try it in your phpMyAdmin “Run SQL query/queries on database” tab.

set @N = 0; 
SELECT @N := @N +1 AS number, name, surname FROM people;

The first line set a variable @N to be used as the counter. In the SELECT statement the variable is incremented. To use it in PHP scripts you have to use 2 query statements:

$conn->query("set @N = 0;");
$sql = "SELECT @N := @N +1 AS number, name  FROM people limit 0,10";
if ($rs = $conn->query($sql)) {
  while($r = $rs->fetch_array()) {
    echo $r['number']." - ".$r['name']."<br/>";
  }
}

In the above code the $conn object is the mysqli object with the connection. When we run the first query set @N = 0; the variable is stored in the $conn object and can be used in the next query.

Creating variables in SQL statements could simplify your PHP code by reducing the number of lines. Sometimes it could also help you to solve problems directly in SQL, without using PHP.

If you want to find some PHP functions to handles database problems you can read this article about 10 useful function in PHP for mysqli, you can find the function to connect to a db, a script to repair and optimize tables, a useful function to convert a recordset to an HTML table, a function to get values of an ENUM field or a SET field and many more.

What does the i mean in mysqli?

When mysql for php was created there wasn’t the “i”.

Mysqli is the evolution of the mysql extension used in PHP developer enviroment, the “i” character added just stands for “improved“.

[POST UPDATE 2019/11/10] At this moment (2019) nobody still uses Mysql and everybody uses Mysqli, why? Because Mysqli gives you prepared statements – a safer way of sending data to Mysql and protecting you from SQL injection.