1

I am trying to write a script that pulls a specific query from a MYSQL database and then paginates the results.

I think I know what the solutions are its just a question of my lack of knowledge on how to implement / script them.

Here is my script that is partially working :

<?php
$tstart = 0; 
$tend = 0;
//$tpage = 0; // the start page
//$tpages = 0; // number of pages
$tpagelinks = ""; // stores the output pagination
$ttotal = 0; / total number of results
$trows = 5; // number of results per page
$online = ""; // stores the results to display

$dbhost = "localhost"; 
$dbuser = "****_models"; 
$dbpass = "****";

$dbcon = @mysql_connect($dbhost,$dbuser,$dbpass) or die('Database error: ' . mysql_error());

$db = mysql_select_db('****_cammodels', $dbcon) or die('Database error: ' . mysql_error());

$query = "SELECT * FROM cbmodels WHERE gender='f' AND status='Public' AND age<='22'";

$result = mysql_query($query) or die('Query failed: ' . mysql_error() . "\nQuery: $query");

while($row = mysql_fetch_array($result))
{
  $online .= "#". $ttotal . " \n";
  $online .= $row['status'] . "/" . $row['name'] . "/" . $row['gender'] . "/" . $row['age'] . "\n";
  $online .= "<br>\n";
  $ttotal ++;
 }
 $tstart = ($tpage * $trows) - $trows;
 $tend = $tstart + $trows;
 if ($tend > $ttotal) { $tend = $ttotal; }
 $tpages = floor($ttotal / $trows);
 if ($ttotal % $trows != 0) { $tpages ++; }
 if ($tpage > $tpages) { $tpage = 1; }

 if ($tpage > 1) { $tpagelinks .= "<a class='allonlinepages_url' href='new_from_db.php?page=" . ($tpage - 1) . "'>PREVIOUS</a>\n"; }

 for ($ti = 1; $ti <= $tpages; $ti ++)
 {
 if ($tpage == $ti) { $tpagelinks .= "$ti \n"; }

    else { $tpagelinks .= "<a class='allonlinepages_url' href='new_from_db.php?page=$ti' >$ti</a>\n"; }
 }

 if ($tpage < $tpages) { $tpagelinks .= "<a class='allonlinepages_url' href='new_from_db.php?page=" . ($tpage + 1) . "' >NEXT</a>\n"; }

echo $online;
echo $tpagelinks;

mysql_close($dbcon);
?>

What it is doing is showing all the results found in the database, the pagination count seems to be showing the correct number of pages but the links are wrong / do not show the next page of results.

I think I need to query the database to get a row count for the pagination first and then calculate the number of pages, then do another query to obtain the results maybe store that in another array as results per page ? or something to that effect. I have tried many times and the script above is the closest I have gotten to get it almost working all my other attempts just break the script and either produce strange results or none at all.

The script I made to pull the data from the actual feed to store in the database itself flagging online and offline works correctly so I have not included that since it is a separate script anyway.

Thank you in advance for any help given Guys.

EDIT Removed all the none working code RE: Comments.

Improve this question

2 Answers 2

Reset to default
12

Yes, first you need to find out the total number of records. Then run the query again, this time with a LIMIT at the end of your query. For example LIMIT 0, 10. This example returns 10 results starting with record 0 (the first record). If you dynamically set the first record of the limit results using a variable, you can have it update it with every page. For each page you can calculate what you want the starting record to be. For example, $offset= $recordsperpage * page number.

I'm not sure exactly what your variables are, so I won't toy with your script. Here's an example I had used for a project that I know works. Example script: 

//Use mysqli object instead of mysql_xxx, better security
$db3= new mysqli($hostname, $username, $password, $dbname);

     //how many records I want per page
    $perPage= 9;

    //I'm passing the startrecording as a Get variable in the page links 
    if($_GET['startingrecord']){
        $startingRecord=trim($_GET['startingrecord']);
    }

    //but if there is no get variable, start at record zero
    else {
        $startingRecord=0;
    }


    //prepare your query
    $stmt3 = $db3->prepare("SELECT DISTINCT file, category, P.productID, price, title, price * (1- sale) FROM Products AS P INNER JOIN OrderProducts AS OP ON P.productID NOT IN (Select productID from OrderProducts) Left JOIN Sale AS S on P.productID= S.ProductID ORDER BY productID DESC");

    // You can bind parameters if necessary, but it's not here

    //execute the query and store the result
    $stmt3 -> execute();
    $stmt3->store_result();

    //determine the number of total rows
    $numberRows= $stmt3->num_rows;

    //close the db
    $db3 -> close;

   //Reopen with limits this time.
    $db3= new mysqli($hostname, $username, $password, $dbname);


    $stmt3 = $db3->prepare("SELECT DISTINCT file, category, P.productID, price, title, price * (1- sale) FROM Products AS P INNER JOIN OrderProducts AS OP ON P.productID NOT IN (Select productID from OrderProducts) Left JOIN Sale AS S on P.productID= S.ProductID ORDER BY productID DESC LIMIT ?, ?");
    // bind the startingrecord from the get variable and the perpage variable.
     stmt3->bind_param("ii", $startingRecord, $perPage);

    //you use ceiling to make sure if that if there's a remainder, you have an extra page for the stray results
    $pages= ceil($numberRows/ $perPage);

   //This is me generating the page links
    for ($i=1; $i<= $pages; $i++) {
        if ($i==1){
            $start=0;
        }
        else{
            $start= ($i-1) * ($perPage);
        }
        echo "<a href='#' onclick='gallery(\"startingrecord=".$start."\")'>".$i." </a>";
    }   

    $stmt3 -> execute();
    $stmt3->store_result();
    $stmt3->bind_result($file, $category2, $productID, $price, $title2, $sale); 

    while($stmt3->fetch()){ 
    // do whatever you want to your result

     })
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

Ok, I have implemented those changes and now what I am getting is the correct number of results in the list but only 2 pages in the pagination however when you click the 2nd page it does show the next set of results... I echoed the var in count and it only shows 1 result though ?
I added the amended script to the original post just in case you don't notice it, Thanks... (I could include the url to the test page I suppose but it is hosted on an adult domain so I do not think that would be acceptable here)
Be careful of off-by-one errors. I've written pagination code several times and there are several places where you need to round up or add one that doesn't immediately make sense.
None of it is making much sense at the moment lol, I changed the calculations about a bit and made no progress... Other than confirming that it is only pulling 10 rows from the database, so I changed the var age<='22' to '42' and it is still only showing 2 pages 5 results on each ! when I know for a fact there is at least 25 possible matches for that query.
I will show you a pagination script I wrote a few days ago that you can use as a base. I will post it as soon as I am able. Now that I look at your code with a little more detail I noticed you are using deprecated objects...
|
-1 
<style>
ul.pagination {
font-family: "Arial", "Helvetica", sans-serif;
font-size: 13px;
height: 100%;
list-style-type: none;
margin: 20px 0;
overflow: hidden;
padding: 0; }
ul.pagination li.details {
background-color: white;
border-color: #C8D5E0;
border-image: none;
border-style: solid;
border-width: 1px 1px 2px;
color: #1E598E;
font-weight: bold;
padding: 8px 10px;
text-decoration: none; }
ul.pagination li.dot {
padding: 3px 0; }
ul.pagination li {
float: left;
list-style-type: none;
margin: 0 3px 0 0; }
ul.pagination li:first-child {
margin-left: 0; }
ul.pagination li a {
color: black;
display: block;
padding: 7px 10px;
text-decoration: none; }
ul.pagination li a img {
border: medium none; }
ul.pagination li a.current {
background-color: white;
border-radius: 0 0 0 0;
color: #333333; }
ul.pagination li a.current:hover {
background-color: white; }
ul.pagination li a:hover {
background-color: #C8D5E0; }
ul.pagination li a {
background-color: #F6F6F6;
border-color: #C8D5E0;
border-image: none;
border-style: solid;
border-width: 1px 1px 2px;
color: #1E598E;
display: block;
font-weight: bold;
padding: 8px 10px;
text-decoration: none; }
</style>
<?php
/******************************************pagination*****function*****************************************/
function pagination($per_page = 10, $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;
} 
/****************************************////**pagination*****function*****************************************/
?>



<!---------------------------pagination------------------------------------------------->
<?php 
//error_reporting(0);
$con = mysqli_connect("localhost","root","","accounts");
$page=1;//Default page
$limit=2;//Records per page
$start=0;//starts displaying records from 0
if(isset($_GET['page']) && $_GET['page']!=''){
$page=$_GET['page'];
}
$start=($page-1)*$limit;
?>


<!---------------------------pagination-------end------------------------------------------>

<table width="100%" >
<tr>
<td>

<?php

$query = mysqli_query($con,"select * from receipts");
$rows=mysqli_num_rows($query);
//newly registerd on top
$query=mysqli_query($con,"select * from receipts order by r_id ASC LIMIT $start, $limit");


if(mysqli_num_rows($query)>0){

while($row = mysqli_fetch_array($query,1)){
?>
  <table cellpadding="3" cellspacing="3" border="1px #f8f8f8" width="100%" style="background-color:#FFFFFF;border:1px solid #e8e8e8">  
  <tr>
  <td id="class1">Name</td>
  <td id="class2"><?php echo $row['party_name'] ;?></td>
  </tr>
    <tr><td id="class1">Roll No</td>
    <td width="700px" id="class2"><?php echo $row['receipt_no']; ?></td>
    </tr>
    <tr>
    <td id="class1" width="170px">Date</td>
    <td id="class2"><?php echo $row['date1']; ?></td>
    </tr>
    </table>

<?php
}}

?>
</td>
</tr>
</table>

<?php
if(empty($_GET['id']))
{
    $id = 1;
}
if(isset($_GET['id'])){
    $id = $_GET['id'];
}
echo '</table><table>';
echo "<tr><td>";

echo pagination($limit,$page,'try.php?id='.$id.'&page=',$rows); //call function to show pagination

echo "</td></tr>";
echo "</table>";
?>
</div>
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.