[RESOLVED] Php Pagination Question.

ashleycox

Hi all,
I know there are plenty of topics and tutorials out there that cover this, but all seam to recommend different things and i'm nto sure what to use. I have a page, that displays categories of articles. When a category is clicked on, the news articles are displayed. What I want to do is paginate the display of the news articles.

Here is the code for my main page:

<?php
require_once("var.php"); // To get the bullet and background color info.
require_once("mysql_conn.php"); // To get the mysql connection

$catid = strtolower($GET['catid']);
$articleid = $GET['article'];
if(!empty($catid)){
$catid = mysql_real_escape_string($catid);
$query = "SELECT title, id, DATE_FORMAT(postdate, '%Y-%m-%d') as date FROM news WHERE category='{$catid}' ORDER BY id DESC LIMIT $news_limit";
$r = mysql_query($query);
if($r && mysql_num_rows($r) > 0){
while($row = mysql_fetch_assoc($r)){
$display .= '<tr bgcolor="' . $title_cell_color . '">';
$display .= ' <td><img src="' . $bullet . '" /> <b><a href="news.php?article=' . $row['id'] . '">' . ucfirst($row['title']) . '</a></b> Posted on ' . $row['date'] . '</td>';
$display .= '</tr>';
}
}else if(mysql_num_rows($r) === 0){
$display = '<tr><td>No News Articles Found In The "' . ucwords($catid) . '" Category</td></tr>';
}else{
$display = "<tr><td>MySQL ERROR:</td></tr><tr><td>" . mysql_error() . "</td></tr>";
}
}else if(!empty($articleid)){
$articleid = mysql_real_escape_string($articleid);
$query = "SELECT *, DATE_FORMAT(postdate, '%Y-%m-%d') as date FROM news WHERE id='{$_articleid}'";
$r = mysql_query($query);
if($r && mysql_num_rows($r) > 0){
while($row = mysql_fetch_assoc($r)){
$display .= '<tr bgcolor="' . $title_cell_color . '">';
$display .= ' <td><img src="' . $bullet . '" /> <b>' . ucfirst($row['title']) . '</b> Posted in <a href="news.php?catid=' . $row['category'] . '">' . $row['category'] . '</a> on ' . $row['date'] . '</td>';
$display .= '</tr>';
$display .= '<tr bgcolor="' . $news_cell_color . '">';
$display .= ' <td>' . $row['content'] . '</td>';
$display .= '</tr>';
}
}else if(mysql_num_rows($r) === 0){
$display = '<tr><td>The Article was not found. It could have been moved or deleted. <a href="index.php">Try another selection</a></td></tr>';
}else{
$display = "<tr><td>MySQL ERROR:</td></tr><tr><td>" . mysql_error() . "</td></tr>";
}

}else{
// Get Results for the Category.
$r = mysql_query("SELECT category, COUNT(title) as count FROM news GROUP BY category");
if($r && mysql_num_rows($r) > 0){
$display .= '<tr bgcolor="' . $title_cell_color . '">';
$display .= ' <td>Please Select a Category</td>';
$display .= '</tr>';
while($row = mysql_fetch_assoc($r)){
$display .= '<tr bgcolor="' . $news_cell_color . '">';
$display .= ' <td><a href="news.php?catid=' . $row['category'] . '">' . ucwords($row['category']) . '</a> (' . $row['count'] . ' Articles)</td>';
$display .= '</tr>';
}
}else if(mysql_num_rows($r) === 0){
$display = '<tr><td>No News Categories Were Found In The Database. Please <a href="add.php?action=newCat">Add</a> Some.</td></tr>';
}else{
$display = "<tr><td>MySQL ERROR:</td></tr><tr><td>" . mysql_error() . "</td></tr>";
}
}

echo "<br />
<table width='100%'>
" . $display . "
</table>
<br />";

In my var.php file, I have a limit variable:
<?php
$news_limit="10"; // number of news that script shows
?>
As you can see from the above code, this variable is used to define how many news items are show on a page. At the moment, by default, it shows the 10 newest articles, and gives no option for the others. What I want is for all articles to be displayed, but paginated into pages of 10 articles.

Any suggestions on how to do this would be much appreciated.
thanks!
Ashley

dagon

ashleycox;10974703 wrote:
Hi all,
I know there are plenty of topics and tutorials out there that cover this, but all seam to recommend different things and i'm nto sure what to use.

many a way to skin a cat, choose one that appeals.

doobster

Typical approach is as follows:

Get a count of all news articles. Be sure to use the COUNT() function and not query all rows for this...
Divide this number by your per page variable to get the number of pages. It's been a while since I've written pagination, but I think you floor this number.
Default a $_GET variable that will maintain your "current page", e.g. if it doesn't exist it will be 1.
Before doing your query, but after your count query, you need to calculate the start and end limit using the current page variable. start = (current page - 1) * per page. end = start + per page
Update your query to use LIMIT $start, $end
Do a for loop from 1 to max pages creating a link to the current page setting the $_GET parameter of the "current page" to the loop counter so when the user clicks on it, the calculation in step 4 will be processed based on that page number.

A lot of theory above, but the best place to start is always pseudo code! At the end of the day, this is why I like to use frameworks because someone has already written this for me!

ashleycox

Hi,
Thanks for your reply. Unfortunately i'm not great at php yet 🙂 . Any chance you could provide some code examples?
Thanks and Sorry to be such a pain :d
Ashley

doobster

// default to page 1; otherwise, get from URL
$current_page = array_key_exists('page', $_GET) ? $_GET['page'] : 1;

$start = ($current_page - 1) * $news_limit;
$end = $start + 10;

// get count of news items
$query = "SELECT COUNT(id) FROM news WHERE category='{$_catid}'"; 
$r = mysql_query($query);
$row = mysql_fetch_array($r);

$total = $row[0];
$pages = $total / $news_limit;

$query = "SELECT title, id, DATE_FORMAT(postdate, '%Y-%m-%d') as date FROM news WHERE category='{$_catid}' ORDER BY id DESC LIMIT $start, $end"; 
$r = mysql_query($query);

// output pages
for ($i = 1; $i <= $pages; $i++) {
   echo '<a href="' . $_SERVER['PHP_SELF'] . '?page=' . $i . '">Page ' . $i . '</a> | ';
}

The above should get you started, might be a few typos.

dagon

im really liking the jquery tablesorter plugin with pagination, then you get all the data let js sort it, you get funky things like being able to sort any col with no reload; as well as the pagination with out reload

ashleycox

thanks very much for all your help, working great now!