Does MySQL have a native counter function for its results?
Asked by
molave (
147)
June 5th, 2008
I’d like my results to be numbered the same way Excel rows are numbered. Is this possible?
Observing members:
0
Composing members:
0
5 Answers
I know that with PHP when you query the database and it returns any results it’ll provide an array of results I think so you can loop through them and get the data from them, however nativly I don’t know what MySQL returns from a query and whether it numbers the rows it finds, I’ll have a quick look on the MySQL site and report what I find, if anything at all :) what langauge are you programming in by the way?
I’m no MySQL guru, but wouldn’t something like
SELECT COUNT(*) AS numer_of_results FROM table WHERE condition=true
do that?
Hi Skyrail and Vincent, thanks for the help so far! Skyrail, I work in the ubiquitous PHP5/MySQL5 environment.
In a blog somewhere I found a post that said proposed a solution in which you first set a variable, and then have that variable auto-increment, like so:
set @cnt := 0;
select @cnt := @cnt + 1 as cnt, myfield from mytable;
Whew, until an hour ago, I did not even know you could set variables in MySQL. In any case I’m trying the solution on my MySQL Query Browser. Doesn’t seem to work… But my friend, on his PC, says it does work. Go figure.
THanks again for the help. The search continues…
Ah, now I understand what you’re getting at. Sorry, I’m no native speaker…
Anyway, what you did also worked for me. I used the following query in PMA:
set @cnt := 0; SELECT @cnt := @cnt + 1 as cnt, action FROM `block` WHERE location=‘core’
I’ve taken a screenshot of the query in PHPMyAdmin.
I’m using PHP 5.2.3 and MySQL 14.12 on Xubuntu 7.10.
Ah very nice Vincentt, I’m no guru either, it now makes more sense what you wanted to do molave :)
Answer this question
This question is in the General Section. Responses must be helpful and on-topic.