Counting rows before a certain row

[Anon]

Hi,

I'm seeking some help on how to count the number of rows in a Mysql Database, just before a certain row.

i.e., the visitor uses a search field, and I want to notify him of the number of rowns that exists in the db before the row that is displayed in the result of the search query.

I hope this is clear enough...

Thank you for your help.

[Anon]

Sloppy slow way:
<?PHP
$link = mysql_connect($dbhost, $dbuser, $dbpass);

$rowsquery = mysql_query("select * from yourtable order by someorderyoupick ASC");

$rowsgoneby = 0;
$numtotalrows = mysql_num_rows($rowsquery);

for ($cnt = 0; $cnt < $numtotalrows; $cnt++) {
$rowdata = mysql_fetch_array($rowsquery);
if (!in_array($searchfeildfromform, $rowdata)) {
$rowsgoneby++;
}
else {
echo "there were ".$rowsgoneby." rows until I found a match";
}
}

?>

Just a quick 1 minute answer. Hopefully you don't try to use it as is because I doubt it will work 🙂 I didn't check it at all. I only wanted to show you the road.

Also, I'm sure now that there is code in the post the people who have done this before and have a better way will swoop in and make me look foolish 😉 but hey, atleast you'll get your answer one way or another!

[Anon]

Yeah, that's what I was thinking about too, but it seems a bit too slow for a big db.

In fact, it would be cool if mysql were giving each row an id, that would follow the previous row's id, whitout creating a "hole" if the row is deleted. You know, like the auto_increment stuff.

Anyway, thank you.

[Anon]

What's wrong with COUNT?

http://www.mysql.com/doc/C/o/Counting_rows.html