mysqli_fetch_array()，准备好的语句和LIKE语句 [英] mysqli_fetch_array(), prepared statement, and LIKE statement

问题描述

我正在尝试将mysqli准备好的语句与LIKE语句查询和通配符运算符一起使用.调试后，在整个代码中添加echo语句，可以看到我的while语句未执行.你在这里看到我在做什么错吗?

I'm trying to use mysqli prepared statements with a LIKE statement query and wildcard operators. After debugging be sprinkling echo statements throughout the code, I can see that my while statement is not executing. Can you see what I'm doing wrong here?

这是我第一次在这个论坛上提问，因此我很抱歉这不是一个好问题.我花了6个小时来尝试使代码的准备好的语句部分正常工作，但找不到任何无法完全解决我问题的线程(例如

This is my first time asking on this forum, so I apologize if this isn't a good question; I've spent 6 hours trying to get the prepared statement section of my code to work and I can't find any threads addressing my question that don't go completely over my head (e.g. How can I put the results of a MySQLi prepared statement into an associative array?). The two closest I found were:

在准备好的语句中使用通配符-MySQLi 和>用LIKE组合PHP准备的语句.

这是我的代码的相关摘录:

Here's the relevant excerpt of my code:

    //set up and execute queries
    $titleQuery = "SELECT keyframeurl, videoid, title, creationyear, sound, color, 
    duration, genre FROM openvideo WHERE title LIKE CONCAT ('%', ?, '%') 
    ORDER BY $order";

    if($stmt = mysqli_prepare($db, $titleQuery)){
        //bind parameters
        mysqli_stmt_bind_param($stmt, 's', $trimmedTitleSearch);
        //execute query
        mysqli_stmt_execute($stmt);
        //bind results
        mysqli_stmt_bind_result($stmt, $keyframeurl, $videoid, $title, $year, $sound, 
        $color, $duration, $genre); 
        //store result so num rows can be counted
        $result = mysqli_stmt_store_result($stmt);
        //fetch results 
        while ($row = mysqli_fetch_array($result, MYSQL_ASSOC)) {
            echo "<tr>";
                echo "<td><a href=\"".$row['keyframeurl']."\">".$row['videoid']."</a></td>";
                echo "<td>" . $row['title'] . "</td>";
                echo "<td>" . $row['year'] . "</td>";
                echo "<td>" . $row['sound'] . "</td>";
                echo "<td>" . $row['color'] . "</td>";
                echo "<td>" . $row['duration'] . "</td>";
                echo "<td>" . $row['genre'] . "</td>";
            echo "</tr>";   
        }
    }
    else {
    // Error 
    printf("Prepared Statement Error: %s\n", $db->error);
    }

感谢您的任何建议！

推荐答案

您正在混合两种获取结果的样式.要么使用丑陋的bind_result方法(然后使用fetch()获取数据)，要么尝试使用get_result()-因此，您将能够使用fetch_array()(尽管不能保证).

You are mixing 2 styles of fetching results. Either use ugly bind_result way (and get your data using fetch() then), or try to use get_result() - so, you'll be able to use fetch_array() (not guaranteed though).

无论如何，只要摆脱所有混乱并使用PDO.

Anyway, just get rid of all that mess and use PDO.

$titleQuery = "SELECT keyframeurl, videoid, title, creationyear, sound, color, 
duration, genre FROM openvideo WHERE title LIKE CONCAT ('%', ?, '%') 
ORDER BY $order";

$stmt = $pdo->prepare($titleQuery);
$stmt->execute(array($trimmedTitleSearch));
$data = $stmt->fetchAll();

foreach ($data as $row ) {
    // the rest is the same as yours

我希望您正确清除了$ order变量.最好的方法显然是通过占位符添加它，因此，您将需要一个允许它的库， SafeMysql 例如:

I hope you properly sanitized your $order variable. The best way would be apparently to add it via placeholder, so, you will need a library that allows it, SafeMysql for example:

$sql = "SELECT * FROM openvideo WHERE title LIKE CONCAT ?s ORDER BY ?n";
$data = $db->getAll($sql,"%$trimmedTitleSearch%", $order);
foreach ($data as $row ) {
    // the rest is the same as yours

记下代码量，并与当前使用的原始API调用量进行比较

Note the amount of code and compare with that load of raw API calls you are using at the moment

这篇关于mysqli_fetch_array()，准备好的语句和LIKE语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！