General Question

molave's avatar

Does MySQL have a native counter function for its results?

Asked by molave (147points) 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

Skyrail's avatar

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?

Vincentt's avatar

I’m no MySQL guru, but wouldn’t something like

SELECT COUNT(*) AS numer_of_results FROM table WHERE condition=true

do that?

molave's avatar

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…

Vincentt's avatar

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.

Skyrail's avatar

Ah very nice Vincentt, I’m no guru either, it now makes more sense what you wanted to do molave :)

Answer this question




to answer.

This question is in the General Section. Responses must be helpful and on-topic.

Your answer will be saved while you login or join.

Have a question? Ask Fluther!

What do you know more about?
Knowledge Networking @ Fluther