[Solved] Pagination problems

[sford999]

Hi, I`m using the following code to automatically create previous/current/next page links, but the problem is, its returning more pages than there actually is.

There should only be 2 pages, but its returning 99 pages for some reason and I can`t figure out why? :-[

[code]<?php
// Get the county id
$cid = $_GET['id'];

// Get the page number
// If no page is defined, then set the page to "1"
if(!isset($_GET['p']))
{
$p = 1;
}
else
{
$p = $_GET['p'];
}

// Get the functions file
include('../functions.php');

// Get the users IP address
$ip = $_SERVER['REMOTE_ADDR'];

// Check the ip address against those that are in the database
// If all is ok, then show the page.
// If IP address is found in the db, then the function will show an error page
check_ip($ip_addr = "$ip");

// Make the header
make_header($head_title = 'Venues - View County');

// Connect to the database
db_connect();

// Figure out the limit for the query based on the current page number.
$max_results = 3;
$from = (($p * $max_results) - $max_results);

$sql = "SELECT * FROM venues WHERE county = $cid ORDER BY id ASC LIMIT $from, $max_results";
$result = mysql_query($sql);

$authflag =0;
while ($row = mysql_fetch_array($result))
{
extract($row);
if($auth ==1)
{
$authflag =1;

echo "<table width=\"100%\" border=\"0\">
  <tr>
    <td colspan=\"2\" class=\"thddr\"><strong>".stripslashes($vname)."</strong></td>
  </tr>
  <tr>
    <td width=\"20%\" class=\"thddr2\">Contact details </td>
    <td width=\"80%\" class=\"thddr2\">".stripslashes($addr)."</td>
  </tr>
  <tr>
    <td width=\"20%\" class=\"thddr2\">Price</td>
    <td width=\"80%\" class=\"thddr2\">".stripslashes($cost)."</td>
  </tr>
  <tr>
    <td width=\"20%\" class=\"thddr2\">Description, stock levels etc... </td>
    <td width=\"80%\" class=\"thddr2\">".stripslashes($descr)."</td>
  </tr>
  <tr>
    <td width=\"20%\" class=\"thddr2\">Bans &amp; restrictions </td>
    <td width=\"80%\" class=\"thddr2\">".stripslashes($bans)."</td>
  </tr>
  <tr>
    <td width=\"20%\" class=\"thddr2\">Comments or tips</td>
    <td width=\"80%\" class=\"thddr2\">".stripslashes($comments)."</td>
  </tr>";

// Find out if there are any added comments
// If there are, then insert them into the page and continue
// If not, continue with the page
venue_comments($vid = "$id", $cty = "$cid");

echo "<tr>
    <td colspan=\"2\" class=\"thddr\"><a href=\"comment.php?cty=$cid&vid=$id\">Click here to add a comment on this venue</a></td>
  </tr>
  <tr>
    <td colspan=\"2\" class=\"thddr\">&nbsp;</td>
  </tr>
</table><br /><br />";
}

}

// Figure out the total number of results in DB
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM venues WHERE auth = 1"),0);

// Figure out the total number of pages.
// Round up using ceil()
$total_pages = ceil($total_results / $max_results);

// Build Previous Link
if($p > 1)
{
$prev = ($p - 1);
echo "<a href=\"county.php?id=$cid&p=$prev\"><span class='pagelink'>Previous Page</span></a> ";
}
// Build Page Number Hyperlinks
for($i = 1; $i <= $total_pages; $i++)
{
if(($p) == $i)
{
echo "<span class=\"pagecurrent\">$i</span> ";
}
else
{
echo "<a href=\"county.php?id=$cid&p=$i\"><span class=\"pagelink\">$i</span></a> ";
}
}

// Build Next Link
if($p < $total_pages)
{
$next = ($p + 1);
echo "<a href=\"county.php?id=$cid&p=$next\"><span class='pagelink'>Next Page</span></a>";
}


// Will only show if nothing is grabbed from the database
if($authflag==0)
{
echo "Sorry, no venues have been submitted for this area.<br /><br /><a href=\"add.php\">Click here to add a venue for this county</a>";
}

// Make the footer
make_footer();

?>[/code]

[jwwceo]

That looks like the tutorial from this site. I just used it yesterday.

I had to tweak the SQL statement a little bit. I changed mine to something like this

SELECT COUNT(unique_id_from_venue_table) FROM venues WHERE auth = 1

where unique_id_from_venue_table is your primary key from your table.  this worked for me.

[HuggieBear]

That's not correct...

The SQL statement that selects the total records needs to reflect your original select.  So for you, you need to change this:

[code=php:0]$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM venues WHERE auth = 1"),0);
[/code]

To this:

[code=php:0]$total_results = mysql_result(mysql_query("SELECT COUNT(*) as NUM FROM venues WHERE county = $cid"),0);
[/code]

Regards
Huggie

[sford999]

Excellent, thanks, I knew it must`ve been the sql query, but couldn`t figure it out