440,441 Members | 1,766 Online
Need help? Post your question and get tips & solutions from a community of 440,441 IT Pros & Developers. It's quick & easy.

# get the page number a record is ont (Limit)

 Expert 100+ P: 1,584 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 Feb 23 '09 #1
4 Replies

 10K+ P: 13,264 Random babble : It's the way I always do it. I was too lazy to look for a better approach. Feb 25 '09 #2

 Expert 5K+ P: 5,058 That's probably what I would do to. I was trying to put it all into a single query, but it became very ugly: Expand|Select|Wrap|Line Numbers 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'     ); And I would imagine all the subqueries would make this very slow on larger tables. So I tried a procedure: Expand|Select|Wrap|Line Numbers CREATE PROCEDURE `GetSearchPage`(IN keyword VARCHAR(255), IN perPage INT) BEGIN     DECLARE pageNumber Int DEFAULT 0;     DECLARE pageNumberCursor 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 pageNumberCursor;     FETCH pageNumberCursor INTO pageNumber;     CLOSE pageNumberCursor;       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 Which seems to work much better, and only uses two simple queries. Feb 26 '09 #3

 Expert 100+ P: 1,584 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 @Atli Feb 27 '09 #4

 Expert 5K+ P: 5,058 @dlite922 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. Feb 28 '09 #5