Retrieve current record number from a mysql query

Rik_Comery

Hi. Is it possible to retrieve the current record number from a mysql result set.

I will elaborate. Let's say I have performed a query which returns the following records

ID   Name

1    Rik
5    Cath
27   James
45   Cameron

When looping though the resultset, i want to be able to display the info for Cath, and show that this is record 2.

I have tried using a FOR loop, setting a parameter named X to 1, and incrementing it by 1 everytime we move to the next record, but for lengthy reasons i won't go into here, i really need the actual record number

Drakla

Are you defining the current record as just the order retrieved by? As if you did a order by, then did the same order desc, then cath would be record 2 at one time, record 3 the next. If it is as simple as the first scenarion

$q "SELECT * from table";
$res = mysql_query($q) or die('booo');

$count = 1;

while ($row = mysql_fetch_assoc($res))
{
    echo 'Record ', $count++, ' is id:', $row['id'], ' name:', $row['name'], '<br />';
}

Rik_Comery

Thanks Drakla. However i have already tries something similar. I will tell you what i am trying to achieve. Maybe that will help. (or maybe you will see i am going about it all wrong!)

I have a table with many columns, one of which is "Type". I have performed a query to return all records where TYPE is "Hardwood".

What i am trying to do is display on my page, all the information about the first record only. Then down the bottom of the page, there is a link labelled "NEXT" which when clicked will re-load the same page, but show all the information about the second record.

To enable the page to know what record i want displayed on the page, i have a URL parameter "ID=XX" (ID is an auto_increment field in the database)

I wanted the current record number in hope i could find a way to include in the "NEXT" link, the record number of the next record. Does that make sense?

I should probably add that the query orders the results by the NAME column, no the ID. Not sure if that makes a difference to any answer you may have.

Rik_Comery

Don't worry about this. I have found a solution, by selecting just the record i require with the use of LIMIT in the query.

Thanks for your help.