473,326 Members | 2,438 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

get the page number a record is ont (Limit)

dlite922
1,584 Expert 1GB
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 3972
r035198x
13,262 8TB
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
5,058 Expert 4TB
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
1,584 Expert 1GB
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
5,058 Expert 4TB
@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

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

Similar topics

2
by: Wm | last post by:
I'm trying to get a handle on the best way to handle setting up my listings so that I display groups of about 25 records per page. I currently have a page that returns over 1,000 names/addresses,...
8
by: joe | last post by:
Can anyone help me out by either telling me how to get to the result i need or by pointing me to some documentation about the following question: A certain query to a database give me eg 100...
1
by: Michael Brennan-White | last post by:
If I submit my for using a get action the resulting page loads . If I use a post action I get an error page saying "The page cannot be found". I am calling the originating page!!! This happens...
2
by: Miguel Orrego | last post by:
Hi, I have a page that pulls data from a database, one of the fields is Notetext which frequently contains an email. I then want to pass this onto another page, that updates the email field in...
6
by: PG | last post by:
When deleting a row from the database, that id is now missing. So what I'm trying to do is update in a loop (maybe an sql loop if there is one) of all of the id numbers - sort of like renaming...
9
by: campbellwarren | last post by:
Does anyone know how I could limit the number of rows allowed in a MS Access table... want to limit it to 1.
2
by: Mike | last post by:
Is it possible to add a page system like: "< 1-2-3-4-5 >" or "<PREV - NEXT> To a page thats already written out, or will it be too much work? For example: 5 articles per page...
15
by: Hexman | last post by:
Hello All, How do I limit the number of detail records selected in a Master-Detail set using SQL? I want to select all master records for a date, but only the first 3 records for the details...
11
by: PW | last post by:
One of my ASP's was working fine for a long time. Now it has started constantly refreshing itself. Everytime I run it I just get the first part of the page, then it refreshes itself, the rest of...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.