I trying to switch over to connecting to my database via PDO, but when doing so all my old working code needs to be rewrote. I’m trying to make a new section on my website and include pagination.
The pagination part is were I’m having the most trouble.
I have this old mysql pagination query here:
[php]include_once($_SERVER[‘DOCUMENT_ROOT’] . “/includes/database.php”);
foreach ($_REQUEST as $k => $v) {
$_REQUEST[$k] = mysql_real_escape_string($v);
}
$category = @$_REQUEST[“category”] ? $_REQUEST[“category”] : null;
$images = mysql_query(
$category ?
sprintf(
“SELECT * FROM htp_news WHERE date = ‘%s’”,
$category
) :
“SELECT * FROM htp_news”
);
if ($images) {
$total = mysql_num_rows($images);
if ($total) {
$per = 2;
$page = @$_REQUEST[“page”] ? $_REQUEST[“page”] : 1;
$pages = ceil($total/$per);
}
mysql_free_result($images);
}
?>
-
<?php
if ($category) {
$images = mysql_query(sprintf(
"SELECT * FROM htp_news WHERE date = '%s' ORDER BY date DESC LIMIT %d, %d",
$category, ($page - 1) * $per, $per
));
} else $images = mysql_query(sprintf(
"SELECT * FROM htp_news ORDER BY date DESC LIMIT %d, %d",
($page - 1) * $per, $per
));
while ($image=mysql_fetch_array($images))
{
?>
- ">
<?=$image["title"] ?><?= date("F d, Y", strtotime($image["date"])); ?>.articletext img{width:100%; height:auto;}<?php $string = $image["description"]; $max = 300; // or 200, or whatever if(strlen($string) > $max) { // find the last space < $max: $shorter = substr($string, 0, $max+1); $string = substr($string, 0, strrpos($shorter, ' ')).'...'; } echo $string; ?>
<?php
}
?>
for ($current = 1; $current <= $pages; $current++) {
if ($current != $page) {
if ($category) {
printf(
“<a href=”?category=%s&page=%d">%d\n",
$category, $current, $current
);
} else printf(
“<a href=”?page=%d">%d\n",
$current, $current
);
} else printf("%d\n", $page);
}
?>[/php]
I was wondering if any one could meet me in a collaboration site and help me rewrite this? If so please email me. I would be available anytime if someone could help me. I’ve been trying to figure this out for a week now.
I tried taking a stab at it by writing a Pagination Function and including it on my news page, I feel I almost have it , but I’m having a problem trying to limit the number of news articles per page on the news section of my website.
Currently I only have 4 articles in my news section. I would like to set the number of articles shown on each page to 2 articles per page. Then when I click page 2 it will show the next two articles. So on and so on. As of now the pagination numbers show 1-4 and on each page show all 4 articles. I want this to show 1-2, and 2 articles on each page.
Sorry for the code dump. I think the problem is in my query down below in the 3rd section.
I have my pagination function setup like this:
FYI I have my database connection script stored in my header.
[php]<?php
//Pagination Function
function pagination($per_page = 2, $page = 1, $url = ‘’, $total){
$adjacents = "2";
$page = ($page == 0 ? 1 : $page);
$start = ($page - 1) * $per_page;
$prev = $page - 1;
$next = $page + 1;
$lastpage = ceil($total/$per_page);
$lpm1 = $lastpage - 1;
$pagination = "";
if($lastpage > 1)
{
$pagination .= "<ul class='pagination'>";
$pagination .= "<li class='details'>Page $page of $lastpage</li>";
if ($lastpage < 7 + ($adjacents * 2))
{
for ($counter = 1; $counter <= $lastpage; $counter++)
{
if ($counter == $page)
$pagination.= "<li><a class='current'>$counter</a></li>";
else
$pagination.= "<li><a href='{$url}$counter'>$counter</a></li>";
}
}
elseif($lastpage > 5 + ($adjacents * 2))
{
if($page < 1 + ($adjacents * 2))
{
for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
{
if ($counter == $page)
$pagination.= "<li><a class='current'>$counter</a></li>";
else
$pagination.= "<li><a href='{$url}$counter'>$counter</a></li>";
}
$pagination.= "<li class='dot'>...</li>";
$pagination.= "<li><a href='{$url}$lpm1'>$lpm1</a></li>";
$pagination.= "<li><a href='{$url}$lastpage'>$lastpage</a></li>";
}
elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
{
$pagination.= "<li><a href='{$url}1'>1</a></li>";
$pagination.= "<li><a href='{$url}2'>2</a></li>";
$pagination.= "<li class='dot'>...</li>";
for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
{
if ($counter == $page)
$pagination.= "<li><a class='current'>$counter</a></li>";
else
$pagination.= "<li><a href='{$url}$counter'>$counter</a></li>";
}
$pagination.= "<li class='dot'>..</li>";
$pagination.= "<li><a href='{$url}$lpm1'>$lpm1</a></li>";
$pagination.= "<li><a href='{$url}$lastpage'>$lastpage</a></li>";
}
else
{
$pagination.= "<li><a href='{$url}1'>1</a></li>";
$pagination.= "<li><a href='{$url}2'>2</a></li>";
$pagination.= "<li class='dot'>..</li>";
for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
{
if ($counter == $page)
$pagination.= "<li><a class='current'>$counter</a></li>";
else
$pagination.= "<li><a href='{$url}$counter'>$counter</a></li>";
}
}
}
if ($page < $counter - 1){
$pagination.= "<li><a href='{$url}$next'>Next</a></li>";
$pagination.= "<li><a href='{$url}$lastpage'>Last</a></li>";
}else{
$pagination.= "<li><a class='current'>Next</a></li>";
$pagination.= "<li><a class='current'>Last</a></li>";
}
$pagination.= "</ul>\n";
}
return $pagination;
}
?> [/php]
And this is how I’m calling it on my news page: I think my problem is the query on the 3rd part.
1st part
[php]<?php
$title = ‘…’;
$page_description = “…”;
$thisPage=“news”;
include_once($_SERVER[‘DOCUMENT_ROOT’] . “/header.php”);
include_once($_SERVER[‘DOCUMENT_ROOT’] . “/includes/pagination.php”);
$id = ‘id’;
$date = $_GET[‘date’];
?>[/php]
2nd part
[php]<?php
$page=1;//Default page
$limit=1;//Records per page
$start=1;//starts displaying records from 0
if(isset($_GET[‘page’]) && $_GET[‘page’]!=’’){
$page=$_GET[‘page’];
}
$start=($page-1)*$limit;
?>
<?php $sql = "SELECT id FROM `htp_news`"; $ps_count = $db->prepare($sql); $ps_count->execute(array( ':id'=>$id)); $ps_count->execute(); $rows = $ps_count->rowCount(); $sql_2 = "SELECT id FROM `htp_news` where date = :date order by id ASC LIMIT :start, :limit"; $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE); $ps = $db->prepare($sql_2); $ps->execute(array( ':date'=>$_GET['date'], ':start'=>$start, ':limit'=>$limit)); $ps->execute(); while ($row = $ps->fetch(PDO::FETCH_ASSOC)) { ?> <?php } ?>[/php]3rd part (This is where I’m getting confused on how to limit the # of articles. I’m not sure what to add to the query to limit…?)
[php]<?php
$query = $db->query("SELECT id, date, title, description FROM htp_news ORDER BY date DESC $limit ");
while ($row = $query->fetch(PDO::FETCH_ASSOC))
{
?>
4th part:
[php]<?php
echo pagination($limit,$page,‘my-site-news.php?date=’.$date.’&page=’,$rows); //call function to show pagination
?>[/php]
I’m pretty desperate for some help. If someone could help me rewrite my original script that would be great.
Or, Any guidance or help on my 2nd attempt would be greatly appreciated as well.
Thanks!
I’ve been through it a few times and it’s hard for me to understand.