Forum Moderators: open

Message Too Old, No Replies

Where in the record set

         

Nutter

10:03 pm on Jan 27, 2008 (gmt 0)

10+ Year Member



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

5:24 pm on Jan 28, 2008 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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

2:02 pm on Jan 31, 2008 (gmt 0)

10+ Year Member



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.