By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,441 Members | 1,766 Online
Bytes IT Community
+ Ask a Question
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)

dlite922
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
Share this Question
Share on Google+
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

Atli
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
  1. SELECT *
  2. FROM `table` AS `primary`
  3. WHERE ( SELECT FLOOR(COUNT(*) / 10)
  4.         FROM  `table` AS `left`
  5.         WHERE `left`.`id` < `primary`.`id`
  6.         ORDER BY `left`.`id` ASC
  7.     ) = (
  8.         SELECT
  9.             (   SELECT FLOOR(COUNT(*) / 10)
  10.                 FROM  `table` AS `inner`
  11.                 WHERE `inner`.`id` < `right`.`id`
  12.                 ORDER BY `inner`.`id` ASC
  13.             ) AS `PageNumber`
  14.         FROM  `table` AS `right`
  15.         WHERE `right`.`name` = 'Testing'
  16.     );
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
  1. CREATE PROCEDURE `GetSearchPage`(IN keyword VARCHAR(255), IN perPage INT)
  2. BEGIN
  3.     DECLARE pageNumber Int DEFAULT 0;
  4.     DECLARE pageNumberCursor CURSOR FOR
  5.         SELECT
  6.             (   SELECT FLOOR(COUNT(*) / perPage)
  7.                 FROM  `table` AS `inner`
  8.                 WHERE `inner`.`id` < `outer`.`id`
  9.                 ORDER BY `id` ASC
  10.             ) AS `PageNumber`
  11.         FROM  `table` AS `outer`
  12.         WHERE `name` = keyword;
  13.     DECLARE CONTINUE HANDLER FOR NOT FOUND SET pageNumber = NULL;
  14.  
  15.     OPEN pageNumberCursor;
  16.     FETCH pageNumberCursor INTO pageNumber;
  17.     CLOSE pageNumberCursor;
  18.  
  19.     IF pageNumber IS NOT NULL THEN
  20.         SET @query = CONCAT('SELECT *, ', pageNumber, ' AS `PageNumber` FROM `table` LIMIT ', (pageNumber * perPage), ', ', perPage);
  21.         PREPARE stmt FROM @query;
  22.         EXECUTE stmt;
  23.     END IF;
  24. END
Which seems to work much better, and only uses two simple queries.
Feb 26 '09 #3

dlite922
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

Atli
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

Post your reply

Sign in to post your reply or Sign up for a free account.