php - mysql_query表示SQL语法错误，但相同的查询在mysql客户端中正常运行

当我尝试发送以下查询时：

SET @attempts=0;
SELECT mt1.TimeStamp, mt1.ReadAttempts - @attempts AS delta,
       @attempts := mt1.ReadAttempts ReadAttempts
FROM OneWireStats mt1
WHERE SensorIndex=1 ORDER BY mt1.TimeStamp;

要在表OneWireStats中的行之间获取delta值，我收到以下错误：
查询失败，出现错误：SQL语法有错误；请检查
对应于右侧MySQL服务器版本的手册
“SELECT mt1.TimeStamp，mt1.ReadAttempts-@attempts”附近要使用的语法
作为delta，@attempts:=mt1.Re'在第1行Query=SET@attempts=0；
选择mt1.TimeStamp，mt1.ReadAttempts-@attempts AS delta，@attempts
：=mt1.readattems从OneWireStats mt1读取尝试，其中
SensorIndex=1 ORDER BY mt1.TimeStamp；，查询号：0
当我将上面打印出的错误中的查询粘贴到mysql客户端时，它可以正常工作。

mysql>  SET @attempts=0; SELECT mt1.TimeStamp, mt1.ReadAttempts - @attempts AS delta, @attempts := mt1.ReadAttempts ReadAttempts FROM OneWireStats mt1 WHERE SensorIndex=1 ORDER BY mt1.TimeStamp LIMIT 10; Query OK, 0 rows affected (0.01 sec)

+---------------------+-------+--------------+
| TimeStamp           | delta | ReadAttempts |
+---------------------+-------+--------------+
| 2013-12-23 07:55:01 |  5532 |         5532 |
| 2013-12-23 08:00:00 |   302 |         5834 |
| 2013-12-23 08:05:01 |   302 |         6136 |
| 2013-12-23 08:10:00 |   302 |         6438 |
| 2013-12-23 08:15:00 |   302 |         6740 |
| 2013-12-23 08:20:01 |   302 |         7042 |
| 2013-12-23 08:25:00 |   302 |         7344 |
| 2013-12-23 08:30:01 |   302 |         7646 |
| 2013-12-23 08:35:00 |   302 |         7948 |
| 2013-12-23 08:40:01 |   302 |         8250 |
+---------------------+-------+--------------+
10 rows in set (0.07 sec)

I use the following php code:

$query = mysql_real_escape_string(trim($queryobj[$counter]));

$sqlresult = mysql_query($query) or die("Query failed with error: ".mysql_error() .
             " Query = " . $query . ", Query no: " . $counter);

我不使用任何单引号或双引号在我的查询，所以我还不明白错误打印输出。
很好的提示。

最佳答案

您需要分别运行每个查询，可能是

mysql_query( "SET @attempts=0" );
mysql_query( "SELECT mt1.TimeStamp, mt1.ReadAttempts - @attempts AS delta, @attempts := mt1.ReadAttempts ReadAttempts FROM OneWireStats mt1 WHERE SensorIndex=1 ORDER BY mt1.TimeStamp" );

或者使用mysqli'smulti_query()，比如：

$query  = "SET @attempts=0;";
$query .= "SELECT mt1.TimeStamp, mt1.ReadAttempts - @attempts AS delta, @attempts := mt1.ReadAttempts ReadAttempts FROM OneWireStats mt1 WHERE SensorIndex=1 ORDER BY mt1.TimeStamp";
mysqli_multi_query($connection, $query);
mysqli_next_result($connection);
if ($result = mysqli_store_result($connection)) {
  //while loop here
}