MYSQL variables in php query

Question

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.

MrCode · Accepted Answer · 2013-01-23 18:51:02Z

21

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.

answered Jan 23, 2013 at 18:51

MrCode

64.6k10 gold badges95 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David.LPower Over a year ago

Thank you for your help MrCode. It is much appreciated. I will update my code to take this into account.

Paul Over a year ago

Please 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

GeistZero · Accepted Answer · 2015-11-23 09:55:12Z

5

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);

edited Nov 23, 2015 at 9:55

answered Nov 23, 2015 at 9:24

GeistZero

512 silver badges3 bronze badges

Comments

echo_Me · Accepted Answer · 2013-04-01 12:01:33Z

2

put variable like this. i guess it should work.

 mysql_query("SELECT @i:=0");
 mysql_query("UPDATE table_name SET id = @i:=@i+1");

edited Apr 1, 2013 at 12:01

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

answered Jan 23, 2013 at 18:47

Raul

6071 gold badge6 silver badges17 bronze badges

1 Comment

Ariel Over a year ago

I'm sorry, I misread it. I've deleted my comment. Delete yours too and we can pretend I never said anything. Sorry.

Collectives™ on Stack Overflow

MYSQL variables in php query

3 Answers 3

2 Comments

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Related