使用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.

这是我的脚本，部分起作用:

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.

在此先感谢您对Guys的帮助.

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