I've a classic pagination system using LIMIT startrecord, endrecord and I want to figure out in what page number an X record is located.
The only idea I've right now is to seek recursively all the records to find it out. But I'm looking for a much more "economic" method!
Any ideas ?
You could use a sub query to create a table with the results and their position, then query that for the specific entry you are looking at:
SET @rank=0;
SELECT rank, record
FROM (
SELECT
@rank:=@rank+1 AS rank,
record
FROM table
) as subquery
WHERE record = x;
The returned table would show the record an the rank it appeared in the original query. You can the divide the rank by the number of results per page... Or build it into the query. Hope this helps.
Cout the number of records that are prior to the one you are looking for. This requires you to assume an order for your query which is natural.
SELECT COUNT(id) AS c
FROM tbl
WHERE sort_field < ((SELECT sort_field FROM tbl WHERE id = 18))
OR (sort_field = ((SELECT sort_field FROM tbl WHERE id = 18)) AND id < 18);
Then just retrieve the c and calculate ceilling(c/page_size). This will give you the page number that your record will fall in. The only important thing to remember is that you need to sort the records in the same order as you would in your query with limit.
To describe what the query does, it counts the number records that stand before the record with id 18. The only tricky part is with records with the same value as for their sort_field in which MySQL will make use of primary key and in our case the id. And that's why we have the OR part in our condition. In my answer I'm assuming you are sorting your original query (with limit statement in it) ascending, but if you are sorting descending then you need to change all of < to >.
Use something like this with your query as part of the s subselect
SELECT s.row, s.RECORD, YOUR_OTHER_FIELDS...
FROM (SELECT @row := 0) cnt
JOIN (SELECT @row := @row + 1 row, RECORD, ...YOUR QUERY WITH ORDER BY ...) s
WHERE s.RECORD = <desired record number>
and divide row by the pagesize from your pagination. Concrete but nonsensical example:
SELECT p.row, p.id
FROM (SELECT @row := 0) cnt
JOIN (SELECT @row := @row + 1 row, id FROM products ORDER BY id desc) p
WHERE p.id = 485166
As intended, the value of row changes with the order you use in the subselect.
It folds the variable initialization into the query so this is only one statement. It also does not depend on a natural order or distribution of rows - as long as the order they ARE returned in stays the same for whatever ORDER you specify (or leave out).
if this is something that you will use often, i think it is a good idea to create an stored procedure or a function. We can use a cursor inside, to iterate through the results and get the position of the desired item. I think this will be faster, it wont have to iterate to all the records, and dont need a subquery (for all this i would say that it is more economic) and you can use order, join, and whatever you need.
DELIMITER $$
CREATE FUNCTION position ( looking_for INT )
RETURNS INT
READS SQL DATA
BEGIN
-- First we declare all the variables we will need
DECLARE id INT;
DECLARE pos INT;
SET pos=0;
-- flag which will be set to true, when cursor reaches end of table
DECLARE exit_loop BOOLEAN;
-- Declare the sql for the cursor
DECLARE pos_cursor CURSOR FOR
SELECT id
FROM your_table
--you can use where, join, group by, order and whatever you need
--end of query
-- Let mysql set exit_loop to true, if there are no more rows to iterate
DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;
-- open the cursor
OPEN example_cursor;
-- marks the beginning of the loop
example_loop: LOOP
-- read the id from next row into the variable id
FETCH pos_cursor INTO id;
-- increment the pos var
SET pos=pos+1;
-- check if we found the desired item,
-- if it has been set we close the cursor and exit
-- the loop
IF id=looking_for THEN
CLOSE example_cursor;
LEAVE example_loop;
END IF;
-- check if the exit_loop flag has been set by mysql,
-- if it has been set we close the cursor and exit
-- the loop
IF exit_loop THEN
CLOSE example_cursor;
LEAVE example_loop;
END IF;
END LOOP example_loop;
RETURN pos;
END $$
DELIMITER ;
You create the function just once, and for using it, you just need to use this sql:
CALL position(ID_OF_THE_ITEM_YOU_ARE_LOOKING_FOR);
and it returns the position of the item, in the position [0][0] of the returned rowset.
Of course instead of the id you can create a function that compares the name, or any other field, or even more than one.
If the query is always diferent, then you cannot use a function, but you can still use the cursor (the syntax will be the same). You can build the cursor in your PHP, let pos be a System variable (using @pos), and in any case just add the specific sql of the query (the part between DECLARE pos_cursor CURSOR FOR and --end of query)
You can't really create an "economic" way. You have to get the full list of records from the DB since there is no way to know the position of a record from MySQL.
Depending on your sorting, the frequency at which the data changes, you could assign the record its position in a column: add column position to the table you are querying. That might not be feasible in all cases.
