[RESOLVED] Help with pagination

cretam

Hi,
I have been trying to display 10 records per page and then display links to next pages etc but I can't seem to get it to work.

Here is my code:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Search Results - Environmental Publications Database</title>
<meta http-equiv="content-type"
content="text/html; charset=iso-8859-1" />
</head>
<body>
<h3>Search Results - Environmental Publications Database</h3>
<?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, pubdesc, pubdate, orgid, pages, pubno, cost';
$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%'";
}

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

</tr>

<?php
$publications = @($select . $from . $where);
if (!$publications) {
echo '</table>';
exit('<p>Error retrieving publications from database!<br />'.
'Error: ' . mysql_error() . '</p>');
}

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

while ($publication = mysql_fetch_array($publications)) {
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";

}
?>

</table>

</body>
</html>

Any help would be greatly appreciated.

ClarkF1

I found that this works a treat:

http://www.phpbuilder.com/board/showpost.php?p=10371374&postcount=3

Just adapt it to your needs....

cretam

Hi ClarF1,
Thanks so much - it is great.

I can get it to display all the titles with pagination but I can't use the advanced search. Can you help me with the code to get it to work - here is my new code

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Search Results - Environmental Publications Database</title>
<meta http-equiv="content-type"
    content="text/html; charset=iso-8859-1" />
</head>
<body>
<h3>Search Results - Environmental Publications Database</h3>
<?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';
$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); 

// 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>';

   $strSQL = mysql_query("$select . $from . $where LIMIT $start, $pagelimit");
   if (!$strSQL) {
  echo '</table>';
  exit('<p>Error retrieving publications from database!<br />'.
      'Error: ' . mysql_error() . '</p>');
}

 $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. */ 



?>



</body>
</html>

Thanks for the tip - I wondered how they do that. It is much easier to read hey.
I am still having trouble with this. I think it must have something to do with my variable $where.
I would be most appreciative of any help. I know it's probably something small I am doing wrong.

ClarkF1

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

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

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

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

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

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

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

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

vertmonkee

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

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

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

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

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

excellent thats done it thank you very much

cretam

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

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

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.