I have a web page (PHP) with multiple queries in it to get the specific table data from the DB.
I recently tried to include pagination to it, but as it seems, the pagination system doesn't work., ie., it shows the page numbers but prints all the data from the DB table in the first page itself, leaving all other pages blank..,
Then I kept only one query in the PHP page then it works fine properly.. which is absurd.
The PHP query code for the single query (pagination work for this one):
<div class="navi">
<?php
//Sql starts now
$sql = "SELECT * FROM smf_messages where id_board = 4 LIMIT ".$pageLimit." , ".$setLimit;
$query = mysqli_query($conn, $sql);
while ($rec = mysqli_fetch_array($query)) {
$member = $rec["id_member"];
$replies = $rec["id_topic"];
?>
<div class="show"><a href="http://www.myurl.com/<?php echo $rec["id_msg"];?>.htm" target="_blank"><?php echo $rec['subject'];?></a></div>
<?php }
?>
</div>
<?php
// Calling the Pagination Function.
echo displayPaginationBelow($setLimit,$page);
?>
<script type="text/javascript">
var _gaq = _gaq || [];
_gaq.push(['_setAccount', 'UA-38304687-1']);
_gaq.push(['_trackPageview']);
(function() {
var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
})();
</script>
While The PHP query code for the multiple queries (pagination doesn't work for this one):
<div class="navi">
<?php
$sql1 = "SELECT * FROM smf_log_digest where note_type = 'topic' ORDER BY id_msg DESC";
$query1 = mysqli_query($conn, $sql1);
while ($rec1 = mysqli_fetch_array($query1)) {
$number = $rec1["id_msg"];
// SQl starts here
$sql = "SELECT * FROM smf_messages where id_msg = $number AND id_board = 4 LIMIT ".$pageLimit." , ".$setLimit;
$query = mysqli_query($conn, $sql);
while ($rec = mysqli_fetch_array($query)) {
$member = $rec["id_member"];
$replies = $rec["id_topic"];
?>
<div class="show"><a href="http://www.myurl.com/<?php echo $rec["id_msg"];?>.htm" target="_blank"><?php echo $rec['subject'];?></a></div>
<?php }
}
?>
</div>
<?php
// Calling the Pagination Function.
echo displayPaginationBelow($setLimit,$page);
?>
<script type="text/javascript">
var _gaq = _gaq || [];
_gaq.push(['_setAccount', 'UA-38304687-1']);
_gaq.push(['_trackPageview']);
(function() {
var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
})();
</script>
There is a function.php page as well..
<?php include('db.php'); ?>
<?php
function displayPaginationBelow($per_page,$page){
$page_url="?";
$conn = mysqli_connect('localhost','root','','msuryad_meded');
$query = mysqli_query($conn, "SELECT COUNT(*) as totalCount FROM smf_messages where id_board = 4");
$rec = mysqli_fetch_array($query);
$total = $rec['totalCount'];
$adjacents = "2";
$page = ($page == 0 ? 1 : $page);
$start = ($page - 1) * $per_page;
$prev = $page - 1;
$next = $page + 1;
$setLastpage = ceil($total/$per_page);
$lpm1 = $setLastpage - 1;
$setPaginate = "";
if($setLastpage > 1)
{
$setPaginate .= "<ul class='setPaginate'>";
$setPaginate .= "<li class='setPage'>Page $page of $setLastpage</li>";
if ($setLastpage < 7 + ($adjacents * 2))
{
for ($counter = 1; $counter <= $setLastpage; $counter++)
{
if ($counter == $page)
$setPaginate.= "<li><a class='current_page'>$counter</a></li>";
else
$setPaginate.= "<li><a href='{$page_url}page=$counter'>$counter</a></li>";
}
}
elseif($setLastpage > 5 + ($adjacents * 2))
{
if($page < 1 + ($adjacents * 2))
{
for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
{
if ($counter == $page)
$setPaginate.= "<li><a class='current_page'>$counter</a></li>";
else
$setPaginate.= "<li><a href='{$page_url}page=$counter'>$counter</a></li>";
}
$setPaginate.= "<li class='dot'>...</li>";
$setPaginate.= "<li><a href='{$page_url}page=$lpm1'>$lpm1</a></li>";
$setPaginate.= "<li><a href='{$page_url}page=$setLastpage'>$setLastpage</a></li>";
}
elseif($setLastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
{
$setPaginate.= "<li><a href='{$page_url}page=1'>1</a></li>";
$setPaginate.= "<li><a href='{$page_url}page=2'>2</a></li>";
$setPaginate.= "<li class='dot'>...</li>";
for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
{
if ($counter == $page)
$setPaginate.= "<li><a class='current_page'>$counter</a></li>";
else
$setPaginate.= "<li><a href='{$page_url}page=$counter'>$counter</a></li>";
}
$setPaginate.= "<li class='dot'>..</li>";
$setPaginate.= "<li><a href='{$page_url}page=$lpm1'>$lpm1</a></li>";
$setPaginate.= "<li><a href='{$page_url}page=$setLastpage'>$setLastpage</a></li>";
}
else
{
$setPaginate.= "<li><a href='{$page_url}page=1'>1</a></li>";
$setPaginate.= "<li><a href='{$page_url}page=2'>2</a></li>";
$setPaginate.= "<li class='dot'>..</li>";
for ($counter = $setLastpage - (2 + ($adjacents * 2)); $counter <= $setLastpage; $counter++)
{
if ($counter == $page)
$setPaginate.= "<li><a class='current_page'>$counter</a></li>";
else
$setPaginate.= "<li><a href='{$page_url}page=$counter'>$counter</a></li>";
}
}
}
if ($page < $counter - 1){
$setPaginate.= "<li><a href='{$page_url}page=$next'>Next</a></li>";
$setPaginate.= "<li><a href='{$page_url}page=$setLastpage'>Last</a></li>";
}else{
$setPaginate.= "<li><a class='current_page'>Next</a></li>";
$setPaginate.= "<li><a class='current_page'>Last</a></li>";
}
$setPaginate.= "</ul>
";
}
return $setPaginate;
}
?>
Please note that all the pages has db.php file included in the script.. And there was no error or warning shown..
Any help is appreciated..