Adding 'Pages' Page1,2,3 etc

[Oblivion]

Hello,

I know the title may have been abit missleading, but please can someone try and help me, I couldn't think of a better way to describe it in the title.

Many websites have their content split into pages... Take a forum for example, if you view a topic there is only a few (I'll say 10) posts on each page, then 'Page Numbers' are generated and put at the bottom, click on the next page, and you'll get other content. I'm wanting something like this, but I've not got a clue how to do it :(

I've got a MySQL database setup with my content in it;
DB Name= 'fabcontent'
DB Table= 'games'
The rows I'm using= 'title' and 'description', each entry also has an auto-incrementing value under 'id'.

I want the 5 newest items in the DB (ordered by the 'id' column) to show up on 'Page One', showing the title and description of each entry. Then I want page numebrs to automatically (where nedeed) appear at the bottom of the page, so if I click on 1 I'd get items 1-5, click 2 and I'd get items 6-10... I hope you get the idea.

Here's and example encase you don't understand what I mean;

Page 1;

--------------------------------------------------
-- Title: 1st entry
-- Description: a fun game
--------------------------------------------------

--------------------------------------------------
-- Title: 2nd entry
-- Description: my 2nd fun game
--------------------------------------------------

--------------------------------------------------
-- Title: 3rd entry
-- Description: another fun game
--------------------------------------------------

--------------------------------------------------
-- Title: 4th entry
-- Description: yet another fun game
--------------------------------------------------

--------------------------------------------------
-- Title: 5th entry
-- Description: you can guess what this is!
--------------------------------------------------

--------------------------------------------------
-- Pages: 1,2,3 etc
--------------------------------------------------

Page 2;

--------------------------------------------------
-- Title: 6th entry
-- Description: a fun game
--------------------------------------------------

--------------------------------------------------
-- Title: 7th entry
-- Description: my 2nd fun game
--------------------------------------------------

--------------------------------------------------
-- Title: 8th entry
-- Description: another fun game
--------------------------------------------------

--------------------------------------------------
-- Title: 9th entry
-- Description: yet another fun game
--------------------------------------------------

--------------------------------------------------
-- Title: 10th entry
-- Description: you can guess what this is!
--------------------------------------------------

--------------------------------------------------
-- Pages: 1,2,3 etc
--------------------------------------------------

I've previously got help from here before, and was amazed by the excellent response and help in solving my problem (Probably not much of a problem for someone who knows what they're doing). I hope someone can understand what I mean by this post, and I appreciate every bit of help I get.

Thanks in advance to anyone that gives this a go!

[arran]

Hi Oblivion,

Post what you've got so far (even if it's only pseudocode) and we'll give you some pointers.

arran.

[Oblivion]

Hi,

I don't really know what else you want to know... My current system is just limiting it to 10 on the first page, then you click 'more' and it's a page with everything on, I'd prefer it as a system where it's a series of pages.

If you've got any idea of any bits of ode that might help you, please tell me what would be needed. As you can probably tell I'm not the best coder in the worls =/.

It's just working out how to show the 5 newest entries ont he first page, then the next five ont he next page, then the next 5 etc. And making the 'Page Numbers' appear somewhere =/.

Thanks,
Mark

[arran]

Hi Mark,

You can set this up by passing one parameter (the page number) using GET or POST. Then work out the lower bound of the rows you require (this will be used in the LIMIT clause).

Here's a simple example to get you started:

$results_per_page = 5;
$current_page = $_GET['page_number'];
$lower_bound = ($current_page - 1) * $results_per_page;
$sql = "SELECT blah FROM blahblah WHERE blahblah.blah = whatever LIMIT $lower_bound, results_per_page";
$result = mysql_query($sql) or die('Error');

arran.

[BarryStCyr]

Not to be nitpicky but the statement
$sql = "SELECT blah FROM blahblah WHERE blahblah.blah = whatever LIMIT $lower_bound, results_per_page";

should be
$sql = "SELECT blah FROM blahblah WHERE blahblah.blah = whatever LIMIT $lower_bound, $results_per_page";

[BetterSEO]

Search WebmasterWorld for the term "pagination" and you will get several pages about this topic.

[Oblivion]

Thanks to everyone so-far, I took BetterSEO's advice and did a search for pagination, now I've got this code;

<?php
include_once("config.php");
include_once("mysqlcon.php");
?>
<?
/* Set current, prev and next page */
$page = (!isset($_GET['page']))? 1 : $_GET['page']; 
$prev = ($page - 1);
$next = ($page + 1);
/* Max results per page */
$max_results = 5;
/* Calculate the offset */
$from = (($page * $max_results) - $max_results);
/* Query the db for total results. You need to edit the sql to fit your needs */
$result = mysql_query("SELECT * FROM games");
$total_results = mysql_num_rows($result);
$total_pages = ceil($total_results / $max_results);
$pagination = '';
/* Create a PREV link if there is one */
if($page > 1)
{
$pagination .= '<a href="fronttest.php?page='.$prev.'">Previous</a> ';
}
/* Loop through the total pages */
for($i = 1; $i <= $total_pages; $i++)
{
if(($page) == $i)
{
$pagination .= $i;
}
else
{
$pagination .= '<a href="fronttest.php?page='.$i.'">$i</a>';
}
}
/* Print NEXT link if there is one */
if($page < $total_pages)
{
$pagination .= '<a href="fronttest.php?page='.$next.'">Next</a>";
}
/* Now we have our pagination links in a variable($pagination) ready to print to the page. I put it in a variable because you may want to show them at the top and bottom of the page */
/* Below is how you query the db for ONLY the results for the current page */
$result=mysql_query("SELECT * FROM games
LIMIT $from, $max_results ");
while ($i = mysql_fetch_array($result))
{
/* This is where you print your current results to the page */
}
?> 

But it's showing up the following error;

Parse error: parse error, unexpected $ in /home2/infected/public_html/fronttest.php on line 62

Please can someone tell me how to fix it, as I can't find any problems, btu that's just me. After that I'll try and work out how to get my data shown on the page :P.

[jatar_k]

this line is missing a single quote

$pagination .= '<a href="fronttest.php?page='.$next.'">Next</a>";

should be

$pagination .= '<a href="fronttest.php?page='.$next.'">Next</a>"';

[Oblivion]

Thanks jatar_k!

I've only got one problem left now, when I echo $pagination I'm getting

1$i$i$i$i$i$i$i$i$i$i$i$i$i$i$i$i$iNext"

Previous $i2$i$i$i$i$i$i$i$i$i$i$i$i$i$i$i$iNext"

Previous $i$i3$i$i$i$i$i$i$i$i$i$i$i$i$i$i$iNext"

etc.

The only number that's showwing correctly is the current page number, all other numbers don't work, the link works but the number on the page that you click doesn't work, anyone know the solution?

<?php
include_once("config.php");
include_once("mysqlcon.php");
?>
<?
/* Set current, prev and next page */
$page = (!isset($_GET['page']))? 1 : $_GET['page'];
$prev = ($page - 1);
$next = ($page + 1);
/* Max results per page */
$max_results = 5;
/* Calculate the offset */
$from = (($page * $max_results) - $max_results);
/* Query the db for total results. You need to edit the sql to fit your needs */
$result = mysql_query("SELECT * FROM games");
$total_results = mysql_num_rows($result);
$total_pages = ceil($total_results / $max_results);
$pagination = '';
/* Create a PREV link if there is one */
if($page > 1)
{
$pagination .= '<a href="fronttest.php?page='.$prev.'">Previous</a> ';
}
/* Loop through the total pages */
for($i = 1; $i <= $total_pages; $i++)
{
if(($page) == $i)
{
$pagination .= $i;
}
else
{
$pagination .= '<a href="fronttest.php?page='.$i.'">$i</a>';
}
}
/* Print NEXT link if there is one */
if($page < $total_pages)
{
$pagination .= '<a href="fronttest.php?page='.$next.'">Next</a>"';
}
/* Now we have our pagination links in a variable($pagination) ready to print to the page. I put it in a variable because you may want to show them at the top and bottom of the page */
/* Below is how you query the db for ONLY the results for the current page */
$result=mysql_query("SELECT * FROM games LIMIT $from, $max_results ");
while ($i = mysql_fetch_array($result))
{
/* This is where you print your current results to the page */
?>
             <?php
//Call data
//Get data
while($obj = mysql_fetch_object($result)){
$id = $obj->id;
$title = $obj->title;
$description = $obj->description;
$category = $obj->category;
?>
             <a href="<? $title = str_replace(" ","-",$title);
echo "game/$id/$title";?>.html">
             <?
$title = str_replace("-"," ",$title);
$description = str_replace("-"," ",$description);
 echo "<div align=center><strong>$title</strong><br />$description</div><hr>";?>
             </a> 
<? }?>
<? }
echo $pagination;
?> 

That's what I've now got, thanks everyone for your help!

[Oblivion]

It won't let me edit the post, but I've managed to sort it out :D

Thanks for all the help , this is the 2nd time I've been pleased with the help I've recieved here! :D

[dmmh]

im almost always pleased with the help I get here :)

[jatar_k]

>> almost

hard to please all the people all of the time ;)

[dmmh]

so true ;)

[Oblivion]

I've asked for help here twice now, I got a perfect solution both times, and in a short space of time :).

[Oblivion]

Back again, didn't realise before, but the 1st result on every page is dissapearing.

If I set $max_results to 5, it will give me 4 results, and the 1st result will dissapear.

On page one I'd get results, 2,3,4,5
On page two I'd get results, 7,8,9,10 etc..

Can anyone spot the error for me please? I've been playing aornud with the code for over half an hour, and I can't work out what's wrong O_o

[jatar_k]

that is usually a counter error

starting a counter at 1 instead of 0 or vice versa

[Oblivion]

That's what I was thinking jatar_k. After playing around with various things in eh script, and checkign my DB over and over... I still can't work out what's wrong :(

<?
/* Set current, prev and next page */
$page = (!isset($_GET['page']))? 1 : $_GET['page'];
$prev = ($page - 1);
$next = ($page + 1);
/* Max results per page */
$max_results = 6;
/* Calculate the offset */
$from = (($page * $max_results) - $max_results);
/* Query the db for total results. You need to edit the sql to fit your needs */
$result = mysql_query("SELECT * FROM games");
$total_results = mysql_num_rows($result);
$total_pages = ceil($total_results / $max_results);
$pagination = '';
/* Create a PREV link if there is one */
if($page > 1)
{
$pagination .= '<a href="index.php?page='.$prev.'">Previous </a> ';
}
/* Loop through the total pages */
for($i = 1; $i <= $total_pages; $i++)
{
if(($page) == $i)
{
$pagination .= "$i ";
}
else
{
$pagination .= '<a href="index.php?page='.$i.'">'.$i.' </a>';
}
}
/* Print NEXT link if there is one */
if($page < $total_pages)
{
$pagination .= '<a href="index.php?page='.$next.'">Next</a>"';
}
/* Below is how you query the db for ONLY the results for the current page */
$result=mysql_query("SELECT * FROM games ORDER by id DESC LIMIT $from, $max_results");
while ($i = mysql_fetch_array($result))
{
/* This is where you print your current results to the page */
?>
My Content ...
<? }
echo $pagination;
?> 

[jatar_k]

well, there is the possibility of 2 issues

1. you aren't displaying it
2. you aren't selecting

one of these 2 lines will show your error

$result=mysql_query("SELECT * FROM games ORDER by id DESC LIMIT $from, $max_results");
while ($i = mysql_fetch_array($result))

echo your $from and your $max_results and then just dump your rows in your while to see what's there

[Oblivion]

<?
/* Below is how you query the db for ONLY the results for the current page */
$result=mysql_query("SELECT * FROM games ORDER by id DESC LIMIT $from, $max_results");

while ($i = mysql_fetch_array($result))
{
/* This is where you print your current results to the page */
?>

<?php
//HERE

//HERE
while($obj = mysql_fetch_object($result)){
$id = $obj->id;
$title = $obj->title;
$description = $obj->description;
$category = $obj->category;
?>

<tr>
<td width="500px" valign="top"><table align="center" cellspacing="0" width="500px">
<tbody>

<tr>
<td class="btl"></td>
<td class="bt"><div align=center><strong><? echo $title?></strong></div></td>
<td class="btr"></td>
</tr>
<tr>
<td class="bl"></td>
<td class="content">
<br />

<a href="<? $title = str_replace(" ","-",$title);
echo "game/$id/$title";?>.html">
<?
$title = str_replace("-"," ",$title);
$description = str_replace("-"," ",$description);
echo "<div align=center><strong>$title</strong><br />$description</div><hr>";?>
</a>

<br />
</td>
<td class="br"></td>
</tr>
<tr>
<td class="bbl"></td>
<td class="bb"></td>
<td class="bbr"></td>
</tr>

</tbody>
</table>
</td>
</tr>

<tr>
<td>&nbsp;</td>
</tr>

<? } }?>

That's what I'm using when I send my data to the page, I've taken out my normal SQL query (If I wasn't using the page script, I used to have my other query in there)

$query = "SELECT * FROM games ORDER by id DESC LIMIT 3";
$result = mysql_query($query) or die ("damn! couldnt connect!");

^^That's what I used to have as my origional SQL query^^

I removed that code, thinking that the $result made from the pagination script would replace it, was I right?

[jatar_k]

I would think that's right

question

why these 2 loops

while ($i = mysql_fetch_array($result))
{
/* This is where you print your current results to the page */
?>

<?php
//HERE

//HERE
while($obj = mysql_fetch_object($result)){

loop in a loop that do the same thing is redundant

[Oblivion]

jatar_k I love you! =D (no... not in that way)

After getting rid of the 1st one of those, it works perfectly! :D

Thanks!

[jatar_k]

the first loop was stripping off the first result

the second one was actually doing the work but it was always starting at the second result because the internal pointer was already advanced ;)