Returning row numbers

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
irisblaze
Forum Newbie
Posts: 22
Joined: Sun Mar 19, 2006 3:24 am
Location: Palestine
Contact:

Returning row numbers

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Returning row numbers

Post 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?
User avatar
irisblaze
Forum Newbie
Posts: 22
Joined: Sun Mar 19, 2006 3:24 am
Location: Palestine
Contact:

Post 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 :(
Last edited by irisblaze on Mon Apr 17, 2006 4:04 am, edited 1 time in total.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

Code: Select all

$n = 1;
while ($row = mysql_fetch_row()) {
    echo $n . '-' . $row[id'] . '<br/>';
    ++$n;
}
(#10850)
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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;
(#10850)
User avatar
irisblaze
Forum Newbie
Posts: 22
Joined: Sun Mar 19, 2006 3:24 am
Location: Palestine
Contact:

Post 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?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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()
User avatar
irisblaze
Forum Newbie
Posts: 22
Joined: Sun Mar 19, 2006 3:24 am
Location: Palestine
Contact:

Post 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
Post Reply