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…

January 21, 2010

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/>";
}

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:-)

Comments are closed

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 to read facebook likes count from PHP

When you add facebook like button to your site, probably, you also want to save the number of likes of…

October 8, 2012