get the page number a record is ont (Limit)

[dlite922]

hey guys,

I'm doing some brain storming and getting ideas to come up with a solution.

I have a list of ....data... that is displayed 10 per page with the LIMIT clause.

Simply put: My requirement is to jump to the page a particular record is on from a search criteria.

For example, if I'm going to get to record "John", I want to go to the page that John falls on if the list was sorted by primary/id, so that the user can still click previous page and next page as if they manually went to that page where John is.



My Solution, (in Pseudocode, these are not live queries)

//pretend there's only one john and this returns one record
** SELECT id FROM table WHERE name = "john"

$selectedID = id

//to get which record john falls on:
** SELECT COUNT(id) FROM table WHERE id < $selectedID ...sort criteria here..;

//Let's say that John is at number 53 when sorted by ID ASC, this means that he'll be on page 50-60 if 10 items per page.

50 = round(53) // round to 10ths.
$num = 50 - 10; // LIMIT starts at page 0, so minus one page (10)

then put this value in the limit

**SELECT my,fields,here FROM table LIMIT $num,10;

This is a common problem and I'm sure there are other solutions other than what I have above.


My Question:

Any easier way to do this? Other creative solutions or ideas (i'm not expecting much) or random babble you can contribute is appreciated.


Ciao!




Dan

[r035198x]

Random babble : It's the way I always do it. I was too lazy to look for a better approach.

[Atli]

That's probably what I would do to.

I was trying to put it all into a single query, but it became very ugly:
[code=mysql]SELECT *
FROM `table` AS `primary`
WHERE ( SELECT FLOOR(COUNT(*) / 10)
FROM `table` AS `left`
WHERE `left`.`id` < `primary`.`id`
ORDER BY `left`.`id` ASC
) = (
SELECT
( SELECT FLOOR(COUNT(*) / 10)
FROM `table` AS `inner`
WHERE `inner`.`id` < `right`.`id`
ORDER BY `inner`.`id` ASC
) AS `PageNumber`
FROM `table` AS `right`
WHERE `right`.`name` = 'Testing'
);[/code]
And I would imagine all the subqueries would make this very slow on larger tables.

So I tried a procedure:
[code=mysql]CREATE PROCEDURE `GetSearchPage` (IN keyword VARCHAR(255), IN perPage INT)
BEGIN
DECLARE pageNumber Int DEFAULT 0;
DECLARE pageNumberCurso r CURSOR FOR
SELECT
( SELECT FLOOR(COUNT(*) / perPage)
FROM `table` AS `inner`
WHERE `inner`.`id` < `outer`.`id`
ORDER BY `id` ASC
) AS `PageNumber`
FROM `table` AS `outer`
WHERE `name` = keyword;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET pageNumber = NULL;

OPEN pageNumberCurso r;
FETCH pageNumberCurso r INTO pageNumber;
CLOSE pageNumberCurso r;

IF pageNumber IS NOT NULL THEN
SET @query = CONCAT('SELECT *, ', pageNumber, ' AS `PageNumber` FROM `table` LIMIT ', (pageNumber * perPage), ', ', perPage);
PREPARE stmt FROM @query;
EXECUTE stmt;
END IF;
END[/code]
Which seems to work much better, and only uses two simple queries.

[dlite922]

Thanks Atl! More than I asked for!

Two confirmation would have done it for me :D

But i'm sure this SP will come in handy for another lost soul!


Dan

That's probably what I would do to.

I was trying to put it all into a single query, but it became very ugly:
[code=mysql]SELECT *
FROM `table` AS `primary`
WHERE ( SELECT FLOOR(COUNT(*) / 10)
FROM `table` AS `left`
WHERE `left`.`id` < `primary`.`id`
ORDER BY `left`.`id` ASC
) = (
SELECT
( SELECT FLOOR(COUNT(*) / 10)
FROM `table` AS `inner`
WHERE `inner`.`id` < `right`.`id`
ORDER BY `inner`.`id` ASC
) AS `PageNumber`
FROM `table` AS `right`
WHERE `right`.`name` = 'Testing'
);[/code]
And I would imagine all the subqueries would make this very slow on larger tables.

So I tried a procedure:
[code=mysql]CREATE PROCEDURE `GetSearchPage` (IN keyword VARCHAR(255), IN perPage INT)
BEGIN
DECLARE pageNumber Int DEFAULT 0;
DECLARE pageNumberCurso r CURSOR FOR
SELECT
( SELECT FLOOR(COUNT(*) / perPage)
FROM `table` AS `inner`
WHERE `inner`.`id` < `outer`.`id`
ORDER BY `id` ASC
) AS `PageNumber`
FROM `table` AS `outer`
WHERE `name` = keyword;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET pageNumber = NULL;

OPEN pageNumberCurso r;
FETCH pageNumberCurso r INTO pageNumber;
CLOSE pageNumberCurso r;

IF pageNumber IS NOT NULL THEN
SET @query = CONCAT('SELECT *, ', pageNumber, ' AS `PageNumber` FROM `table` LIMIT ', (pageNumber * perPage), ', ', perPage);
PREPARE stmt FROM @query;
EXECUTE stmt;
END IF;
END[/code]
Which seems to work much better, and only uses two simple queries.

[Atli]

Thanks Atl! More than I asked for!

Two confirmation would have done it for me :D

But i'm sure this SP will come in handy for another lost soul!


Dan

Yea, I know you don't need me as your personal code-monkey :]

But I was playing around with this and had all the code.
Thought it couldn't hurt to put it out there.