php - fatal error : Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064

Question

我正在创建一家商店并使用输入来获取结果，现在我有调用 PHP 脚本的 AJAX，它调用它很好，但我收到错误:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064

注意:错误行是 $query->execute(array(':input'=>$input)) 行

这是 AJAX 脚本(+ HTML 调用函数)

                     <input type="text" name="search_item" onkeyup="showItems(this.value)" id="search_item">
                     <script>
                        function showItems(str) {
                            if (str.length == 0) { 

                            } else {
                                var xmlhttp = new XMLHttpRequest();
                                xmlhttp.onreadystatechange = function() {
                                    if (this.readyState == 4 && this.status == 200) {
                                        document.getElementById("items").innerHTML = this.responseText;
                                    }
                                };
                                xmlhttp.open("GET", "searchScript.php?iName=" + str, true);
                                xmlhttp.send();
                            }
                        }
                    </script>

这是调用的 PHP:

    $input = $_REQUEST["iName"];
    $input = "%".$input."%"; 
$dsn = 'mysql:host=xxx.com;dbname=dbNameHidden;charset=utf8mb4';
$username = 'hidden';
$password = 'hidden';

try{
    // connect to mysql
    $con = new PDO($dsn,$username,$password);
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (Exception $ex) {
    echo 'Not Connected '.$ex->getMessage();
}
$query = $con->prepare("SELECT * FROM store AS s INNER JOIN product_pictures AS pp ON s.product_id = pp.id INNER JOIN product_name AS pn ON s.product_id = pn.id WHERE product_name LIKE %:input% LIMIT 9 ");
$query->execute(array(':input' => $input));
$items = $query->fetchAll();

Answer 1

将通配符添加到参数中:

$query = $con->prepare("SELECT ... WHERE product_name LIKE :input LIMIT 9 ");
$query->execute(array(':input' => '%' . $input. '%'));

这样通配符就包含在值中，基本上使查询如下:

SELECT .... WHERE product_name LIKE '%name%'