return row number of a specific (but unknown) row in MySQL?

JLerster

I am making a search for a forum that uses pagination. Post are displayed last in first out. So if the last post was number 1,000 - it would be the first out on page 1 with a url of "index.php?page=0". If it was the 11 to last post it would be on the second page (url "index.php?page=1"). This is simple enough when you are browsing the forum using the paging, but when it comes to a search engine it is different. I need to know how many rows into the database each post is. Post ids cant be used because they are allowed to be deleted which would mess up the ordering (delete post number 100, post number 101 would be now be in row 100) im not sure if this is a good description, so I made a bit of code that would do what I am looking for, but by wasting a good bit of resources

function return_row_number($parent_id)
{	$query = mysql_query("SELECT * FROM cmsteam_forum_posts");
	for($i=0; $row = mysql_fetch_array($query); $i++)
	{	if($row['parent_id'] == $parent_id)
		break;
	}

return $i
}

is there a more simple way to do this?

anandm78

I'm not sure what you are trying to do in the code.

What is parent_id? Is it the id of the thread that the post belongs to? If that's the case, you seem to be returning the first match, which may not be what you want.

In any case you can improve this quite a bit by changing the SELECT statement to return just the parent_id since that's what you seem to be interested in.

SELECT parent_id FROM cmsteam_forum_posts

It'll help if you can describe more as to how your database is organized and what operation you are trying to do.

Thanks ...