How to include a PHP variable inside a MySQL SELECT statement

Question

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?

score 2 · Answer 1 · answered Jun 11 '16 at 20:25

2

When selecting columns you need to use backticks to delimit them:

SELECT `$varPriceCol` FROM ...

You'll also want to be extremely certain that this variable contains only known-good values. Your if chain is a good example of this, but you could do one better by using a lookup array:

$columnMap = array('DEPT1' => 'PriceDEPT1', 'DEPT2' => 'PriceDEPT2');
$columnMapDefault = 'Price';

That minimizes the amount of junk in your if statement if used correctly. When writing code like this try and avoid endless repetition, that's how mistakes are made.

The other thing you can do is SELECT * and just pick the column you want out of the results set.

answered Jun 11 '16 at 20:25

tadman

208,517
23
234
262

Sorry, i forgot to mention that backticks are not working in this case. And i don't know why. – 0xbadc0de Jun 11 '16 at 20:28
1

@0xbadc0de "Not working" is a meaningless phrase. Do you get an error? Does the query return the wrong results? Be more specific, please. Using single or double quotes is just plain wrong, that will return a constant string not a value from the column. – tadman Jun 11 '16 at 20:29
The query does not return anything if using backticks. If i try to print the $r co console after the statement all i get is a "false" printed out. The same case if i try to select non existing column. – 0xbadc0de Jun 11 '16 at 20:37
1

You should have [the documentation to `mysqli` open at all times](http://php.net/manual/en/book.mysqli.php) when writing code like this because that would tell you `false` indicates the query had an error. You should [enable exceptions](http://stackoverflow.com/questions/14578243/turning-query-errors-to-exceptions-in-mysqli) to make these errors show up more clearly. – tadman Jun 11 '16 at 20:39
Also as @amaksr noted, [remember how PHP handles string interpolation](http://php.net/manual/en/language.types.string.php). This is a very important thing to remember. – tadman Jun 11 '16 at 20:43

score 1 · Accepted Answer · answered Jun 11 '16 at 20:38

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

How to include a PHP variable inside a MySQL SELECT statement

2 Answers2