SQL之类的语句问题 [英] SQL like statement problems

问题描述

我在通过mysqli执行准备好的语句时遇到问题.

I am having problems executing a prepared statement via mysqli.

首先，我使Command出现同步错误.我正在存储结果并关闭连接，并且已经停止收到此错误，因此希望该问题已停止.

First I was getting Command out of sync errors. I am storing the result and closing the connection, and I have stopped getting this error, so hopefully the problem has stopped.

但是，我的sql语法错误中的错误又出现了，该错误在命令不同步时可以正常工作.这是我当前的代码:

However, the error in my sql syntax error, which was working fine while the commands were not in sync, has reappeared. Here is my current code:

我尝试了多种方法来更正此snytax错误，从使用CONCAT(由于失败而被注释掉)到在绑定之前为变量分配％符号等.无济于事.

I have tried many different approaches to correct this snytax error, from Using CONCAT, which is commented out as it failed, from assigning % signs to the variable before binding etc..nothing works.

尝试使用:

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

导致通过引用传递错误.

Results in an error to pass by reference.

<?php
$con = mysqli_connect("localhost", "blah", "blah", "blah");
if (!$con) {
    echo "Can't connect to MySQL Server. Errorcode: %s\n". mysqli_connect_error();
    exit;
}
$con->query("SET NAMES 'utf8'");
$brand = "o";
$brand = "% ".$brand." %";
echo "\n".$brand;
$countQuery = "SELECT ARTICLE_NO FROM AUCTIONS WHERE upper(ARTICLE_NAME) LIKE ?";
//CONCAT('%', ?, '%')";
echo "\ntest";
if ($numRecords = $con->prepare($countQuery)) {
    $numRecords->bind_param("s",  $brand);
    echo "\ntest bind";
    $numRecords->execute();
    echo "\ntest exec";
    $numRecords->store_result();
    $data = $con->query($countQuery) or die(print_r($con->error));
    $rowcount = $data->num_rows;
    $numRecords->free_result();
    $numRecords->close();
    echo "/ntest before rows";
    $rows = getRowsByArticleSearch("test", "Auctions", " ");
    $last = ceil($rowcount/$page_rows);
} else {
    print_r($con->error);
}
foreach ($rows as $row) {
    $pk = $row['ARTICLE_NO'];
    echo '<tr>' . "\n";
    echo '<td><a href="#" onclick="updateByPk(\'Layer2\', \'' . $pk . '\')">'.$row['USERNAME'].'</a></td>' . "\n";
    echo '<td><a href="#" onclick="updateByPk(\'Layer2\', \'' . $pk . '\')">'.$row['shortDate'].'</a></td>' . "\n";
    echo '<td><a href="#" onclick="deleterec(\'Layer2\', \'' . $pk . '\')">DELETE RECORD</a></td>' . "\n";
    echo '</tr>' . "\n";
}
function getRowsByArticleSearch($searchString, $table, $max) {
    $con = mysqli_connect("localhost", "blah", "blah", "blah");
    //global $con;
    $recordsQuery = "SELECT ARTICLE_NO, USERNAME, ACCESSSTARTS, ARTICLE_NAME, date_format(str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), '%d %m %Y' ) AS shortDate FROM $table WHERE upper(ARTICLE_NAME) LIKE '%?%' ORDER BY str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s')" . $max;
    if ($getRecords = $con->prepare($recordsQuery)) {
        $getRecords->bind_param("s", $searchString);
        $getRecords->execute();
        $getRecords->bind_result($ARTICLE_NO, $USERNAME, $ACCESSSTARTS, $ARTICLE_NAME, $shortDate);
        while ($getRecords->fetch()) {
            $result = $con->query($recordsQuery);
            $rows = array();
            while($row = $result->fetch_assoc()) {
                $rows[] = $row;
            }
            return $rows;
        }
    }
}

确切的错误是:

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 '?' at line 11

第11行是定义$ countQuery的行.

Line 11 is the line defining $countQuery.

如您所见，品牌被赋予"o"；

as you can see, brand is assined as "o";

因此，SQL语句应为

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

当我手动将其插入时可以正常工作.

Which works fine when I put it in manually.

推荐答案

mysqli_stmt::bind_param 只能绑定一个特定的变量，而不是一个表达式.所提供的变量是通过引用而不是通过值传递给绑定"的，这意味着底层SQL在执行命令时(而不是在绑定时)获得变量具有的任何值.

mysqli_stmt::bind_param can only bind a specific variable, not an expression. The supplied variable is passed to 'bind' by reference, and not by value, meaning that the underlying SQL gets whatever value that variable has at the time the command is executed, not when it's bound.

使用:

WHERE field LIKE CONCAT('%', ?, '%")

或这样做:

$brand = '%' . $brand . '%'

在执行命令之前立即

您不能做的是:

WHERE field LIKE '%?%

因为?绑定变量必须对应于单个字符串或数字值，而不是子字符串(或字段名).

because the ? bound variable must correspond to a single string or numeric value, not to a substring (or field name).

编辑，在这种情况下，您真正​​的问题似乎是混淆了准备好的语句(如 mysqli_stmt::execute() )，带有简单的旧查询(已完成)与 mysqli::query() ).您还应该直接从数据库服务器请求行数，而不是提取数据并使用num_rows:

EDIT in this case, your real problem appears to be mixing up prepared statements (as supported by mysqli::prepare and mysqli_stmt::execute()) with plain old queries (as done with mysqli::query()). You should also just ask for the number of rows directly from the DB server, rather than pull the data and use num_rows:

$countQuery = "SELECT COUNT(ARTICLE_NO) FROM AUCTIONS WHERE upper(ARTICLE_NAME) LIKE ?";
if ($numRecords = $con->prepare($countQuery)) {
    $numRecords->bind_param("s",  $brand);
    $numRecords->execute();
    $numRecords->bind_result($num_rows);
    $numRecords->fetch();
    $numRecords->free_result();
    $numRecords->close();
    $last = ceil($rowcount/$page_rows);
} else {
    print_r($con->error);
}

这篇关于SQL之类的语句问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！