使用 LIKE 进行 PDO 分页 [英] PDO pagination with LIKE

问题描述

以下 PHP SQL 代码显示错误可恢复的致命错误:第 28 行的/home/customer/xxxx/fetch_data.php 中的类 PDOStatement 的对象无法转换为字符串我试图从表 filter 中显示产品信息$statement->execute();有两种，一种是统计搜索结果总数，一种是显示当前页面的产品.我刚接触 PDO 方法，并不是整体编码方面的专家.

The following PHP SQL code shows error Recoverable fatal error: Object of class PDOStatement could not be converted to string in /home/customer/xxxx/fetch_data.php on line 28 I was trying to display products information from the table filter There are two $statement->execute();, one is to count total search results and another one is to display products for the current page. I new to PDO method and not an expert in overall coding.

$filter_query = $stmt .'LIMIT '.$start.', '.$limit.''; 导致问题.使用该函数和相关函数，代码可以工作并显示数据.但是如果我启用它，就会出现错误.

The $filter_query = $stmt . 'LIMIT '.$start.', '.$limit.''; is causing issues. With that and related functions, the code works and displays data. But if I enable it, the error shows up.

    $limit = '5';
$page = 1;
if($_POST['page'] > 1)
{
  $start = (($_POST['page'] - 1) * $limit);
  $page = $_POST['page'];
}
else
{
  $start = 0;
}
$search = "%samsung%";
    
    $stmt = $connect->prepare("SELECT * FROM filter WHERE product_name LIKE :needle");
    $stmt->bindParam(':needle', $search, PDO::PARAM_STR);
    ##
    
    ##
    
    
    $filter_query = $stmt . 'LIMIT '.$start.', '.$limit.'';
    
    $statement->execute();
    $total_data = $stmt->rowCount();
    
    $stmt = $connect->prepare($filter_query);
    $stmt->execute();
    $result = $stmt->fetchAll();
    $total_filter_data = $stmt->rowCount();
    
    $output = '
     <h3> '.$total_data.' results found </h3>  
    and display each product

我按照 Your Common Sense 的建议尝试了以下代码，分页和总搜索结果计数工作正常，但没有显示任何产品.

I tried the following code as suggested by Your Common Sense and the pagination and total search result count is working fine, but no products are getting displayed.

    $limit = '5';
    $page = 1;
    if($_POST['page'] > 1)
    {
      $start = (($_POST['page'] - 1) * $limit);
      $page = $_POST['page'];
    }
    else
    {
      $start = 0;
    }
    $name=str_replace(' ', '%', $_POST['query']);
    $search = "%$name%";
    $base_sql = "SELECT %s FROM filter WHERE product_name LIKE ?";
    ##
    
    ##
    ####
    $count_sql = sprintf($base_sql, "count(*)");
    $stmt = $connect->prepare($count_sql);
    $stmt->execute([$search]);
    $total_data = $stmt->fetchColumn();
    ####
    $data_sql = $count_sql = sprintf($base_sql, "*")." LIMIT ?,?";
    $stmt = $connect->prepare($data_sql);
    $stmt->execute([$search, $start, $limit]);
    $result = $stmt->fetchAll();
    ##
    
    $output = '
   <h3> '.$total_data.' results found </h3>  ';
if($total_data > 0)
{
  foreach($result as $row)
  {
            and display each product

使用以下行使代码显示一些数据，但分页不起作用.

Using the following line makes the code show some data, but the pagination is not working.

$data_sql = sprintf($base_sql, "*");
    $stmt = $connect->prepare($data_sql);
    $stmt->execute([$search]);
    $result = $stmt->fetchAll();

推荐答案

这不是一项微不足道的任务，因为我们需要两次运行相同的查询，但只有细微的差别.第一个查询将使用 count(*) SQL 函数来获取符合搜索条件的记录总数.第二个查询将使用 LIMIT 子句选择实际字段以获取单个页面的数据.

That's a not a trivial task as we need to run the same query twice but with minor differences. The first query will use count(*) SQL function to get the total number of records matching the search criteria. The second query will select actual fields with LIMIT clause to get the data for the single page.

$base_sql = "SELECT %s FROM filter WHERE product_name LIKE ?";

$count_sql = sprintf($base_sql, "count(*)");
$stmt = $connect->prepare($count_sql);
$stmt->execute([$search]);
$total = $stmt->fetchColumn();

$data_sql = $count_sql = sprintf($base_sql, "*")." LIMIT ?,?";
$stmt = $connect->prepare($data_sql);
$stmt->execute([$search, $start, $limit]);
$data = $stmt->fetchAll();

重要提示:为了使此代码正常工作，请确保您连接到mysql 正确.正确的连接将使 LIMIT 子句接受占位符，并在出现任何问题时发出警告.

Important: In order for this code to work, make sure you are connecting to mysql properly. the proper connection will let a LIMIT clause to accept placeholders and also will warn you in case of any problems.

这篇关于使用 LIKE 进行 PDO 分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！