php - PHP 中 SQL 的语法错误

Question

如果列存在，我试图将表的列设置为每一行中的字符串。但是，我收到 SQL 语法错误并且无法查明问题的确切原因:

while($db = mysqli_fetch_array(mysqli_query($conn, "SHOW DATABASES"))){
    $id = $db['id'];

    $sql = /** @lang text */
           "SELECT IF( EXISTS(
                   SELECT DISTINCT `info`
                       FROM INFORMATION_SCHEMA.COLUMNS
                       WHERE COLUMN_NAME IN ('borrower')
                           AND TABLE_SCHEMA = '$db'))
                   BEGIN
                       UPDATE `info`
                           SET `borrower` = '****'
                           WHERE `id` = '$id'
                   END";

    $query = mysqli_query($conn, $sql);

    if (!$query){
        // Deal with error
    }
}

我收到的语法错误:

information_schema
db1
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 ')
BEGIN
UPDATE info
SET borrower = '****'
' at line 5

我知道它一定是非常小的东西，但我已经卡住了一段时间，似乎无法找到我的错误。

Answer 1

为查询尝试这种语法-

 IF EXISTS (SELECT DISTINCT `info`
                   FROM INFORMATION_SCHEMA.COLUMNS
                   WHERE COLUMN_NAME IN ('borrower')
                       AND TABLE_SCHEMA = '$db')) THEN
     UPDATE `info`
                       SET `borrower` = '****'
                       WHERE `id` = '$id'


  END IF;

我在 if 条件的末尾添加了 Then 和 End if 而不是仅 if，并且删除了 select 到。你想选择什么？它只是更新子句。

更新

示例来自 here

you can't use it like a normal query. Control structures like IF or WHILE are only allowed in stored procedures or functions.

MYSQL面板

delimiter $$
create procedure YourIfConditionSP()
begin
  IF EXISTS (SELECT DISTINCT `info`
                   FROM INFORMATION_SCHEMA.COLUMNS
                   WHERE COLUMN_NAME IN ('borrower')
                       AND TABLE_SCHEMA = '$db') THEN
    UPDATE `info`
                       SET `borrower` = '****'
                       WHERE `id` = '$id';


  END IF;
end $$
delimiter ;

而且您必须更改您的 php 代码以调用存储过程。

PHP

//run the stored procedure
$result = mysqli_query($connection, 
         "CALL YourIfConditionSP") or die("Query fail: " . mysqli_error());

How to call a MySQL stored procedure from within PHP code?