/    Sign up×
Community /Pin to ProfileBookmark

Multiple Results Sheets with Navigation

Copy linkTweet thisAlerts:
Apr 23.2008

Hi!,

I am in need of a showing 20 results per page after a database query which may output more than 20 results in average.

For example say, there were 95 results;

The there are five pages;

01-20
21-40
41-60
61-80
81-95

At the bootom end of each page, there should be displayed;

xx-xx out of 95 records |[URL=”http://#”]Next[/URL]|[URL=”http://#”]Prev[/URL]|[URL=”http://#”]First[/URL]|[URL=”http://#”]Last[/URL]|

;as you see at many sites, probably including your email inbox, and in this forum.

I was able count number of pages using ceil , and guess that I should use SQL [b]LIMIT[/b] to generate results for each page. But I couldn’t find a way to output results in a navigational manner as in above.

Could someone please let me know, how to overcome this requirements.

Best Regards

to post a comment
PHP

4 Comments(s) ↴

Copy linkTweet thisAlerts:
@UltimaterApr 23.2008 — The technique is called [url=http://www.google.com/search?hl=en&q=php+mysql+pagination&btnG=Search]pagination[/url].

Many would obtain [b]95[/b] via [url=http://us3.php.net/manual/en/function.mysql-num-rows.php]mysql_num_rows[/url].

However it makes more sense to obtain the 95 by using COUNT in the query:
[code=php]
$q=mysql_query("SELECT COUNT(*) FROM mytable");
$count=(int)mysql_result($q,0);
[/code]

The way the LIMIT clause works is:
[list]
  • [*] LIMIT 20 - SELECT the first 20 records

  • [*] LIMIT 39,20 - start from the 40th record and SELECT the next 20 records

  • [*] LIMIT 20 is just a shorthand for LIMIT 0,20

    • [/list]

    Thus each page will contain two queries:
    [list=1]
  • [*]"SELECT COUNT(*) FROM mytable"

  • [*]"SELECT * FROM mytable LIMIT $start,$end"

    • [/list]
    Since LIMIT uses two parameters, your PHP needs to feed it two parameters.

    start&perpage is the most practical approach IMHO since the LIMIT clause expects a start index and not a page number but some use pagenumber rather than start nonetheless.

    Of coarse the [b]first[/b] link will always point to start=0&perpage=20

    The [b]last[/b] link would use $count and $_GET['perpage']

    In this case $count would be 95 and $_    GET['perpage'] would be 20

    page1: start=0&perpage=20

    page2: start=20&perpage=20

    page3: start=40&perpage=20

    page4: start=60&perpage=20

    page5: start=80&perpage=20

    The last page would thus read:

    LIMIT 80,20

    How do you obtain the 80 dynamically?

    What you do is first figure out how many records the last page will contain than deduct it from the 95.

    95 % 20 gives a remainder of 15 thus the last page will contain only 15 records. However if the remainder is 0, then the last page has 20 records.

    Thus a logic for the last page would be:
    [code=php]
    $remainder=$count % 20;
    $records_on_last_page=$remainder?$remainder:20;
    $last_page_starts_on=$count-records_on_last_page;
    [/code]
    Then $last_page_starts_on will be set to 80 thus:

    start=80&perpage=20


    The rest is just simple math and coming to conclusions with the data at your finger tips.

    For each page you calculate the value of $last_page_starts_on then you move onto calculating

    $previous_page_starts_on which will be start-perpage or 0 if it goes negative

    $next_page_starts_on which will be start+perpage or $last_page_starts_on, whichever is smaller

    $start_page_starts_on will always be 0
    Copy linkTweet thisAlerts:
    @GUIRauthorApr 23.2008 — Hi!

    Thanks very much for your detailed reply. It will help me greatly on finding a solution.

    Best Regards
    Copy linkTweet thisAlerts:
    @UltimaterApr 23.2008 — Kind of a throw-together but its an [url=http://clanhunter.frih.net/ultimater/pagination.php]example[/url] nonetheless.

    I'm sure the logic can be improved upon in some way or another.

    [url=http://clanhunter.frih.net/ultimater/pagination.phps]Here's the messy source code[/url] with the exception of mysql_connect and mysql_select_db being censored.

    Again, the logic might be flawed in some way or another, I haven't thoroughly tested it.

    Happy coding
    Copy linkTweet thisAlerts:
    @GUIRauthorApr 23.2008 — Thanks again...

    You are a great fellow.

    Best Regards.
    ×

    Success!

    Help @GUIR spread the word by sharing this article on Twitter...

    Tweet This
    Sign in
    Forgot password?
    Sign in with TwitchSign in with GithubCreate Account
    about: ({
    version: 0.1.9 — BETA 5.3,
    whats_new: community page,
    up_next: more Davinci•003 tasks,
    coming_soon: events calendar,
    social: @webDeveloperHQ
    });

    legal: ({
    terms: of use,
    privacy: policy
    });
    changelog: (
    version: 0.1.9,
    notes: added community page

    version: 0.1.8,
    notes: added Davinci•003

    version: 0.1.7,
    notes: upvote answers to bounties

    version: 0.1.6,
    notes: article editor refresh
    )...
    recent_tips: (
    tipper: @Yussuf4331,
    tipped: article
    amount: 1000 SATS,

    tipper: @darkwebsites540,
    tipped: article
    amount: 10 SATS,

    tipper: @Samric24,
    tipped: article
    amount: 1000 SATS,
    )...