MYSQL add counter in a query

Use mysql variables to create a counter in SQL, PHP code to use an SQL counter and what does the i mean in mysqli?

November 9, 2019

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.

Author

I'm a software engineer, an everyday web developer and a maker. I usually build sites with PHP, within or without WordPress. I build Internet of Things with Arduino and ESP8266. I'm the founder of Rockit.it and Dailybest.it and I'm actually the Chief Technical Officer of Better Days web agency.

Comments on “MYSQL add counter in a query”

9 thoughts

  1. Lesya says:

    Thank you for interesting information. I was searching this information for a long time.

  2. Scooby says:

    Super simple and efficient!!
    Great post!

  3. Sara says:

    Thank you! I added this page to bookmark)) I think would be useful …

  4. wayne says:

    thank you
    nice post. Do more

  5. Fahmi says:

    This is cool! nice post! bookmarked it :)

  6. You have a great website! In tough economic times such as these, emergencies may arise which require you to take a harder look at getting a fast cash loan, even if it means high interest charges. Finding a payday loan company that suits your needs and circumstances is essential.

  7. Frank says:

    Thanks for your suggestion, it helped me in creating an update query:

    set @N = 0;
    update [sometable] set `order`= @N := @N +1;

    This query updates the [sometable] table by setting the column `order` from 1 to n. I needed it to fill in the values initially.

  8. sotiris zegiannis says:

    Great tip.Thank you but there seems to be a small issue.Every time i run the script the counter does not reset but continues from the last number from the last query.

  9. sotiris zegiannis says:

    Never mind.My mistake:-)

Leave a Reply

Your email address will not be published. Required fields are marked with an *

Recommended

10 PHP usefull functions for MySQLi stuff (mysql improved)

I’ve modified the previous post to use mysqli instead of mysql. Was it necessary? I think it was good step…

January 29, 2010

How to bring back tweet counters

Twitter said that deliver the tweet count for every button is too complex (costs too much) and it’s not really a correct…

November 24, 2015

MySQL fulltext search always empty

If you’ve just switched to FULLTEXT indexes and you’ve just started playing with MATCH... AGAINST syntax, but you always get…

November 23, 2013

How to remove custom fields from WordPress

Ok. You’ve added custom fields in wordpress, but how can you remove them? How to delete custom fields wordpress Two…

September 29, 2013

13 mysql tips that you don’t know

Here is a list of thirteen tips that can be usefull for your queries. If you know more tips send…

October 19, 2012

How many times a web link has been shared on Twitter

Twitter share button and Facebook share button are the most used buttons to share links on Internet. You can read…