php - 将 mysqli 分页转换为准备好的语句分页-6ren

php - 将 mysqli 分页转换为准备好的语句分页

转载作者：行者123 更新时间：2023-11-30 01:22:12

当前 mysqli 的工作代码如下。我想将其转换为准备好的语句。基本上有三个 sql 查询需要转换为准备好的语句。第一部分对我来说是最难的部分，要找出获取计数值的准备方法，我最终设法弄清楚了。

<?php
// Script and tutorial written by Adam Khoury @ developphp.com
// Line by line explanation : youtube.com/watch?v=T2QFNu_mivw
include_once("storescripts/connect_to_mysqli.php");
// This first query is just to get the total count of rows
$sql2 = "SELECT COUNT(id) FROM products ";
$query2 = mysqli_query($myConnection, $sql2);
$row = mysqli_fetch_row($query2);
// Here we have the total row count
$rows = $row[0];
// This is the number of results we want displayed per page
$page_rows = 10;
// This tells us the page number of our last page
$last = ceil($rows/$page_rows);
// This makes sure $last cannot be less than 1
if($last < 1){
    $last = 1;
}
// Establish the $pagenum variable
$pagenum = 1;
// Get pagenum from URL vars if it is present, else it is = 1
if(isset($_GET['pn'])){
    $pagenum = preg_replace('#[^0-9]#', '', $_GET['pn']);
}
// This makes sure the page number isn't below 1, or more than our $last page
if ($pagenum < 1) { 
    $pagenum = 1; 
} else if ($pagenum > $last) { 
    $pagenum = $last; 
}
// This sets the range of rows to query for the chosen $pagenum
$limit = 'LIMIT ' .($pagenum - 1) * $page_rows .',' .$page_rows;
// This is your query again, it is for grabbing just one page worth of rows by applying $limit
$sql = "SELECT id,product_name, price FROM products ORDER BY product_name DESC $limit";
$query = mysqli_query($myConnection, $sql);
// This shows the user what page they are on, and the total number of pages
$textline1 = "Products (<b>$rows</b>)";
$textline2 = "Page <b>$pagenum</b> of <b>$last</b>";
// Establish the $paginationCtrls variable
$paginationCtrls = '';
// If there is more than 1 page worth of results
if($last != 1){
    /* First we check if we are on page one. If we are then we don't need a link to 
       the previous page or the first page so we do nothing. If we aren't then we
       generate links to the first page, and to the previous page. */
    if ($pagenum > 1) {
        $previous = $pagenum - 1;
        $paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$previous.'">Previous</a> &nbsp; &nbsp; ';
        // Render clickable number links that should appear on the left of the target page number
        for($i = $pagenum-4; $i < $pagenum; $i++){
            if($i > 0){
                $paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$i.'">'.$i.'</a> &nbsp; ';
            }
        }
    }
    // Render the target page number, but without it being a link
    $paginationCtrls .= ''.$pagenum.' &nbsp; ';
    // Render clickable number links that should appear on the right of the target page number
    for($i = $pagenum+1; $i <= $last; $i++){
        $paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$i.'">'.$i.'</a> &nbsp; ';
        if($i >= $pagenum+4){
            break;
        }
    }
    // This does the same as above, only checking if we are on the last page, and then generating the "Next"
    if ($pagenum != $last) {
        $next = $pagenum + 1;
        $paginationCtrls .= ' &nbsp; &nbsp; <a href="'.$_SERVER['PHP_SELF'].'?pn='.$next.'">Next</a> ';
    }
}


$dynamicList = '';
while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){
    $id = $row["id"];
    $product_name = $row["product_name"];
    $price = $row["price"];
    $dynamicList .= "
                <li><div class='product'>
                <a href='product.php?id=$id' class='info'>
                <span class='holder'>
                <img src='inventory_images/$id.jpg' alt='$product_name' />
                <span class='book-name'>$product_name</span>
                </a>
                 <a href='product.php?id=$id' class='buy-btn'>RM<span class='price'>$price</span></a>
                </div>
                </li>

              ";
}
// Close your database connection
mysqli_close($myConnection);
?>
<!DOCTYPE html>
<html>
<head>
<style type="text/css">
body{ font-family:"Trebuchet MS", Arial, Helvetica, sans-serif;}
div#pagination_controls{font-size:21px;}
div#pagination_controls > a{ color:#06F; }
div#pagination_controls > a:visited{ color:#06F; }
</style>
</head>
<body>
<div>
  <h2><?php echo $textline1; ?> Paged</h2>
  <p><?php echo $textline2; ?></p>
  <p><?php echo $dynamicList; ?></p>
  <div id="pagination_controls"><?php echo $paginationCtrls; ?></div>
</div>
</body>
</html>

提前感谢您的帮助

最佳答案

这个答案已经通过使用 Paginate result set having written the query with prepared statements, 解决了方法2.

完整代码引用如下。谢谢

<?php
// Script and tutorial written by Adam Khoury @ developphp.com
// Line by line explanation : youtube.com/watch?v=T2QFNu_mivw
include_once("storescripts/connect_to_mysqli.php");
// This first query is just to get the total count of rows
$stmt=$myConnection->prepare('SELECT COUNT(id) FROM products');
// Don't use bind_result()...
// execute your statement
$stmt->execute();
// Get result set into a MySQLi result resource
$result = $stmt->bind_result($id);

// array to hold all rows
$rows = array();

// All results bound to output vars
while ($stmt->fetch()) {
  // Append an array containing your result vars onto the rowset array
  $rows[] = array(
    'id' => $id
  );
}
  $rows=$id;


// This is the number of results we want displayed per page
$page_rows = 10;
// This tells us the page number of our last page
$last = ceil($rows/$page_rows);
// This makes sure $last cannot be less than 1
if($last < 1){
    $last = 1;
}
// Establish the $pagenum variable
$pagenum = 1;
// Get pagenum from URL vars if it is present, else it is = 1
if(isset($_GET['pn'])){
    $pagenum = preg_replace('#[^0-9]#', '', $_GET['pn']);
}
// This makes sure the page number isn't below 1, or more than our $last page
if ($pagenum < 1) { 
    $pagenum = 1; 
} else if ($pagenum > $last) { 
    $pagenum = $last; 
}
$dynamicList = "";
$stmt = $myConnection->prepare('SELECT id,product_name,price FROM products ORDER BY product_name LIMIT ?,? ');
$begin= ($pagenum - 1) * $page_rows;
$end= $page_rows;
$stmt->bind_param('ii',$begin,$end);
$stmt->execute();
        /* store result */
        $stmt->store_result();
        /* get the row count */
        $count = $stmt->num_rows;
        if ($count >= 1) {
            $stmt->bind_result($id, $product_name, $price);
// This shows the user what page they are on, and the total number of pages
$textline1 = "Products (<b>$rows</b>)";
$textline2 = "Page <b>$pagenum</b> of <b>$last</b>";
// Establish the $paginationCtrls variable
$paginationCtrls = '';
// If there is more than 1 page worth of results
if($last != 1){
    /* First we check if we are on page one. If we are then we don't need a link to 
       the previous page or the first page so we do nothing. If we aren't then we
       generate links to the first page, and to the previous page. */
    if ($pagenum > 1) {
        $previous = $pagenum - 1;
        $paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$previous.'">Previous</a> &nbsp; &nbsp; ';
        // Render clickable number links that should appear on the left of the target page number
        for($i = $pagenum-4; $i < $pagenum; $i++){
            if($i > 0){
                $paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$i.'">'.$i.'</a> &nbsp; ';
            }
        }
    }
    // Render the target page number, but without it being a link
    $paginationCtrls .= ''.$pagenum.' &nbsp; ';
    // Render clickable number links that should appear on the right of the target page number
    for($i = $pagenum+1; $i <= $last; $i++){
        $paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$i.'">'.$i.'</a> &nbsp; ';
        if($i >= $pagenum+4){
            break;
        }
    }
    // This does the same as above, only checking if we are on the last page, and then generating the "Next"
    if ($pagenum != $last) {
        $next = $pagenum + 1;
        $paginationCtrls .= ' &nbsp; &nbsp; <a href="'.$_SERVER['PHP_SELF'].'?pn='.$next.'">Next</a> ';
    }
}


while ($stmt->fetch()) {
                "$id, $product_name, $price,";
                $dynamicList .= "
                <li><div class='product'>
                <a href='product.php?id=$id' class='info'>
                <span class='holder'>
                <img src='inventory_images/$id.jpg' alt='$product_name' />
                <span class='book-name'>$product_name</span>
                </a>
                 <a href='product.php?id=$id' class='buy-btn'>RM<span class='price'>$price</span></a>
                </div>
                </li>

              ";
}
    }
// Close your database connection
mysqli_close($myConnection);
?>
<!DOCTYPE html>
<html>
<head>
<style type="text/css">
body{ font-family:"Trebuchet MS", Arial, Helvetica, sans-serif;}
div#pagination_controls{font-size:21px;}
div#pagination_controls > a{ color:#06F; }
div#pagination_controls > a:visited{ color:#06F; }
</style>
</head>
<body>
<div>
  <h2><?php echo $textline1; ?> Paged</h2>
  <p><?php echo $textline2; ?></p>
  <p><?php echo $dynamicList; ?></p>
  <div id="pagination_controls"><?php echo $paginationCtrls; ?></div>
</div>
</body>
</html>

关于php - 将 mysqli 分页转换为准备好的语句分页，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/18414229/

文章推荐： mysql - 查询导致另一表中的一行的多行

文章推荐： mysql - 需要有关谷歌地图过滤和将数据保存到数据库的建议

文章推荐： mysql - 从列中选择顶部值

文章推荐： PHP和Ajax上传

php - 警告:mysqli::mysqli() [mysqli.mysqli]: (08004/1040): OpenCart 中的连接过多
这个问题已经有答案了: php, mysql - Too many connections to database error (6 个回答) 已关闭 8 年前。伙计们，我的 opencart 网站
php - MySQLi mysqli 获取数组关联替换不起作用
这是我想要的，但不能让它与新的 MySQLi 一起工作，只是因为我的主机没有所有新的 php 等... 但是一定有某种解决方案，或者这就是 MYSQLI 能做的？请不要谈论 PDO，因为即使是丑陋的
mysqli - 使用 mysqli 获取准备好的语句的实际 SQL？
这个问题在这里已经有了答案: Is there a way to see a prepared query as it will be executed on the database? [dupli
php - mysqli::query(): 无法获取 mysqli
Warning: mysqli::query(): Couldn't fetch mysqli in C:\Program Files (x86)\EasyPHP-DevServer-13.1VC9\
php - mysqli::query(): 无法获取 mysqli
Warning: mysqli::query(): Couldn't fetch mysqli in C:\Program Files (x86)\EasyPHP-DevServer-13.1VC9\
php - mysqli::query(): 无法获取 mysqli
Warning: mysqli::query(): Couldn't fetch mysqli in C:\Program Files (x86)\EasyPHP-DevServer-13.1VC9\
mysqli - undefined variable : mysqli - PHP OOP
如何在扩展类中实现 mysqli？我正在上传图像并将其存储在 MySQL 数据库中，但出现此错误: Notice: Undefined variable: mysqli in ...ecc/ecc/
php - mysql 与 mysqli。 mysqli 慢吗？
我终于切换到 mysqli 了。但是我发现了显着的性能差异。我有一个脚本，可以进行大约 25.000 个查询。该脚本使用 mysqli 和 mysqlnd 作为驱动程序需要 15 秒10 秒使用
php - Mysqli 事务查询优于普通 php mysqli 查询
这个问题已经有答案了: Why can't I run two mysqli queries? The second one fails [duplicate] (2 个回答) 已关闭 4 年前。我
PHP 警告:mysqli::query():无法获取 mysqli
这个问题已经有答案了: mysqli::query(): Couldn't fetch mysqli (4 个答案) 已关闭 6 年前。这是我当前的代码: query("SELECT * FROM
php mysqli 检查 mysqli 查询是否返回 false
这个问题在这里已经有了答案: What to do with mysqli problems? Errors like mysqli_fetch_array(): Argument #1 must
php - mysqli::mysqli(): (HY000/2002) 本地主机不工作
我正在尝试连接到我的数据库，但当我使用 127.0.0.1 而不是本地主机时出现错误。 Warning: mysqli::mysqli(): (HY000/2002): A connection at
php - 警告:mysqli::mysqli(): header 和客户端库次要版本不匹配
使用 WHM 更新 mysql 后我的网站面临这个问题。 Warning: mysqli::mysqli(): Headers and client library minor version mis
php - MySQLi: $mysqli->query 不会插入，也不会返回错误
我试图通过 MySQLi 将简单数据插入到我的 MySQL 表中，但是它拒绝插入，并且没有报告任何错误消息。我想强调的是，当直接输入 PhpMyAdmin 时，此查询功能正常(当然，替换了变量) r
php - `print_r($mysqli,1)` 更改 `$mysqli->affected_rows `
我正在使用用户断言函数，例如: debug_assert ( gettype($ob)=='object', "Not an object " .print_r($ob
php - 数据库错误 : [mysqli. mysqli]:用户已经有超过 'max_user_connections' 个事件连接
我有一个每天只有大约 100 人访问的站点，但是当我以用户身份登录时收到此错误消息: Warning: mysqli::mysqli() [mysqli.mysqli]: (42000/1203):
php - mysqli::query(): 在 foreach 循环的第一行之后无法获取 mysqli
我创建了一个 foreach 循环来将数据添加到 MySQL 数据库，但在将第一行添加到数据库后，我收到错误“mysqli::query(): Couldn't fetch mysqli”。 PHP
PHP mySQLi : if "&&" protocol and mySQLi statement error checking
我正在使用 mysqli 实现一些基本的 getter 函数，并且正在考虑一种在错误检查中变得懒惰但仍然正确的方法。所以我写了这种类型的代码段 if(!mysqli_stmt_bind_param(
php - mysqli::commit 和 mysqli::rollback 如何工作？
我已经阅读了在线 php 手册，但我仍然不确定这两个函数的工作方式:mysqli::commit 和 mysqli::rollback。我要做的第一件事是: $mysqli->autocommit(
php - mysqli::mysqli(): php_network_getaddresses: getaddrinfo 失败:名称或服务未知
这个问题在这里已经有了答案: What is the difference between single-quoted and double-quoted strings in PHP? (7 个答

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

php - 将 mysqli 分页转换为准备好的语句分页