why can't I use the defined values directly in a query?

[ajoo]

Hi all,

 

when i try to use 

const test = 25;

define('best', 40);

in a query directly like 

$query = "SELECT A, B, C,e WHERE sum = best"; 

it given an error like 

 

 

Unknown column 'test' in 'where clause' 

 

 

So is it not possible to use the defined or const values in SQL queries ? Whats the best way to use a constant in a query then ?

 

Thanks all.

 

[benanamen]

You are missing a FROM.

[Jacques1]

How is this supposed to work? You're writing a constant query string which is passed straight to MySQL, and MySQL of course doesn't know your PHP constants.

 

If you want the constant to be resolved, you need to do it in PHP:

$query = "SELECT ... WHERE sum = " . YOUR_CONSTANT;

But this is a bad approach to begin with. Constants should be passed via statement parameters like all other values. The risk of an SQL injection may be low, but you can still screw up your query if the constant happens to contain special characters.

[ajoo]

@Guru Jacques @Benanamen : Thank you loads ! 

[Psycho]

I think your confusion may stem from the fact that PHP has logic that can parse (some) variables in some instances. But, note that even that logic is not perfect. That logic works off of looking for the dollar sign and capturing the characters following the dollar sign up to the first space/termination character. It then checks to see if there is a variable of that name. If so, it replaces the variable with the value - else it leaves it alone. But, that logic can "break" for some variable names such as $foo[1]. In that case you can enclose the variable in curly braces: {$foo[1]}

 

Now, with a constant there is no dollar symbol or other delimiter to use so that code could even be built to parse a constant within a string.

 

Take a look at this page in the manual regarding strings creating using single/double quotes and the heredoc/nowdoc methods. The examples provide an explanation of when variables will be parsed or not and which ones would be parsed.

 

http://php.net/manual/en/language.types.string.php

[ajoo]

Even if I pass the constant or defined value as a statement parameter, I still cannot pass it simple like 

$query = "SELECT A, B, C FROM table WHERE sum = ?"; 

$stmt=$link->prepare($query);
$stmt->bind_param('i',best);

I need a $best and before I can pass a parameter. 

 

Even if I pass the constant or defined value as a statement parameter, I still cannot pass it simple like 

$query = "SELECT A, B, C FROM table WHERE sum = ?"; 

$stmt=$link->prepare($query);
$stmt->bind_param('i',best);

I need a $best and pass that as a parameter.  I guess maybe it's best to not play with constants and defines while dealing with SQL queries. 

 

P.S. Hi Psycho, 

 

Thanks for the response. I'll read that for a clearer understanding. 

Edited May 22, 2017 by ajoo

[Jacques1]

mysqli expects you to pass references to bind_param, so constants or literal values are not possible. You have to copy the value to a variable, then use the variable:

const MY_CONST = 123;            // constants are written in UPPER_CASE

$var = MY_CONST;                 // copy value to variable

$stmt->bind_param('i', $var);    // use $var for parameter

[ajoo]

That's exactly how I used them as you have shown ! 

 

Thanks loads !

Edited May 22, 2017 by ajoo