Where in the record set

[Nutter]

Is there a way to figure out what position a specific record would be in a query? I've got a paginated list of articles and I want to be able to figure out what page a specific article would be on when displayed.

I'm thinking I can do this in PHP, but I really don't want to have to load the entire data set into an array and figure it out that way if there's a SQL method.

[jtara]

If your result set is small, you can look up the record in the result, and (assuming it is stored in an array - I don't do PHP, so don't know how PHP stores SELECT results) once found you can note the index, then divide by the page size. If the result set is ordered by the article name or ID, you can reduce the search time by doing a binary search.

Otherwise create an ordered result set in a temporary table, using INSERT INTO TABLE... SELECT. Make sure to create, in essence, an auto-increment column. (With a SQL variable and a bit of trivial math in the SELECT.) Now, you can look-up the record in the temporary result table, and fetch the "position" column you added, and divide by the page size.

[Nutter]

Here's what I wound up doing, and it seems to work fairly well although I'm not sure how well it'll scale as the application gets more data. And it'd probably fit more in the PHP board, but here we are :)

I created a list of the records as an associative array with the id field as the array key and the position as the value ($i++ in the loop). Then I just access $array['id'] to get the position.