MYSQL query with PHP pagination

问题:

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.

回答1:

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

     })

回答2:

<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>