SQL LIKE查询失败-预备语句中出现致命错误 [英] SQL LIKE query failing - fatal error in prepared statement

问题描述

我有以下代码:

$countQuery = "SELECT ARTICLE_NO FROM ? WHERE upper(ARTICLE_NAME) LIKE '% ? %'";
if ($numRecords = $con->prepare($countQuery)) {
    $numRecords->bind_param("ss", $table, $brand);
    $numRecords->execute();
    $data = $con->query($countQuery) or die(print_r($con->error));
    $rowcount = mysql_num_rows($data);
    $rows = getRowsByArticleSearch($query, $table, $max);
    $last = ceil($rowcount/$page_rows);
}

应该可以正常工作.但是我收到以下错误:

Which should work fine. However I receive the error that :

您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册，以获取在'?附近使用的正确语法.在第1行的较高(ARTICLE_NAME)喜欢'％?％''的地方

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? WHERE upper(ARTICLE_NAME) LIKE '%?%'' at line 1

如果我放

SELECT ARTICLE_NO FROM AUCTIONS WHERE upper(ARTICLE_NAME) LIKE '% o %';

查询工作正常.上面定义了$ table，并且从GET接收了查询，并且两者都是正确的有效值.为什么会失败?

The query works fine. $table is defined above, and query is received from GET, and both are correct valid values. Why is this failing?

更改为:

$countQuery = "SELECT ARTICLE_NO FROM AUCTIONS1 WHERE upper(ARTICLE_NAME) LIKE '% ? %'";

if ($numRecords = $con->prepare($countQuery)) {

    $numRecords->bind_param("s", $query);

导致错误:

警告:mysqli_stmt :: bind_param()[mysqli-stmt.bind-param]:变量数与C:\ Program Files \ EasyPHP 3.0 \ www \ prog \ get_records中已准备好的语句中的参数数不匹配.第38行的PHP

Warning: mysqli_stmt::bind_param() [mysqli-stmt.bind-param]: Number of variables doesn't match number of parameters in prepared statement in C:\Program Files\EasyPHP 3.0\www\prog\get_records.php on line 38

命令不同步；您现在不能运行此命令

Commands out of sync; you can't run this command now

与

$countQuery = "SELECT ARTICLE_NO FROM AUCTIONS1 WHERE upper(ARTICLE_NAME) LIKE ?";

if ($numRecords = $con->prepare($countQuery)) {

    $numRecords->bind_param("s", "%".$query."%");

产生

致命错误:无法在第38行的C:\ Program Files \ EasyPHP 3.0 \ www \ prog \ get_records.php中通过引用传递参数2

Fatal error: Cannot pass parameter 2 by reference in C:\Program Files\EasyPHP 3.0\www\prog\get_records.php on line 38

最后

$countQuery = "SELECT ARTICLE_NO FROM AUCTIONS1 WHERE upper(ARTICLE_NAME) LIKE ? ";

if ($numRecords = $con->prepare($countQuery)) {

    $numRecords->bind_param("s", $query);

只会给出:

命令不同步；您现在不能运行此命令

Commands out of sync; you can't run this command now

是否可以将参数用于LIKE策略?

Is it impossible to use a paramter for a LIKE statament?

推荐答案

对于LIKE子句，请使用此:

SELECT ARTICLE_NO FROM AUCTIONS1 WHERE upper(ARTICLE_NAME) LIKE CONCAT('%', ?, '%')

对于表名，将表名作为参数是非常糟糕的做法.

As for the table name, it's an extremely bad practice to have table names as parameters.

如果出于某些原因您仍然需要这样做，则需要在准备查询之前将其嵌入查询文本中.

If for some reason you still need to do it, you'll need to embed it into the query text before preparing the query:

$countQuery = "SELECT ARTICLE_NO FROM $table_name WHERE upper(ARTICLE_NAME) LIKE CONCAT('%', ? ,'%')";
if ($numRecords = $con->prepare($countQuery)) {
    $numRecords->bind_param("s", $brand);
    $numRecords->execute();
    $data = $con->query($countQuery) or die(print_r($con->error));
    $rowcount = mysql_num_rows($data);
    $rows = getRowsByArticleSearch($query, $table, $max);
    $last = ceil($rowcount/$page_rows);
}

这篇关于SQL LIKE查询失败-预备语句中出现致命错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！