MySql Current Record Number

[charlie12345]

Is there a MySQL function that will return the record number of the current record after a "Select" query has been performed (after retrieving the record contained in mysql_fetch_array) ? I know count(*) will return the number of records in the table, but I want to know the record number of THIS record.

 

[gerrygerry]

You mean the number of rows returned by the query? use MYSQL_NUM_ROWS() -gerrygerry
Go To

http://quickfreewebsite.com!

 

[charlie12345]

No, I want to know the record number of the record currently being 'worked on' in the mysql_fetch_array....

 

[gerrygerry]

do you have an id field (primary, auto_incremnt) in your table? thats the traditional way of doing it... just add a select id from table_name and you should be good to go. -gerrygerry
Go To

http://quickfreewebsite.com!

 

[charlie12345]

I can do that, but then the id won't necessarily be the record number (i.e. in the order returned from the 'mysql_query') if the sql does any kind of sort.

 

[gerrygerry]

ok, then you could just put a counter in your loop...

$counter=1;
while($row=MYSQL_FETCH_ARRAY($results))
 {
 echo "Row #".$counter." has ".$row[0]." in it!";
 $counter++;
 }

-gerrygerry
Go To

http://quickfreewebsite.com!

 

[charlie12345]

I may have to go with the id auto-increment --

I am fetching a single record, and want to display a 'prev' / 'next' button that would key to the record before and after the current record, assuming there is one.
Oh well, thought there might be a mySQL function to give me the record number of the current record.

 

[gerrygerry]

well, the best way is with the auto_increment'ed id feild, you can always display the counter's row number and use the actual row id number in you links though. -gerrygerry
Go To

http://quickfreewebsite.com!

 

[kubla]

as gerrygerry was suggesting, the id is the way to go.

You can then "trap" for the id and assign the id number to a variable.

Here's a code-snippet that I use in one of my proggies:

for ($i = 0; $i < mysql_num_rows($result); $i++){

$row_array=mysql_fetch_row($result);
for ($j=0; $j < mysql_num_fields($result); $j++) {
if ((mysql_field_name($result, $j) == &quot;date&quot xor (mysql_field_name($result, $j) == &quot;entry&quot) {
echo (&quot;<tr><td><p><pre wrap>&quot; . $row_array[$j] . &quot;</pre></p></td></tr>&quot;
}
else {
$last = $row_array[$j];
}
}

$last, in this scenario, will be id number of the record.

Your &quot;next&quot; select statements can then be:

select * from tablename where condition limit $last, 1

and for &quot;previous&quot; it would be ($last - 1)

http://www.robertgraves.org

 

[Guest_imported]

i have this same problem to solve.
i've already been using auto_increment, but may well be faced with deleted records and hence holes in the sequence, so the $current_id++ and $current_id-- will point to non-existant records.
the way i plan to do it;
SELECT * FROM companies WHERE company_id > $current_id ORDER BY ASC LIMIT 1
and
SELECT * FROM companies WHERE company_id < $current_id ORDER BY company_id DESC LIMIT 1

 

[KarveR]

If you create an id field but don't use auto_increment you are able to re-use numbers again. you just gotta fine the empty ones first and fill the gaps. ***************************************
Party on, dudes!