php mysql in变量,PHP查询中的MYSQL变量

The solution to this problem might be a simple over sight of mine.

I am trying to run a MYSQL query stored as a string in PHP. The query runs fine using DBM tool like Navicat but returns false in my PHP development enviorment. Is there something I've over looked?

SET @running_sum = 0;

SELECT

TID,

SumTotal,

T.`Freight`,

T.`Insurance`,

T.`Discount`,

CONCAT(

'$',

FORMAT(

@running_sum :=@running_sum + SumTotal + T.`Freight` + T.`Insurance` - T.`Discount`,

2

)

) AS 'Running Total'

FROM

(

SELECT

TID,

SUM(Quantity * UnitNetValue) AS SumTotal,

T.`Freight`,

T.`Insurance`,

T.`Discount`

FROM

Transactions T

JOIN `Transactions_Products` P ON T.TransactionID = P.TID

WHERE

(

T.TemplateName = ''

OR T.TemplateName IS NULL

)

AND T. STATUS = 1

GROUP BY

TransactionID

) AS T;

I am executing the query like this;

$result = mysql_query($this->query);

$this->query is a string which holds the above query, as it is displayed to you above.

解决方案

The problem is mysql_query() doesn't support multiple queries. Your SET @running_sum = 0; is considered a separate query and so you'll have to execute that first:

$result1 = mysql_query("SET @running_sum = 0;");

$result2 = mysql_query($this->query); //

From the Manual:

mysql_query() sends a unique query (multiple queries are not supported)

Side note: The mysql_* library is deprecated, it is recommended to upgrade to a modern MySQL library such as PDO or MySQLi.