正在迁移到准备好的语句 - Migrating to prepared statements

Migrating to prepared statements

本文关键字：语句准备好迁移 | 更新日期: 2023-09-27

我知道有类似的主题，但它们对我没有帮助，我想将我的代码迁移到准备好的语句中，但我不断得到错误或错误的答案。计划是得到这样的代码：

$sql = "SELECT * FROM our_videos ORDER BY datemade DESC LIMIT 10";
$query = mysqli_query($db_conx, $sql);
while($row = mysqli_fetch_array($query, MYSQL_ASSOC))
{
    $id             = $row["id"];
    $title          = $row["title"];
    $description    = $row["description"];
    echo "this is first row id".$id."";
}

对于这样的事情（这是我目前正在研究的错误例子）：

$stmt = mysqli_prepare($db_conx, "SELECT id,title,description,champion FROM our_videos WHERE datemade BETWEEN NOW() - INTERVAL ? DAY AND NOW() AND LENGTH(champion) - LENGTH(REPLACE(champion, '$', '')) =? ORDER BY ? LIMIT 10");
$filter_date           = mysqli_real_escape_string($db_conx,$_POST['filter_by_date']);
$filter_arrangement    = mysqli_real_escape_string($db_conx,$_POST['filter_by_arrangement']);
$filter_champion_count = mysqli_real_escape_string($db_conx,$_POST['filter_by_champion_count']);
mysqli_stmt_bind_param($stmt, 'iis', $filter_date, $filter_champion_count, $filter_arrangement);
mysqli_stmt_execute($stmt);
while($data = mysqli_fetch_array($res, MYSQLI_ASSOC))
{
    echo 'My name is '.$data['id'].'and my email is <br/>';
}
$res = mysqli_stmt_bind_result($stmt, $single_id, $single_title, $single_description, $single_champion);

我在尝试上面的例子时遇到了这个错误：

警告：mysqli_fetch_array（）要求参数1为mysqli_result，中给定null/主页/tropol4lol/domains/troll4lol.com/public_html/other_videos.php12号线

我真的很困惑这整个准备好的陈述，看起来不会很难，但。。。

根据文档中的示例对代码进行了一些更改http://php.net/manual/en/mysqli-stmt.bind-result.php

参数化查询的一个好处是，我们不再（通常）需要转义数据，这是为我们完成的：D

$sql = "SELECT id,title,description,champion
        FROM our_videos
        WHERE datemade BETWEEN NOW() - INTERVAL ? DAY AND NOW()
        AND LENGTH(champion) - LENGTH(REPLACE(champion, '$', '')) =?
        ORDER BY # // :(
        LIMIT 10";
$stmt = mysqli_prepare($db_conx, $sql);
mysqli_stmt_bind_param($stmt, 'iis', $_POST['filter_by_date'], $_POST['filter_by_champion_count'], $_POST['filter_by_arrangement']);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $single_id, $single_title, $single_description, $single_champion);
while (mysqli_stmt_fetch($stmt)) {
    echo $single_id . ' ' . $single_title . ' ' . $single_description . ' ' . $single_champion;
}
mysqli_stmt_close($stmt);

不过，请注意不幸的是，您只能使用占位符绑定数据。列名/表名是架构的一部分，不能绑定。

您不应该使用mysqli_fetch_array，因为您在提取时使用mysqli_stmt_bind_result来设置变量。

mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $single_id, $single_title, $single_description, $single_champion);
while (mysqli_stmt_fetch($stmt)) {
    echo "My name is $single_id and my email is <br>";
}

请参阅文档中的示例。