Pagination

Question

tulipputih 0 Junior Poster

16 Years Ago

hi everybody,
I am doing pagination.
if I set the limit to 5 and the no of result is 30, then 6 pages is shown as the overall page).
however, only the first 5 results is shown.
How should I change the code?
many thanks

<?php 
   /* call this script "advs.php" */
   //if(!$c) { 
   if(!$_GET['c']) { 
//"c" variable to $_GET['c'] and the "all" "any" and "none" variables to $_POST['all'] 
?>
  <?php
$all= $_POST['all'] ;
 $any= $_POST['any'] ;
 $none=$_POST['none'] ;
 
 
//this makes sure the page number isn't below one, or more than our maximum pages
$pageno = (int)$pageno;
if ($pageno > $lastpage) {
   $pageno = $lastpage;
} 
if ($pageno < 1) {
   $pageno = 1;
} 
 mysql_connect("localhost","root","rootroot");
 MySQL_select_db("coas5");
 
   if((!$all) || ($all == "")) { $all = ""; } else { $all = "+(".$all.")"; }
   if((!$_POST['all'] ) || ($_POST['all']  == "")) { $_POST['all']  = ""; } else { $_POST['all']  = "+(".$_POST['all'] .")"; }
   if((!$any) || ($any == "")) { $any = ""; } 
   if((!$none) || ($none == "")) { $none = ""; } else { $none = "-(".$none.")"; }
  
   $query = " SELECT count(*),
          MATCH(subject, learningArea, noofstudents, topic, ability) AGAINST ('$all $none $any' IN BOOLEAN MODE) 
          FROM lesson
       WHERE MATCH(subject, learningArea, noofstudents, topic, ability) AGAINST ('$all $none $any' IN BOOLEAN MODE) AND lesson.verified= 'yes' " ;
      
	  $artm1 = MySQL_query($query);
	  $query_data = mysql_fetch_row($artm1);
	  $numrows = $query_data[0];
     
$rows_per_page = 2;
$lastpage      = ceil($numrows/$rows_per_page);
	
$pageno = (int)$pageno;
if ($pageno > $lastpage) {
   $pageno = $lastpage;
} 
if ($pageno < 1) {
   $pageno = 1;
} 


//This sets the range to display in our query
$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;

$query = " SELECT *,
          MATCH(subject, learningArea, noofstudents, topic, ability) AGAINST ('$all $none $any' IN BOOLEAN MODE) 
          FROM lesson
       WHERE MATCH(subject, learningArea, noofstudents, topic, ability) AGAINST ('$all $none $any' IN BOOLEAN MODE) AND lesson.verified= 'yes' $limit" ;
       $artm1 = MySQL_query($query);

 if(MySQL_num_rows($artm1) > 0) {
           echo "<b>Lesson Plans Matches</b><br>";
		 echo "<table>";
		 echo "<table border='1'>";
          echo "<tr><td>Lesson ID </td><td>Subject </td><td>Learning area</td> <td>Topic</td> <td>Time period</td><td>Ability</td></tr>";
             
			 while($artm2 = MySQL_fetch_array($artm1)) {
           	  echo "<td><a href='customise1.php?lessonID=  {$artm2['lessonID']}; '>  {$artm2['lessonID']}</a></td>";
//echo "<td>{$artm2['lessonID']}</td>";
			echo "<td>{$artm2['subject']}</td>";
			echo "<td>{$artm2['learningArea']}</td>";
			echo "<td>{$artm2['topic']}</td>";
			echo "<td>{$artm2['minutes']}</td>";
			echo "<td>{$artm2['ability']}</td></tr>";
         }
      echo "</table>";
   }
  else { 
      echo "No Results were found in this category.<br>"; 
   } 

if ($pageno == 1) {
   echo " FIRST PREV ";
} else {
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1'>FIRST</a> ";
   $prevpage = $pageno-1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'>PREV</a> ";
} 

//Next we inform the user of his current position in the sequence of available pages.

echo " ( Page $pageno of $lastpage ) ";

//This code will provide the links for any following pages.

if ($pageno == $lastpage) {
   echo " NEXT LAST ";
} else {
   $nextpage = $pageno+1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'>NEXT</a> ";
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>LAST</a> ";
} // if
}
   echo "<br>"; 
   
  ?>

php

2 Contributors
4 Replies
122 Views
1 Week Discussion Span
Latest Post 16 Years Ago Latest Post by tulipputih

Dani AI

Generated 10 Months Ago

Short diagnosis (why page 2 is empty)
As noted, LIMIT is required — but your symptom (page 2 showing “No Results” and “( Page 1 of 0 )”) points to two separate problems: the script never reads the page number from the URL, and the search terms are coming from POST on the first request but are not preserved when you click the pagination links. In the second request your WHERE clause runs with empty filters so COUNT returns 0 and $lastpage becomes 0.

Minimal, focused fixes

Read the page number from GET and calculate an offset:

$pageno = isset($_GET['pageno']) ? max(1,(int)$_GET['pageno']) : 1;
$rows_per_page = 5;                   // set to the value you want
$offset = ($pageno - 1) * $rows_per_page;

Make sure the same search criteria are used for both the COUNT and the data query. If your form submits via POST, fall back to GET so pagination links can carry the same values:
```
$all_raw = isset($_POST['all']) ? $_POST['all'] : (isset($_GET['all']) ? $_GET['all'] : '');
$safe_all = mysql_real_escape_string(trim($all_raw));   // or use mysqli/PDO
```
Use LIMIT with the computed offset in the data query and include an ORDER BY so results are stable:

data query: ... WHERE <same filters> ORDER BY MATCH(...) DESC LIMIT $offset, $rows_per_page
count query: SELECT COUNT(*) FROM lesson WHERE <same filters>

Persisting filters across pages
Either change the search form to use GET, or append the search fields to every pagination URL (or store them in $_SESSION). Example approach: merge current GET params, set the new pageno, and rebuild the query string with http_build_query so Next/Prev preserve filters.

Additional tips
Sanitize inputs (mysql_real_escape_string or better, migrate to mysqli/PDO + prepared statements). Only show pagination when $numrows > 0, and keep $rows_per_page consistent (your code currently uses 2 while you expect 5). These changes will make page links return the same filtered result set on every page.

All 4 Replies

kevindougans 2 Junior Poster

16 Years Ago

You need to put a LIMIT in your mysql statement for it to do what you want it to do....

SELECT * FROM `your_table` LIMIT 0, 10; # << This will display the first 10 results from the database.

SELECT * FROM `your_table` LIMIT 5, 5; # << This will show records 6, 7, 8, 9, and 10

You should aim to do something like

$query = " SELECT count(*),
          MATCH(subject, learningArea, noofstudents, topic, ability) AGAINST ('$all $none $any' IN BOOLEAN MODE) 
          FROM lesson
       WHERE MATCH(subject, learningArea, noofstudents, topic, ability) AGAINST ('$all $none $any' IN BOOLEAN MODE) AND lesson.verified= 'yes'  LIMIT $offset, $limit" ;

## $offset should be something like $pageno * $limit
## $limit should obviously be how many you want to show per page

hi everybody,
I am doing pagination.
if I set the limit to 5 and the no of result is 30, then 6 pages is shown as the overall page).
however, only the first 5 results is shown.
How should I change the code?
many thanks

<?php 
   /* call this script "advs.php" */
   //if(!$c) { 
   if(!$_GET['c']) { 
//"c" variable to $_GET['c'] and the "all" "any" and "none" variables to $_POST['all'] 
?>
  <?php
$all= $_POST['all'] ;
 $any= $_POST['any'] ;
 $none=$_POST['none'] ;
 
 
//this makes sure the page number isn't below one, or more than our maximum pages
$pageno = (int)$pageno;
if ($pageno > $lastpage) {
   $pageno = $lastpage;
} 
if ($pageno < 1) {
   $pageno = 1;
} 
 mysql_connect("localhost","root","rootroot");
 MySQL_select_db("coas5");
 
   if((!$all) || ($all == "")) { $all = ""; } else { $all = "+(".$all.")"; }
   if((!$_POST['all'] ) || ($_POST['all']  == "")) { $_POST['all']  = ""; } else { $_POST['all']  = "+(".$_POST['all'] .")"; }
   if((!$any) || ($any == "")) { $any = ""; } 
   if((!$none) || ($none == "")) { $none = ""; } else { $none = "-(".$none.")"; }
  
   $query = " SELECT count(*),
          MATCH(subject, learningArea, noofstudents, topic, ability) AGAINST ('$all $none $any' IN BOOLEAN MODE) 
          FROM lesson
       WHERE MATCH(subject, learningArea, noofstudents, topic, ability) AGAINST ('$all $none $any' IN BOOLEAN MODE) AND lesson.verified= 'yes' " ;
      
	  $artm1 = MySQL_query($query);
	  $query_data = mysql_fetch_row($artm1);
	  $numrows = $query_data[0];
     
$rows_per_page = 2;
$lastpage      = ceil($numrows/$rows_per_page);
	
$pageno = (int)$pageno;
if ($pageno > $lastpage) {
   $pageno = $lastpage;
} 
if ($pageno < 1) {
   $pageno = 1;
} 


//This sets the range to display in our query
$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;

$query = " SELECT *,
          MATCH(subject, learningArea, noofstudents, topic, ability) AGAINST ('$all $none $any' IN BOOLEAN MODE) 
          FROM lesson
       WHERE MATCH(subject, learningArea, noofstudents, topic, ability) AGAINST ('$all $none $any' IN BOOLEAN MODE) AND lesson.verified= 'yes' $limit" ;
       $artm1 = MySQL_query($query);

 if(MySQL_num_rows($artm1) > 0) {
           echo "<b>Lesson Plans Matches</b><br>";
		 echo "<table>";
		 echo "<table border='1'>";
          echo "<tr><td>Lesson ID </td><td>Subject </td><td>Learning area</td> <td>Topic</td> <td>Time period</td><td>Ability</td></tr>";
             
			 while($artm2 = MySQL_fetch_array($artm1)) {
           	  echo "<td><a href='customise1.php?lessonID=  {$artm2['lessonID']}; '>  {$artm2['lessonID']}</a></td>";
//echo "<td>{$artm2['lessonID']}</td>";
			echo "<td>{$artm2['subject']}</td>";
			echo "<td>{$artm2['learningArea']}</td>";
			echo "<td>{$artm2['topic']}</td>";
			echo "<td>{$artm2['minutes']}</td>";
			echo "<td>{$artm2['ability']}</td></tr>";
         }
      echo "</table>";
   }
  else { 
      echo "No Results were found in this category.<br>"; 
   } 

if ($pageno == 1) {
   echo " FIRST PREV ";
} else {
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1'>FIRST</a> ";
   $prevpage = $pageno-1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'>PREV</a> ";
} 

//Next we inform the user of his current position in the sequence of available pages.

echo " ( Page $pageno of $lastpage ) ";

//This code will provide the links for any following pages.

if ($pageno == $lastpage) {
   echo " NEXT LAST ";
} else {
   $nextpage = $pageno+1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'>NEXT</a> ";
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>LAST</a> ";
} // if
}
   echo "<br>"; 
   
  ?>

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

tulipputih 0 Junior Poster · Answer 1 · 2010-03-02T16:37:59+00:00

You need to put a LIMIT in your mysql statement for it to do what you want it to do....
SELECT * FROM `your_table` LIMIT 0, 10; # << This will display the first 10 results from the database.
SELECT * FROM `your_table` LIMIT 5, 5; # << This will show records 6, 7, 8, 9, and 10
You should aim to do something like
$query = " SELECT count(*),
          MATCH(subject, learningArea, noofstudents, topic, ability) AGAINST ('$all $none $any' IN BOOLEAN MODE) 
          FROM lesson
       WHERE MATCH(subject, learningArea, noofstudents, topic, ability) AGAINST ('$all $none $any' IN BOOLEAN MODE) AND lesson.verified= 'yes'  LIMIT $offset, $limit" ;

## $offset should be something like $pageno * $limit
## $limit should obviously be how many you want to show per page

OK, BUT i WANT ALL THE RESULT TO BE DISPLAYED ACCORDING TO HOW MANY PAGES I SPECIFY ;
$rows_per_page = 2;
$lastpage = ceil($numrows/$rows_per_page);

kevindougans 2 Junior Poster · Answer 2 · 2010-03-02T17:00:16+00:00

i don't understand what your trying to do sorry.

if you want tio display 5 results per page and be able to click through the pages you don't need $rows_per_page = 2;
$lastpage = ceil($numrows/$rows_per_page);

all you need to do is what i said in the previous post, then put $pageno = 1 in the url and then the "next" link should be $pageno=2 then $offset can be calculated by $offset = $pageno * $limit;

i would edit your code for you but it's a bit all over the place and my boss wouldn't be too happy if i spent half my day trying to fix it.

if you can't get this working i would usggest starting simple, starting fresh and starting with a simple query and home amde pagination system then add to it to get what you want.

tulipputih 0 Junior Poster · Answer 3 · 2010-03-11T17:51:39+00:00

maybe this is better to explain my problem.
Any suggestion please?
thank you

this is current output of my code

let say I set the row to be displayed per page is 5, and there are 9 results.
the following output is displayed. the output is perfect in the first page

Lesson Plans Matches

Lesson ID Learning area T
52 Computer Systems
53 Computer Systems
61 ICT and Society
75 Computer Systems
91 Computer Systems
FIRST PREV ( Page 1 of 2 ) NEXT LAST

When I click next, supposed the 2nd page will be displayed..
and this should be there( Page 2 of 2 ).
however in the 2nd page nothingis displayed and the page is displayed like this

No Results were found in this category.
FIRST PREV ( Page 1 of 0 ) NEXT LAST

it seems that the link doesn't function even the correct page nmber is sent to the URL
http://localhost/search/booleanview.php?pageno=2

Pagination

Dani AI

Recommended Answers Collapse Answers

All 4 Replies

Recommended Answers