Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MYSQL variables in php query

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.

like image 211
David.LPower Avatar asked Jan 23 '13 18:01

David.LPower


People also ask

How use PHP variable inside MySQL query?

$type = 'testing'; mysql_query("INSERT INTO contents (type, reporter, description) VALUES($type, 'john', 'whatever')");

How pass variable in SQL query in PHP?

php $host='localhost'; $user='root'; $pass=''; $db='ratownictwo'; $nr = $_POST['nzr']; $ndz = $_POST['nd']; $adde = $_POST['add']; echo $nr; $conn=mysqli_connect($host,$user,$pass,$db); $q = 'INSERT INTO `zgloszenia` (ratownicy_id, dyspozytorzy_id, adres, pilne, czas_zgloszenia) VALUES ('$nr','$ndz','$adde', 0, ...

What is $_ in PHP?

Variable names follow the same rules as other labels in PHP. A valid variable name starts with a letter or underscore, followed by any number of letters, numbers, or underscores. So $_ is just an arbitrary variable.

How do I use variables in MySQL?

Mysql also supports the concept of User-defined variables, which allows passing of a value from one statement to another. A user-defined variable in Mysql is written as @var_name where, var_name is the name of the variable and can consist of alphanumeric characters, ., _, and $.


1 Answers

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);
like image 135
GeistZero Avatar answered Oct 15 '22 01:10

GeistZero