[RESOLVED] Help with pagination

ClarkF1 · Jun 13, 2006

Please add php tags so the code is formatted properly in your post.

Go to edit post, highlight the code and click on the php button (second from the right) on the formatting options above the message box

cretam · Jun 15, 2006

Hi Clark,
I am not sure if you have given up on me but I have been away and couldn't get back to the computer. I have highlighted my code as you suggested. Real cool, thanks for the tip. I hope you can help me.
Cheers

ClarkF1 · Jun 15, 2006

I think the problem is here as you may be querying the fullstops too:

$strSQL = mysql_query("$select . $from . $where LIMIT $start, $pagelimit");

Try


$strquery = $select . $from . $where . " LIMIT ".$start." ,".$pagelimit;

echo $strquery; // check the correct query is being written

$strSQL = mysql_query($strquery) or die('Database error: '.mysql_error());

Comment out the if (!strSQL) section........if there is an error the above line will print out the problem and we can work from that.

Let me know about any error messages you get.

cretam · Jun 16, 2006

You're a gem!! You don't know how happy you make me feel. I wish I had remembered about this forum 6 months ago. That's how long it's taken me with this.

Thankyou Thankyou Thankyou :

Could I ask one more question - when there are no results found - instead of saying:
0 matches found
Results 1-0 found

Is it difficult to say Sorry no results found when there are no matches instead of Results 1-0 found.

Thanks again. You're a legend.

ClarkF1 · Jun 16, 2006

After:

$totalrows = mysql_num_rows($strSQL);

put in an if clause surrounding the rest of the code

if ($totalrows > 0) {

then after everything which is dependant on 1 or more results put

}
else 
echo "No results found";

Please don't forget to mark the thread resolved if your questions have been answered (In the Thread Tools menu at the top)

Glad I could help

Good luck with the rest.......

cretam · Jun 18, 2006

Hi Clark,
Thanks again it worked brilliantly. When I tried to add a Search again link after 'No results found' I kept getting an error.

ie.
}
else
echo "No results found. <a href="index.php">Search again</a>";

Can you tell me why? The error is Parse error: parse error, expecting ','' or';''

Thanks for the tip to say Thread resolved - I didn't even know about that.

Thanks again for all your help.

Cheers

ClarkF1 · Jun 18, 2006

You need to escape the double quotes so they get printed to the source code.

echo "No results found. <a href="index.php">Search again</a>";

should be

echo "No results found. <a href=\"index.php\">Search again</a>";

cretam · Jun 19, 2006

Thanks - we're done. You're a champion!!

vertmonkee · Jun 19, 2006

Hi, I know you have this thread as resolved but i have been working on some pagination as well and so far havent managed to get it working the way I need.

I used the link that clark gave earlier and have tried it out.

Unforunatley when I use it I click through the pages but all the results display as the same.

here is my ever so slightly modified code form the linked to post

<? 

/*  Replace the following credentials with yours */ 

//This is filled in with my details

$server = ""; 
$username = ""; 
$password = ""; 
$database = ""; 
$table = "your_table"; 


// Connect To MySQL Server 
@mysql_connect($server, $username, $password) or die("Couldn't Connect to Database"); 

// Select Database 
@mysql_select_db($database) or die("Couldn't Select Database"); 

// set number of results to display per page (in this case, 10 per page) 
$pagelimit = "10"; 

// run query 
   $strSQL = mysql_query("SELECT * FROM country") or die(mysql_error()); 

// count number of matches 
   $totalrows = mysql_num_rows($strSQL); 

// determine how many pages there will be by using ceil() and dividing total rows by pagelimit 
   $pagenums = ceil ($totalrows/$pagelimit); 

// if no value for page, page = 1 
    if ($page==''){ 
        $page='1'; 
    } 
// create a start value 
    $start = ($page-1) * $pagelimit; 

// blank matches found 
echo "<b>" . $totalrows . " matches found</b><br>\n"; 

// Showing Results 1 to 10 (or if you're page limit were 15) 1 to 15, etc. 
$starting_no = $start + 1; 

if ($totalrows - $start < $pagelimit) { 
   $end_count = $totalrows; 
} elseif ($totalrows - $start >= $pagelimit) { 
   $end_count = $start + $pagelimit; 
} 


echo "Results $starting_no to $end_count shown.<br>\n"; 

// create dynamic next, previous, and page links 

/* lets say you're set to show 10 results per page and your script comes out with 12 results. 
this will allow your script to say next 2 if you're on the first page and previous 10 if you're on the second page. */ 

if ($totalrows - $end_count > $pagelimit) { 
   $var2 = $pagelimit; 
} elseif ($totalrows - $end_count <= $pagelimit) { 
   $var2 = $totalrows - $end_count; 
} 

$space = "&nbsp;"; 

// previous link (if you're on any page besides the first, create previous link) 
if ($page>1) { 
        echo "« <a href='" . $PHP_SELF . "?page=".($page-1)."' class=main>Previous" . $space . $pagelimit . "</a>" . $space . $space . ""; 
    } 

// dynamic page number links 

for ($i=1; $i<=$pagenums; $i++) { 
    if ($i!=$page) { 
        echo " <a href='" . $PHP_SELF . "?page=$i' class=main>$i</a>"; 
    } 
    else { 
        echo " <b>[".$i."]</b>"; 
    } 
} 


// next link (if the page you are on is less than the total amount of page numbers, there are more pages left) 

if ($page<$pagenums) { 
    echo "" . $space . $space . $space . $space . " <a href='" . $PHP_SELF . "?page=".($page+1)."' class=main>Next " . $var2 . "</a> »"; 
} 

/* output your data wherever you'd like. 

BUT 

in order for this all to work, before outputting your data, you have to run the query over using MySQL's LIMIT. 
This will limit how many results are actually displayed on the page. */ 

   $strSQL = mysql_query("SELECT * FROM country LIMIT $start,$pagelimit") or die(mysql_error()); 

// LIMIT 0,10 will start at 0 and display 10 results 
// LIMIT 10,5 will start at 10 and display 5 results 

/* now you can do whatever you'd like with this query. it will only output ten results per page. 
change the $pagelimit variable to whatever to output more than 10 result per page. */ 

while($row = mysql_fetch_array($strSQL)) {
	extract($row);
	echo "<br>$country_name";
}
?>

You can see the results at this link

http://ttphp.open.ac.uk/~idl27/skatespots/phpbuilderpaginate.php

thanks

cretam · Jun 19, 2006

Hi,
Here my entire code if that helps

<?php

$dbcnx = @mysql_connect('localhost', 'root', '');
if (!$dbcnx) {
  exit('<p>Unable to connect to the ' .
      'database server at this time.</p>');
}

if (!@mysql_select_db('ijdb')) {
  exit('<p>Unable to locate the publication ' .
      'database at this time.</p>');
}
// The basic SELECT statement
$select = 'SELECT DISTINCT id, pubname, pubdate';
$from   = ' FROM pub';
$where  = ' WHERE 1=1';

$aid = $_POST['aid'];
if ($aid != '') { // An author is selected
  $where .= " AND authorid='$aid'";
}

$cid = $_POST['cid'];
if ($cid != '') { // A category is selected
  $from  .= ', pubcategory';
  $where .= " AND id=pubid AND categoryid='$cid'";
}

$oid = $_POST['oid'];
if ($oid != '') { // An organisation is selected
   $where .= " AND orgid='$oid' ";
}

$searchtext = $_POST['searchtext'];
if ($searchtext != '') { // Some search text was specified
  $where .= " AND pubname LIKE '%$searchtext%' OR pubdesc LIKE '%$searchtext%'";
}

// set number of results to display per page (in this case, 10 per page) 
$pagelimit = "10"; 
// run query 
   $strSQL = mysql_query($select . $from . $where) or die(mysql_error()); 


// count number of matches 
   $totalrows = mysql_num_rows($strSQL); 
if ($totalrows > 0) { 



// determine how many pages there will be by using ceil() and dividing total rows by pagelimit 
   $pagenums = ceil ($totalrows/$pagelimit); 

// if no value for page, page = 1 
    if ($page==''){ 
        $page='1'; 
    } 
// create a start value 
    $start = ($page-1) * $pagelimit; 

// blank matches found 
echo "<b>" . $totalrows . " matches found</b><br>\n"; 

// Showing Results 1 to 10 (or if you're page limit were 15) 1 to 15, etc. 
$starting_no = $start + 1; 

if ($totalrows - $start < $pagelimit) { 
   $end_count = $totalrows; 
} elseif ($totalrows - $start >= $pagelimit) { 
   $end_count = $start + $pagelimit; 
} 


echo "Results $starting_no to $end_count shown.<br>\n"; 

// create dynamic next, previous, and page links 

/* lets say you're set to show 10 results per page and your script comes out with 12 results. 
this will allow your script to say next 2 if you're on the first page and previous 10 if you're on the second page. */ 

if ($totalrows - $end_count > $pagelimit) { 
   $var2 = $pagelimit; 
} elseif ($totalrows - $end_count <= $pagelimit) { 
   $var2 = $totalrows - $end_count; 
} 

$space = "&nbsp;"; 

// previous link (if you're on any page besides the first, create previous link) 
if ($page>1) { 
        echo "« <a href='" . $PHP_SELF . "?page=".($page-1)."' class=main>Previous" . $space . $pagelimit . "</a>" . $space . $space . ""; 
    } 

// dynamic page number links 

for ($i=1; $i<=$pagenums; $i++) { 
    if ($i!=$page) { 
        echo " <a href='" . $PHP_SELF . "?page=$i' class=main>$i</a>"; 
    } 
    else { 
        echo " <b>[".$i."]</b>"; 
    } 
} 


// next link (if the page you are on is less than the total amount of page numbers, there are more pages left) 

if ($page<$pagenums) { 
    echo "" . $space . $space . $space . $space . " <a href='" . $PHP_SELF . "?page=".($page+1)."' class=main>Next " . $var2 . "</a> »"; 
} 



echo '<table width="600">';
echo '<tr>';
echo '<th width="90%">Publication Name</th>';
echo '<th width="10%"><div align="left">Date</div></th>';
echo '</tr>';

$strquery = $select . $from . $where . " LIMIT ".$start." ,".$pagelimit;

//echo $strquery; // check the correct query is being written

$strSQL = mysql_query("$strquery") or die('Database error: '.mysql_error());



 $pubname = ereg_replace('\\[(B|EB|I|EI|L|L=|L=[-_./a-z0-9!&%#?+,\'=:;@~]+|EL|E)?(]|$)', '', $pubname);

while ($publication = mysql_fetch_array($strSQL)) {
  echo "<tr valign='top'>\n";
  $id = $publication['id'];
  $pubname = htmlspecialchars($publication['pubname']);
  $date = $publication['pubdate'];




  echo "<td><a href=\"pub.php?id=$id\">$pubname</a></td>\n";
  echo "<td>$date</td>\n";





}


// LIMIT 0,10 will start at 0 and display 10 results 
// LIMIT 10,5 will start at 10 and display 5 results 

/* now you can do whatever you'd like with this query. it will only output ten results per page. 
change the $pagelimit variable to whatever to output more than 10 result per page. */ 

} 
else 
echo "No results found. <a href=\"index.php\">Search again</a>";


?>

cretam · Jun 19, 2006

I have done the same with my authors page, thought the code might be simpler - take a look

<?php

$dbcnx = @mysql_connect('localhost', 'root', '');
if (!$dbcnx) {
  exit('<p>Unable to connect to the ' .
      'database server at this time.</p>');
}

if (!@mysql_select_db('ijdb')) {
  exit('<p>Unable to locate the publication ' .
      'database at this time.</p>');
}
// set number of results to display per page (in this case, 15 per page) 
$pagelimit = "15"; 

// run query 
   $strSQL = mysql_query("SELECT id, name FROM author order by name asc") or die(mysql_error()); 

// count number of matches 
   $totalrows = mysql_num_rows($strSQL); 

// determine how many pages there will be by using ceil() and dividing total rows by pagelimit 
   $pagenums = ceil ($totalrows/$pagelimit); 

// if no value for page, page = 1 
    if ($page==''){ 
        $page='1'; 
    } 
// create a start value 
    $start = ($page-1) * $pagelimit; 

// blank matches found 

echo "<b>" . $totalrows . " matches found</b><br>\n"; 

// Showing Results 1 to 10 (or if you're page limit were 15) 1 to 15, etc. 
$starting_no = $start + 1; 

if ($totalrows - $start < $pagelimit) { 
   $end_count = $totalrows; 
} elseif ($totalrows - $start >= $pagelimit) { 
   $end_count = $start + $pagelimit; 
} 


echo "Results $starting_no to $end_count shown.<br>\n"; 

// create dynamic next, previous, and page links 

/* lets say you're set to show 10 results per page and your script comes out with 12 results. 
this will allow your script to say next 2 if you're on the first page and previous 10 if you're on the second page. */ 

if ($totalrows - $end_count > $pagelimit) { 
   $var2 = $pagelimit; 
} elseif ($totalrows - $end_count <= $pagelimit) { 
   $var2 = $totalrows - $end_count; 
} 

$space = "&nbsp;"; 

// previous link (if you're on any page besides the first, create previous link) 
if ($page>1) { 
        echo "« <a href='" . $PHP_SELF . "?page=".($page-1)."' class=main>Previous" . $space . $pagelimit . "</a>" . $space . $space . ""; 
    } 

// dynamic page number links 

for ($i=1; $i<=$pagenums; $i++) { 
    if ($i!=$page) { 
        echo " <a href='" . $PHP_SELF . "?page=$i' class=main>$i</a>"; 
    } 
    else { 
        echo " <b>[".$i."]</b>"; 
    } 
} 


// next link (if the page you are on is less than the total amount of page numbers, there are more pages left) 

if ($page<$pagenums) { 
    echo "" . $space . $space . $space . $space . " <a href='" . $PHP_SELF . "?page=".($page+1)."' class=main>Next " . $var2 . "</a> »"; 
} 

/* output your data wherever you'd like. 

BUT 

in order for this all to work, before outputting your data, you have to run the query over using MySQL's LIMIT. 
This will limit how many results are actually displayed on the page. */ 

   $strSQL = mysql_query("SELECT id, name FROM author order by name asc LIMIT $start,$pagelimit") or die(mysql_error()); 


while ($author = mysql_fetch_array($strSQL)) {
  $id = $author['id'];
  $name = htmlspecialchars($author['name']);
  echo "<li>$name ".
      "<a href='editauthor.php?id=$id'>Edit</a> ".
      "<a href='deleteauthor.php?id=$id'>Delete</a></li>";
}

?>

vertmonkee · Jun 20, 2006

Hi thanks for the replies.

I still cant get it to work. When I change page the $start does not increase.

<?php 

$dbcnx = @mysql_connect('', '', ''); 
if (!$dbcnx) { 
  exit('<p>Unable to connect to the ' . 
      'database server at this time.</p>'); 
} 

if (!@mysql_select_db('idl27')) { 
  exit('<p>Unable to locate the publication ' . 
      'database at this time.</p>'); 
} 
// set number of results to display per page (in this case, 15 per page) 
$pagelimit = "15"; 

// run query 
   $strSQL = mysql_query("SELECT * FROM country") or die(mysql_error()); 

// count number of matches 
   $totalrows = mysql_num_rows($strSQL); 

// determine how many pages there will be by using ceil() and dividing total rows by pagelimit 
   $pagenums = ceil ($totalrows/$pagelimit); 

// if no value for page, page = 1 
    if ($page==''){ 
        $page='1'; 
    } 
// create a start value 
    $start = ($page-1) * $pagelimit; 

// blank matches found 

echo "<b>" . $totalrows . " matches found</b><br>\n"; 

// Showing Results 1 to 10 (or if you're page limit were 15) 1 to 15, etc. 
$starting_no = $start + 1; 

if ($totalrows - $start < $pagelimit) { 
   $end_count = $totalrows; 
} elseif ($totalrows - $start >= $pagelimit) { 
   $end_count = $start + $pagelimit; 
} 


echo "Results $starting_no to $end_count shown.<br>\n"; 

// create dynamic next, previous, and page links 

/* lets say you're set to show 10 results per page and your script comes out with 12 results. 
this will allow your script to say next 2 if you're on the first page and previous 10 if you're on the second page. */ 

if ($totalrows - $end_count > $pagelimit) { 
   $var2 = $pagelimit; 
} elseif ($totalrows - $end_count <= $pagelimit) { 
   $var2 = $totalrows - $end_count; 
} 

$space = "&nbsp;"; 

// previous link (if you're on any page besides the first, create previous link) 
if ($page>1) { 
        echo "« <a href='" . $PHP_SELF . "?page=".($page-1)."' class=main>Previous" . $space . $pagelimit . "</a>" . $space . $space . ""; 
    } 

// dynamic page number links 

for ($i=1; $i<=$pagenums; $i++) { 
    if ($i!=$page) { 
        echo " <a href='" . $PHP_SELF . "?page=$i' class=main>$i</a>"; 
    } 
    else { 
        echo " <b>[".$i."]</b>"; 
    } 
} 


// next link (if the page you are on is less than the total amount of page numbers, there are more pages left) 

if ($page<$pagenums) { 
    echo "" . $space . $space . $space . $space . " <a href='" . $PHP_SELF . "?page=".($page+1)."' class=main>Next " . $var2 . "</a> »"; 
} 

/* output your data wherever you'd like. 

BUT 

in order for this all to work, before outputting your data, you have to run the query over using MySQL's LIMIT. 
This will limit how many results are actually displayed on the page. */ 

   $strSQL = mysql_query("SELECT * FROM country LIMIT $start,$pagelimit") or die(mysql_error()); 

echo "<br>Start is : $start<br>";
echo "<br>Page Limit is : $pagelimit<br>";
while ($row = mysql_fetch_array($strSQL)) { 
  extract($row);
  echo "$country_name<br>"; 
} 

?>

I have changed the url of the page it is now

http://ttphp.open.ac.uk/~idl27/skatespots/paginate5.php

thanks

ClarkF1 · Jun 20, 2006

at the top of the script put

$page = $_GET['page'];

I can't see anywhere where it was reading the page variable from the url in your script.

vertmonkee · Jun 20, 2006

excellent thats done it thank you very much

cretam · Jun 21, 2006

It all seems to be working fine when I click on your page numbers.
Guess you worked it out. Don't forget to remove the Start is and Page Limit is.

cretam · Jun 21, 2006

Clark,
I know I have marked this as resolved, but I want to display the pagination at the bottom of the first 10 results. I have been cutting and pasting code but am having trouble getting it to work. Can you tell me how I make the pagination display after the first 10 results please.

ClarkF1 · Jun 21, 2006

Try this:

<?php

$dbcnx = @mysql_connect('localhost', 'root', '');
if (!$dbcnx) {
  exit('<p>Unable to connect to the ' .
      'database server at this time.</p>');
}

if (!@mysql_select_db('ijdb')) {
  exit('<p>Unable to locate the publication ' .
      'database at this time.</p>');
}
// The basic SELECT statement
$select = 'SELECT DISTINCT id, pubname, pubdate';
$from   = ' FROM pub';
$where  = ' WHERE 1=1';

$aid = $_POST['aid'];
if ($aid != '') { // An author is selected
  $where .= " AND authorid='$aid'";
}

$cid = $_POST['cid'];
if ($cid != '') { // A category is selected
  $from  .= ', pubcategory';
  $where .= " AND id=pubid AND categoryid='$cid'";
}

$oid = $_POST['oid'];
if ($oid != '') { // An organisation is selected
   $where .= " AND orgid='$oid' ";
}

$searchtext = $_POST['searchtext'];
if ($searchtext != '') { // Some search text was specified
  $where .= " AND pubname LIKE '%$searchtext%' OR pubdesc LIKE '%$searchtext%'";
}

// set number of results to display per page (in this case, 10 per page)
$pagelimit = "10";
// run query
   $strSQL = mysql_query($select . $from . $where) or die(mysql_error());


// count number of matches
   $totalrows = mysql_num_rows($strSQL);
if ($totalrows > 0) {



// determine how many pages there will be by using ceil() and dividing total rows by pagelimit
   $pagenums = ceil ($totalrows/$pagelimit);

// if no value for page, page = 1
    if ($page==''){
        $page='1';
    }
// create a start value
    $start = ($page-1) * $pagelimit;

// blank matches found
echo "<b>" . $totalrows . " matches found</b><br>\n";

// Showing Results 1 to 10 (or if you're page limit were 15) 1 to 15, etc.
$starting_no = $start + 1;

if ($totalrows - $start < $pagelimit) {
   $end_count = $totalrows;
} elseif ($totalrows - $start >= $pagelimit) {
   $end_count = $start + $pagelimit;
}


echo "Results $starting_no to $end_count shown.<br>\n";

// create dynamic next, previous, and page links

/* lets say you're set to show 10 results per page and your script comes out with 12 results.
this will allow your script to say next 2 if you're on the first page and previous 10 if you're on the second page. */

if ($totalrows - $end_count > $pagelimit) {
   $var2 = $pagelimit;
} elseif ($totalrows - $end_count <= $pagelimit) {
   $var2 = $totalrows - $end_count;
}

$space = "&nbsp;";





echo '<table width="600">';
echo '<tr>';
echo '<th width="90%">Publication Name</th>';
echo '<th width="10%"><div align="left">Date</div></th>';
echo '</tr>';

$strquery = $select . $from . $where . " LIMIT ".$start." ,".$pagelimit;

//echo $strquery; // check the correct query is being written

$strSQL = mysql_query("$strquery") or die('Database error: '.mysql_error());



$pubname = ereg_replace('\\[(B|EB|I|EI|L|L=|L=[-_./a-z0-9!&%#?+,\'=:;@~]+|EL|E)?(]|$)', '', $pubname);

while ($publication = mysql_fetch_array($strSQL)) {
  echo "<tr valign='top'>\n";
  $id = $publication['id'];
  $pubname = htmlspecialchars($publication['pubname']);
  $date = $publication['pubdate'];




  echo "<td><a href=\"pub.php?id=$id\">$pubname</a></td>\n";
  echo "<td>$date</td></tr>\n";





}

echo "</table";

// previous link (if you're on any page besides the first, create previous link)
if ($page>1) {
        echo "« <a href='" . $PHP_SELF . "?page=".($page-1)."' class=main>Previous" . $space . $pagelimit . "</a>" . $space . $space . "";
    }

// dynamic page number links

for ($i=1; $i<=$pagenums; $i++) {
    if ($i!=$page) {
        echo " <a href='" . $PHP_SELF . "?page=$i' class=main>$i</a>";
    }
    else {
        echo " <b>[".$i."]</b>";
    }
}


// next link (if the page you are on is less than the total amount of page numbers, there are more pages left)

if ($page<$pagenums) {
    echo "" . $space . $space . $space . $space . " <a href='" . $PHP_SELF . "?page=".($page+1)."' class=main>Next " . $var2 . "</a> »";
}

// LIMIT 0,10 will start at 0 and display 10 results
// LIMIT 10,5 will start at 10 and display 5 results

/* now you can do whatever you'd like with this query. it will only output ten results per page.
change the $pagelimit variable to whatever to output more than 10 result per page. */

}
else
echo "No results found. <a href=\"index.php\">Search again</a>";


?>

You forgot to close your table tag which might have been the problem.

cretam · Jun 21, 2006

Thanks Clark, it worked fine for the first page but when I went to another page (ie page 2) the links jumped back up to the top.
Any ideas?

ClarkF1 · Jun 21, 2006

Have you got a link I can look at........

cretam · Jun 21, 2006

I wish I had it live but unfortunately it is only on my laptop using apache.

[RESOLVED] Help with pagination

CClarkF1

Ccretam

CClarkF1

Ccretam

CClarkF1

Ccretam

CClarkF1

Ccretam

Vvertmonkee

Ccretam

Ccretam

Vvertmonkee

CClarkF1

Vvertmonkee

Ccretam

Ccretam

CClarkF1

Ccretam

CClarkF1

Ccretam