Jump to content


Photo

[Solved] Pagination problems


  • This topic is locked This topic is locked
3 replies to this topic

#1 sford999

sford999
  • Members
  • PipPipPip
  • Advanced Member
  • 119 posts

Posted 14 September 2006 - 10:29 AM

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? :-[

<?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();

?>


#2 jwwceo

jwwceo
  • Members
  • PipPipPip
  • Advanced Member
  • 212 posts

Posted 14 September 2006 - 10:56 AM

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.

#3 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 14 September 2006 - 12:13 PM

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:

$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM venues WHERE auth = 1"),0);

To this:

$total_results = mysql_result(mysql_query("SELECT COUNT(*) as NUM FROM venues WHERE county = $cid"),0);

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#4 sford999

sford999
  • Members
  • PipPipPip
  • Advanced Member
  • 119 posts

Posted 14 September 2006 - 02:56 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users