php - Ajax mysql php 过滤器不能与 mysqli 函数一起使用

Question

我正在使用 ajax、jquery、mysql 和 php 过滤器，它在 PDO 连接上工作正常，但是当我使用 MYSQLI 连接时，它不起作用并且不显示任何输出，我的 html 和 ajax 代码是

index.php

<table id="phones">
    <thead>
        <tr>
            <!--  <th width="15">ID</th>-->
            <th>Brand</th>
            <th>Model</th>
            <th>Price</th>
        </tr>
    </thead>
    <tbody>
    </tbody>
</table>

<div id="filter">
    <h2>Filter options</h2>
    <div>
        <input type="checkbox" id="Samsung">
        <label for="Samsung">Samsung</label>
    </div>
    <div>
        <input type="checkbox" id="iPhone">
        <label for="iPhone">iPhone</label>
    </div>
    <div>
        <input type="checkbox" id="HTC">
        <label for="HTC">HTC</label>
    </div>
    <div>
        <input type="checkbox" id="LG">
        <label for="LG">LG</label>
    </div>
    <div>
        <input type="checkbox" id="Nokia">
        <label for="Nokia">Nokia</label>
    </div>
</div>

<script src="jquery-latest.js"></script> 
<script>
    function makeTable(data){
        var tbl_body = "";
        $.each(data, function() {
            var tbl_row = "",
            currRecord = this;

            $.each(this, function(k , v) {
                if(k==='model'){
                    v = "<a href='content.php?id=" + currRecord['model'] +"'>" + v + "</a>";
                } else if (k==='price'){
                    v = "<span class='price'>" + v + "</span>";
                }
                tbl_row += "<td>"+ v +"</td>";
            });
            tbl_body += "<tr>"+tbl_row+"</tr>";
        });

        return tbl_body;
    }

    function getPhoneFilterOptions(){
        var opts = [];
        $checkboxes.each(function(){
          if(this.checked){
            opts.push(this.id);
          }
        });

        return opts;
    }

    function updatePhones(opts){
        if(!opts || !opts.length){
            opts = allBrands;
        }

        $.ajax({
          type: "POST",
          url: "submit.php",
          dataType : 'json',
          cache: false,
          data: {filterOpts: opts},
          success: function(records){
            $('#phones tbody').html(makeTable(records));
            updatePrices();
          }
        });
    }

    var $checkboxes = $("input:checkbox");
    $checkboxes.on("change", function(){
        var opts = getPhoneFilterOptions();
        updatePhones(opts);
    });


    var allBrands = [];
    $("input[type=checkbox]").each(function(){
        allBrands.push($(this)[0].id)
    })

    updatePhones();
    updatePrices();
</script>

submit.php(MYSQLI 连接)

<?php
$con = mysqli_connect("localhost", "root", "", "oop");
if(!$con){
    die ("Could not connect to database" . mysqli_connect_errno());
}

$opts = isset($_POST['filterOpts']) ? $_POST['filterOpts'] : '';
$qMarks = str_repeat('?,', count($opts) - 1) . '?';

$sql="SELECT name, model, price  FROM mobile_phone_name WHERE name IN ($qMarks)";
$result = mysqli_query($con, $sql);

while($row = mysqli_fetch_assoc($result))
{
   $name = $row['name'];
   $model = $row['model'];
   $price = $row['price'];

   $arr[] = array("name"=>"$name", "price"=>"$price", "model"=>"$model");
}
echo json_encode($arr);
?>

submit.php(PDO 连接)

<?php 
$pdo = new PDO('mysql:host=localhost;dbname=oop', 'root', '');
$opts = $_POST['filterOpts'];
$qMarks = str_repeat('?,', count($opts) - 1) . '?';
$statement = $pdo->prepare("SELECT name, model, price FROM mobile_phone_name WHERE name IN ($qMarks)");
$statement -> execute($opts);
$results = $statement -> fetchAll(PDO::FETCH_ASSOC);
echo json_encode($results);
?>

提前致谢

Answer 1

正如我在评论中所说，您不要将数据放入 MySQLi 准备好的语句的占位符中:

$opts = isset($_POST['filterOpts']) ? $_POST['filterOpts'] : '';
$qMarks = str_repeat('?,', count($opts) - 1) . '?';

$sql="SELECT name, model, price  FROM mobile_phone_name WHERE name IN ($qMarks)";
$result = mysqli_query($con, $sql);

while($row = mysqli_fetch_assoc($result))

$opts用在哪里？

它的工作原理如下(尚未测试):

$stmt = $con->prepare($sql);
foreach ($opts as $v) {
    $stmt->bind_param('s', $v);
}
$stmt->execute();

while($row = $stmt->fetch())

类型不匹配:

$opts = isset($_POST['filterOpts']) ? $_POST['filterOpts'] : '';

必须是

$opts = isset($_POST['filterOpts']) ? $_POST['filterOpts'] : array();