Pagination, Please Help

[Donno24]

Hey Everyone, Ive got this result page shown below there is alot of records and I need pagination. Problem is I dont know how to paginate. Does anyone have any suggestions on how to paginate this code below.

Hope you can help,
Many Thanks
Paul

<?php
include "assets/connect.php";
?>
<div class="countynumrows">
<?php
// Retrieve data from database
$pubcounty= $_GET['pubcounty'];
$pubtown= $_GET['pubtown'];

$sql="SELECT * FROM pubdirectory WHERE pubcounty LIKE '$pubcounty' OR pubtown LIKE '$pubtown'" or die (mysql_error());
$result=mysql_query($sql);

$numRows = mysql_num_rows($result);

echo"<p>We found <strong><FONT COLOR=\"Red\">$numRows</FONT></strong> Pubs/Clubs searching for <strong>\"<FONT COLOR=\"Red\">$pubcounty$pubtown</FONT>\"</strong>. </p>";
?>
</div>

<?php
while($rows=mysql_fetch_assoc($result)){
?>
<div class="countyresults">



<h2><?php echo $rows[pubname]; ?></h2>
<table width="100%" border="0" cellspacing="2" cellpadding="0">
<tr>
<td width="80"><strong>Address:</strong></td>
<td width="382"><?php echo $rows[pubaddress]; ?>, <?php echo $rows[pubtown]; ?>, <?php echo $rows[pubcounty]; ?> </td>
</tr>
<tr>
<td><strong>Web:</strong></td>
<td><a href="<?php echo $rows[pubweb]; ?>"><?php echo $rows[pubweb]; ?></a></td>
</tr>
<tr>
<td><strong>Info:</strong></td>
<td><?php echo $rows[pubdetails]; ?></td>
</tr>
</table>
</div>
<?php
}
?>

[Andrew Johnson]

You'll need to use the LIMIT keyword

SELECT * FROM table LIMIT 1,2

Number 1 is the number of records to skip over while number 2 is how many you want to show. For example if there are 10 records for page your second number will always be 10 and the first will be 0 for the first page, 10 for the second page, 20 for the third page,...

I'd suggest using a GET variable, lets call it "p"

So for page 2 the URL will be page.php?p=2 or p=3 for page 3, etc.

Use code like:


// if $_GET["p"] isn't set then show page 1
if (isset($_GET["p"]))
$page = $_GET["p"];
else
$page = 1;

//set query, NOTE: we subtract one from $page as we need to start at record 0
$query = mysql_query("SELECT * FROM table LIMIT " . ($page-1)*10 . ",10");

[Donno24]

I have edited my code to be like this

if (isset($_GET["p"]))
$page = $_GET["p"];
else
$page = 1;

$query = "SELECT * FROM reviews WHERE pubdir_id = '$pubdir_id' LIMIT ' . ($page-1)*10 . ',10 ORDER BY date DESC"
$result = mysql_query($query, $connection);

I'm getting an error saying "Parse error: syntax error, unexpected T_VARIABLE"

[Andrew Johnson]

Your quotes aren't proper, also I'm not sure if ORDER BY can come after LIMIT or if it has to come before, either way just do what I do...

Change
$query = "SELECT * FROM reviews WHERE pubdir_id = '$pubdir_id' LIMIT ' . ($page-1)*10 . ',10 ORDER BY date DESC"

To
$query = "SELECT * FROM reviews WHERE pubdir_id = '$pubdir_id' ORDER BY date DESC LIMIT " . ($page-1)*10 . ",10"

[Donno24]

I'm sorry but im still getting this error "Parse error: syntax error, unexpected T_VARIABLE".
I have change the code as you advised.

The error is saying its on this line " $result = mysql_query($query, $connection); "

[Andrew Johnson]

Post your updated snippet and let me know which line this error is occuring on.

[Donno24]

Error

Parse error: syntax error, unexpected T_VARIABLE in C:\wamp\www\MajorProject\Website\directory\show.php on line 179

Line 179

$result = mysql_query($query, $connection);

[Andrew Johnson]

It's the line prior, the one with the actual query being defined... post that one

[Donno24]

Oh Sorry, here is the original code you advised also.

if (isset($_GET["pubdir_id"]))
$page = $_GET["pubdir_id"];
else
$page = 1;
$query = "SELECT * FROM reviews WHERE pubdir_id = '$pubdir_id' ORDER BY date DESC LIMIT " . ($page-1)*10 . ",10"
$result = mysql_query($query, $connection);

[Andrew Johnson]

You need a semi-colon at the end of your second last line - my bad I missed it when I copied and pasted...

[Donno24]

Ok, we have no errors now.
But there is no records being displayed now lol there is approx 9 in this db at the minute.
Also could you explain how I would navigate through these pages when They are displayed?

Your being a great help, thank you.

[abduraooft]

$result = mysql_query($query, $connection) or die(mysql_error());, to ensure that there is no error in your query!

[Andrew Johnson]

Make sure you're no on page 2 or something, to ensure the query is working add ?p=1 to your URL to ensure we're for sure viewing page one.

[Donno24]

When the link is "show.php?pubdir_id=9" there is 3 records coming up as I changed the query to "($page-1)*0 . ",3"

BUT when I add the p=1, no records are appearing
"show.php?pubdir_id=9?p=1"

[Andrew Johnson]

While I'm waiting he's a snippet to add a page menu:


$total_pages = ceil(mysql_num_rows(mysql_query("SELECT * FROM table"))/10);

for ($i=1;$i<=$total_pages;$i++)
{
echo "<a href=\"page.php?p=" . $i . "\">Page " . $i . "</a> ";
}


Gosh I'm in a good mood for a Monday morning

[Andrew Johnson]

You have to seperate the variables in a URL by an ampersand (&)

Change
"show.php?pubdir_id=9?p=1"

To
"show.php?pubdir_id=9&p=1"

[Donno24]

Would it not be working cause my GET is pubdir_id?
Already on the page Im using a GET to get the records from a DB with the Foreign Key id Pubdir_id?

[Andrew Johnson]

There is no limit to GET variables, they are stored in an array, just seperate them with an ampersand like I showed

[Donno24]

I'm sorry about all this Mr Johnson, The pagination navigation is working, but when I click on page 2 or 3 onwards the content stays the same. I have added the code once more so you can glance over and see if there is any thing missing that I wouldnt pick up.

Hope you can help.

<?php

if (isset($_GET["p"]))
$page = $_GET["p"];
else
$page = 1;
$query = "SELECT * FROM reviews WHERE pubdir_id = '$pubdir_id' ORDER BY date DESC LIMIT " . ($page-1)*0 . ",10" ;
$result = mysql_query($query, $connection) or die(mysql_error());

for ($i = 0; $i < mysql_num_rows($result); $i++)
{
$pubdir_id = mysql_result($result, $i, "pubdir_id");
$name = mysql_result($result, $i, "name");
$email = mysql_result($result, $i, "email");
$email_len = strlen($email);
$comment = mysql_result($result, $i, "comment");
$comment = nl2br($comment);
$date = mysql_result($result, $i, "date");
$show_date = date("H:i l dS F Y", $date);


if($i % 2)
{
$bg_color="#0A70C5";
}
else
{
$bg_color="#0004b8";
}

echo '
<tr>

<tr>
<td colspan="2" width="100%" bgcolor="'.$bg_color.'">
<p>'.$comment.'</p>
</td>
</tr>
<tr>
<td colspan="2" width="100%" bgcolor="'.$bg_color.'">
<p class="submitby">Posted By:
';
if($email_len > 0)
{
echo $name;
}
else
{
echo''.$name;
}
echo ', '.$show_date.'


</p>
</td>
</tr>
';
}
?>
</table>

<?php
$total_pages = ceil(mysql_num_rows(mysql_query("SELECT * FROM reviews"))/5);

for ($i=1;$i<=$total_pages;$i++)
{
echo "<a href=\"show.php?pubdir_id=9&p=" . $i . "\">Page " . $i . " | </a> ";
} ?>

[Andrew Johnson]

I don't understand why you changed

($page-1)*0

To
($page-1)*0

Didn't you learn in grade one that zero multiplied by anything results in zero?

Change it back to 10.

Also you've changed the Page menu draw code from 10 to 5. You have also modify the query code to reflect this as well.

[Donno24]

Aha, I see. I got it working now.

Thanks Alot for your Help & I'm sorry for the headache on a monday morn (evening here).

Thanks Again,
Paul

[Mikroz]

Neverming, didn't realize the second page before posting, maybe shouldn't be working today :)

[Andrew Johnson]

No problem, always willing to help a fellow Irish coder.