mysql 查询在 phpmyadmin 中运行时有效，但在 php 中运行时返回错误 [英] mysql query works when ran in phpmyadmin, but returns an error when ran in php

问题描述

使用以下函数，我尝试将数据更新到数据库.该查询在 phpmyadmin 中直接运行时运行良好，但在 php 中运行时产生错误.

Using the following function, I am attempting to update data to a database. The query works well when directly ran in phpmyadmin, but produces an error when running from php.

这是函数

function edit_row($table, $columns, $where){
        db_connect();
        $query = "BEGIN WORK; SET AUTOCOMMIT=0; UPDATE $table SET $columns WHERE $where;    COMMIT;";
        echo $query; //this is to control for typing errors when testing in phpmyadmin
        mysql_query($query) or die ("Entry could not be made, " . mysql_error());
        db_close();
}

运行这个命令:

edit_row("hello","test = 'some other string'", "test = 'somestring'");

回声输出:

BEGIN WORK; SET AUTOCOMMIT=0; UPDATE hello SET test = 'some other string' WHERE test = 'some string'; COMMIT;

产生的错误:

Entry could not be made, 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 'SET AUTOCOMMIT=0; UPDATE hello SET test = 'some other string' WHERE test = 'so' at line 2

它似乎切断了查询字符串的最后一位，但不确定这是否是 die() 方法的怪癖

It appears to cut off the last bit of the query string, but not sure if this is a quirk of the die() method

推荐答案

您不能在对 mysql_query 的一次调用中执行多个查询 - 您需要将您的查询分解为四个单独的调用.

You cannot execute multiple queries in a single call to mysql_query - you need to break-up your query into four separate calls.

根据 PHP mysql_query 文档:

mysql_query() 发送一个唯一的查询(不支持多个查询)到当前活动的数据库与指定的链接标识符.

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

这在 phpMyAdmin 中起作用的原因是因为 phpMyAdmin 实际上在解构输入的语句后在后台执行四个单独的查询.

The reason this works in phpMyAdmin, is because phpMyAdmin is in fact carrying out four separate queries in the background after de-constructing the statement(s) entered.

这篇关于mysql 查询在 phpmyadmin 中运行时有效，但在 php 中运行时返回错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！