MYSQL variables in php query
Solution 1
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); // <-- without the SET ... 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.
Solution 2
There is also possible to use multi_query
method instead of query
of MySQLi:
$query = "SET @running_sum = 0; SELECT ...";
$db_link->multi_query($query);
Solution 3
put variable like this. i guess it should work.
mysql_query("SELECT @i:=0");
mysql_query("UPDATE table_name SET id = @i:=@i+1");
David.LPower
Updated on July 22, 2022Comments
-
David.LPower almost 2 years
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. -
David.LPower over 11 yearsThank you for your help MrCode. It is much appreciated. I will update my code to take this into account.
-
Ariel almost 11 yearsI'm sorry, I misread it. I've deleted my comment. Delete yours too and we can pretend I never said anything. Sorry.
-
Paul over 7 yearsPlease also be aware of the security consideration when using mysqli_multi_query() to send multiple queries at once. With mysqli_multi_query() you open yourself to injected queries like "; DROP DATABASE". php.net/manual/en/mysqli.quickstart.multiple-statement.php