0xbadc0de 0xbadc0de - 5 months ago 18
MySQL Question

How to include a PHP variable inside a MySQL SELECT statement

I got a not solvable problem with a SELECT statement in a PHP script.
This Query is doing it's job, however i need to expand it too get the values from a particular column.

`

$r = DBi::$conn->query('
SELECT
a.pKey,
a.Name,
a.`Pic-Name`,
a.GTIN,
a.Type,
a.Avail,
(SELECT Price FROM preise WHERE Art_pKey = a.pKey ORDER BY From_date DESC LIMIT 1) Price,
(AVG((b.Preice / b.Art_Num) * -1.00)) Mid_price,
a.Created
FROM art a LEFT
JOIN kasse b ON a.pKey = b.Art_pKey
WHERE Aktiv = "Y" AND Avail = "Y" AND Visible = "Y"
GROUP BY a.pKey
ORDER BY Avail DESC, Name ASC
') or trigger_error('Query Failed! SQL: ' . $r . ' - Error: ' . mysqli_error(DBi::$conn), E_USER_ERROR);


`

Again, this query is doing its job. But now i'm trying to replace the
SELECT Price FROM...
part with a variable.

I put this prior the query:

`

if ($_SESSION['user']['Organisation'] == 'DEPT1'){
$varPriceCol = 'PriceDEPT1';
}
elseif ($_SESSION['user']['Organisation'] == 'DEPT2'){
$varPriceCol = 'PriceDEPT2';
}
else{
$varPriceCol = 'Price';`


But as soon, as i'm doing it this way:
(SELECT "$varPriceCol" FROM preise WHERE Art_pKey = a.pKey ORDER BY From_date DESC LIMIT 1) Price
the query stops working. If i place the "PriceDEPT1" or "PriceDEPT2" or "Price" instead of a variable the query starts working for any of the 3 colum names.

What am I doing wrong?

Answer

Try to change quotes of your query to double quotes, as PHP will not substitute strings with variables if query is single-qouted. Also, inside your query you need to change string literals to single quotes, so it complies with SQL syntax.

Something like that:

$r = DBi::$conn->query(" -- <<< note double quote
        SELECT 
            a.pKey,
            a.Name,
            a.`Pic-Name`,
            a.GTIN,
            a.Type,
            a.Avail,
            (SELECT $varPriceCol FROM preise WHERE Art_pKey = a.pKey ORDER BY From_date DESC LIMIT 1) Price,
            (AVG((b.Preice / b.Art_Num) * -1.00)) Mid_price,
            a.Created
      FROM art a LEFT
        JOIN kasse b ON a.pKey = b.Art_pKey
     WHERE Aktiv = 'Y' AND Avail = 'Y' AND Visible = 'Y' -- <<< note single quotes
     GROUP BY a.pKey
     ORDER BY Avail DESC, Name ASC
    ") or trigger_error('Query Failed! SQL: ' . $r . ' - Error: ' . mysqli_error(DBi::$conn), E_USER_ERROR);