Pagenav. By clicking next get next lets say "20" records (php/mysql)

Question

Hey there.. i would like to ask the experts.. i've already created a query to mysql that will give 20 results from mysql table etc. "cat" heres the calling:

if(isset($_GET['cat']))
{
    $query = "SELECT game_title,game_desc,.... 
       FROM games WHERE cat_id='".validate_input($_GET['cat'])."' LIMIT 20";
}
...

by this i manage to get the results i wanted.. what i'm asking here is how can i create a "button" that will load the next "20" records from table "cat" (something like Buttons). Sorry for my bad English.. lets hope that you will get the meaning of this is.. Thenx in advance! oh i forgot to mention that i'm a newbie in web programming..:)

Answer 1

<?
 $cn=mysql_connect("localhost","root","root") or die(mysql_error());
 mysql_select_db("db56") or die(mysql_error());

 $sql="select count(*) from emp";
 $result=mysql_query($sql);
 $r=mysql_fetch_row($result);

 $record=$r[0];
 $pagesize=20;
 $totalpages=$record/$pagesize;

 $currpage=$_GET["pg"];

 if(!isset($currpage)) 
      $start=0;
 else {
    $currpage--;
     $start= $currpage * $pagesize;
      }

 $end=$start+$pagesize;
 $sql="select * from emp limit $start,$pagesize";

 $result=mysql_query($sql);
 if($result){
    print "<table border='1'>";
    print "<tr><th>No</th><th>Name</th><th>Date</th></tr>";
    while($r=mysql_fetch_row($result))
      {
         print "<tr><td>$r[0]</td><td>$r[1]</td><td>$r[2]</td></tr>";
          }
    print "</table>";
 }   
 for($i=1;$i<=$totalpages;$i++){
       print "<a href='listemp.php?pg=$i'>&nbsp;$i&nbsp;</a>";
  }
?>

Answer 2

If you keep track of what page you're on, in the URL or a hidden field or session variable, you can use that to work out the limit. For example, page 2 should show limit 20 from row 20 (20 times the page offset).

Answer 3

You can pass two SQL parameters to LIMIT (put a comma between them) If you do, the first tells SQL how many records to skip (ie the offset of the first record) and the 2nd parameter is the one you're already using (how many records to return).

So just put a variable in your "next" link that says what page to display. You can pass the offset in this link, but it's pretty common to pass the "page number" instead, and multiply by the number of records per page before sticking it in the sql.

<a href="show_rows.php?page=<?php echo int($_REQUEST['page']) + 1; ?>">next page</a>

With a bit more work, you can make a "previous page" link, and make the correct links non-clickable when you're at the first/last page.

Answer 4

Pass along a parameter that tells the script that you want another chunk of the results and not just the first batch.

So for the link it could be like this:

example.com/results.php?cat=1&page=2

Where page= will tell the script what page you want to return.

Then you want to turn that LIMIT number you have so that you can work out some simple maths

$results_cnt = 20; //--rows you want per page of results

Now in your script you'll check to see if the page variable has been set. If not, default the start row to return from the first. But as you want to return different pages/sets of results, a little math is needed in order to start at the proper row.

if(isset($_GET["page"]) //--see if the variable is even there
{
    $page_num = (int)$_GET["page"]; //--forcing it to always be an integer

    $start_row = $results_cnt * ($page_num - 1);

    /* --
     what happens:
       ($results_cnt currently at 20)

     on page one (page=1), start at row 0
      math: 20 * (1 - 1) = 0

     on page two (page=2), start at row 20
      math: 20 * (2 - 1) = 20

     on page three (page=3), start at row 40
      math: 20 * (3 - 1) = 40

     etc.
    */
}
else
    $start_row = 0;

Now, having set the correct starting row, adjust the SQL query to use the variables like so:

if(isset($_GET['cat']))
{
    $query = "SELECT game_title,game_desc,.... 
       FROM games 
       WHERE cat_id='".validate_input($_GET['cat'])."' 
       LIMIT $start_row, $results_cnt";
}

Answer 5

ok i got it fix i guess..

in index.php:

--start php

$games = array(); $count = 1; $total = 0;

if(isset($_GET['cat'])) { $query = "SELECT game_title,game_desc,.... FROM games WHERE cat_id='".validate_input($_GET['cat'])."' LIMIT 20";

$total = mysql_num_rows(mysql_query("SELECT 1 FROM games WHERE cat_id='".validate_input($_GET['cat'])."'")); }

$query_result = @mysql_query ($query) OR error(mysql_error(), LINE, FILE, 0, '', ''); while ($info = @mysql_fetch_array($query_result)) { /* -- here goes the infos.. */ $games[$info['game_title']]['game_title'] = $info['game_title']; etc..

if(isset($GET['cat'])) { / -- for template engine */ $page->SetLoop ('PAGES', pagenav($total,$_GET['page'],20,$config['site_url'].'?cat='.$_GET['cat'],1,$lang)); } --end php

and in funct.php

--start php

function pagenav($total,$page,$perpage,$url,$posts=0) { $page_arr = array(); $arr_count = 0;

if($posts) { $symb='&'; } else { $symb='?'; } $total_pages = ceil($total/$perpage); $llimit = 1; $rlimit = $total_pages; $window = 5; $html = ''; if ($page<1 || !$page) { $page=1; }

if(($page - floor($window/5)) <= 0) { $llimit = 1; if($window > $total_pages) { $rlimit = $total_pages; } else { $rlimit = $window; } } else { if(($page + floor($window/2)) > $total_pages) { if ($total_pages - $window < 0) { $llimit = 1; } else { $llimit = $total_pages - $window + 1; } $rlimit = $total_pages; } else { $llimit = $page - floor($window/2); $rlimit = $page + floor($window/2); } } if ($page>1) { $page_arr[$arr_count]['title'] = 'Prev'; $page_arr[$arr_count]['link'] = $url.$symb.'page='.($page-1); $page_arr[$arr_count]['current'] = 0;

$arr_count++; }

for ($x=$llimit;$x <= $rlimit;$x++) { if ($x <> $page) { $page_arr[$arr_count]['title'] = $x; $page_arr[$arr_count]['link'] = $url.$symb.'page='.($x); $page_arr[$arr_count]['current'] = 0; } else { $page_arr[$arr_count]['title'] = $x; $page_arr[$arr_count]['link'] = $url.$symb.'page='.($x); $page_arr[$arr_count]['current'] = 1; }

$arr_count++; }

if($page < $total_pages) { $page_arr[$arr_count]['title'] = 'Next'; $page_arr[$arr_count]['link'] = $url.$symb.'page='.($page+1); $page_arr[$arr_count]['current'] = 0;

$arr_count++; }

return $page_arr; } --end php

and call it by

{LOOP: PAGES}{PAGES.title} {/LOOP: PAGES}

in an .html file.. it works perfectly but when i press the number 2 or next to get the next 20 records it jumps back to the first 20.. on the browser it reads "http://siteurl/?cat=1&amp;page=2" i cant figure out why.. if you please give me some help it will be appreciated.. thenx!