Page 1 of 1

Returning row numbers

Posted: Sun Apr 16, 2006 3:36 pm
by irisblaze
hello, I want help for this problem:

when executing the following query on some table this is what i get:

Query:

Code: Select all

SELECT id FROM [i]tablename[/i] WHERE tid='804'
Result:

id
11345
11348
11350
11351
11352
11353

Now I want:
1. to return the result like this:

rownum-id
1 -11345
2-11348
3-11350
............
6-11353

2. to select from the result the rownum of the id 11352 for example which it should return 5

now don't tell me to use PHP code to make a counter and check the values, this query has returned only 6 records but if I change the WHERE clause it would return thousends of records, so I want mysql solution please, and I want it for an old mysql server

Re: Returning row numbers

Posted: Sun Apr 16, 2006 6:58 pm
by RobertGonzalez
irisblaze wrote:now don't tell me to use PHP code to make a counter and check the values, this query has returned only 6 records but if I change the WHERE clause it would return thousends of records, so I want mysql solution please, and I want it for an old mysql server
When you say old, which version are you talking about? Not sure if it will help you, but this tidbit from the mysql manual might be of assistance. It is referencing a C API so I'm not sure how it could help you, but it might.

You might also want to take a look at the MySQL manual section on functions.

PS What is your objection to doing this code-side?

Posted: Mon Apr 17, 2006 12:53 am
by irisblaze
Everah wrote:When you say old, which version are you talking about?
3.23 :oops: well.. I have a newer version on my site server, but on my pc i only have that, and mysql is to large (17-36 MB) to download on a hectic internet connection.
You might also want to take a look at the MySQL manual section on functions.
Nothing useful i could find :(

Posted: Mon Apr 17, 2006 1:22 am
by Christopher

Code: Select all

$n = 1;
while ($row = mysql_fetch_row()) {
    echo $n . '-' . $row[id'] . '<br/>';
    ++$n;
}

Posted: Mon Apr 17, 2006 1:39 am
by RobertGonzalez
That's a good way to do it, but as the original poster posted...
irisblaze wrote:now don't tell me to use PHP code to make a counter and check the values...
For some reason the OP is against using PHP code to do the work.

Posted: Mon Apr 17, 2006 1:47 am
by Christopher
How about inserting the selected results into a temporary table with an autoincrement int field and a field to hold 'id'. The SELECT CONCAT(counter, '-', id) FROM tmptable;

Posted: Mon Apr 17, 2006 4:02 am
by irisblaze
arborint wrote:How about inserting the selected results into a temporary table with an autoincrement int field and a field to hold 'id'. The SELECT CONCAT(counter, '-', id) FROM tmptable;
well... this what i'm going to do anyway, it seems no possible way to do what I wanted except this, thanx, but don't you agree with me that mysql developers should present an easy way to do this? after all mysql server goes into a loop to return the results? why we should do the loop again after it's already done?

Posted: Mon Apr 17, 2006 9:27 am
by John Cartwright
irisblaze wrote:
arborint wrote:How about inserting the selected results into a temporary table with an autoincrement int field and a field to hold 'id'. The SELECT CONCAT(counter, '-', id) FROM tmptable;
well... this what i'm going to do anyway, it seems no possible way to do what I wanted except this, thanx, but don't you agree with me that mysql developers should present an easy way to do this? after all mysql server goes into a loop to return the results? why we should do the loop again after it's already done?
I personally don't see the usefulness of such a function.. I mean, your going to be looping your results anyway when displaying/storing them anyways, correct?

Personally, I always have my db abstraction layer return an array of the selected results, that way I can involk any alterations to the values in one swoop with array_walk()

Posted: Wed Apr 19, 2006 7:15 pm
by irisblaze
Jcart wrote:
I personally don't see the usefulness of such a function.. I mean, your going to be looping your results anyway when displaying/storing them anyways, correct?
No not correct, I wanna display 5 results at a time so I'll be using limit start, 5 still I want to know the location of a certain record