带有 PHP 分页的 MYSQL 查询 [英] MYSQL query with PHP pagination

问题描述

我正在尝试编写一个脚本，该脚本从 MYSQL 数据库中提取特定查询，然后对结果进行分页.

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.

这是我的部分脚本:

<?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() . "
Query: $query");

while($row = mysql_fetch_array($result))
{
  $online .= "#". $ttotal . " 
";
  $online .= $row['status'] . "/" . $row['name'] . "/" . $row['gender'] . "/" . $row['age'] . "
";
  $online .= "<br>
";
  $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>
"; }

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

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

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

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.

编辑删除了所有无效的代码 RE:评论.

EDIT Removed all the none working code RE: Comments.

推荐答案

是的，首先你需要找出总记录数.然后再次运行查询，这次在查询结束时使用 LIMIT.例如 LIMIT 0, 10.此示例返回 10 个结果，从记录 0(第一条记录)开始.如果您使用变量动态设置限制结果的第一条记录，您可以让它随每个页面更新它.对于每个页面，您可以计算出您想要的起始记录.例如，$offset= $recordsperpage * 页码.

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

     })

这篇关于带有 PHP 分页的 MYSQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！