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

2022-01-04 00:00:00 php mysql mysqli pagination

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

)";$textline2 = "$last 的页面 $pagenum";//建立 $paginationCtrls 变量$paginationCtrls = '';//如果结果超过 1 页if($last != 1){/* 首先我们检查我们是否在第一页.如果我们是，那么我们不需要链接到上一页或第一页所以我们什么都不做.如果我们不是那么我们生成指向第一页和上一页的链接.*/如果 ($pagenum > 1) {$previous = $pagenum - 1;$paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$previous.'">Previous</a>&nbsp;&nbsp;';//呈现应出现在目标页码左侧的可点击编号链接for($i = $pagenum-4; $i < $pagenum; $i++){如果($ i > 0){$paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$i.'">'.$i.'</a>&nbsp;';}}}//渲染目标页码，但没有链接$paginationCtrls .= ''.$pagenum.'&nbsp;';//呈现应出现在目标页码右侧的可点击编号链接for($i = $pagenum+1; $i <= $last; $i++){$paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$i.'">'.$i.'</a>&nbsp;';if($i >= $pagenum+4){休息;}}//和上面一样，只检查我们是否在最后一页，然后生成Next"如果($pagenum ！= $last){$next = $pagenum + 1;$paginationCtrls .= ' &nbsp;&nbsp;<a href="'.$_SERVER['PHP_SELF'].'?pn='.$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'><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>
"；}//关闭数据库连接mysqli_close($myConnection);?><!DOCTYPE html><头><style type="text/css">body{ font-family:"Trebuchet MS", Arial, Helvetica, sans-serif;}div#pagination_controls{font-size:21px;}div#pagination_controls >{ 颜色:#06F;}div#pagination_controls >一个:访问{颜色:#06F；}</风格><身体><div><h2><?php echo $textline1;?>分页<p><?php echo $textline2;?></p><p><?php echo $dynamicList;?></p><div id="pagination_controls"><?php echo $paginationCtrls;?></div>

预先感谢您的任何帮助

解决方案 

$stmt=$myConnection->prepare('SELECT COUNT(id) FROM products');//不要使用 bind_result()...//执行你的语句$stmt->execute();//获取结果集到一个 MySQLi 结果资源中$result = $stmt->bind_result($id);//保存所有行的数组$rows = array();//所有结果绑定到输出变量而 ($stmt-> fetch()) {//将包含结果变量的数组附加到行集数组$rows[] = 数组('id' =>$id);}$rows=$id;

第一部分.我想你已经明白了

the current working code with mysqli is as below. I would like to convert it to prepared statements. there is basically three sql query need to convert to prepared statement. the first part is the hardest part for me to figure out the prepared way to get count value which i kinda managed to figure it out eventually.

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

thanks in advance for any assistance

解决方案 

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

for the first part. i think you have got it

相关文章