Page 1 of 1

Inserting variable with parenthesis in sql query

Posted: Tue Dec 13, 2005 8:35 am
by mliungman
Hi!

A short question: I want to insert a variable with parenthesis into my sql query, without it being interpreted as a function. This since I retrieve the variable from a database and the variable is a parameter with the corresponding unit.

Like this

Code: Select all

$rs= $db_connection->execute("SELECT * FROM table_a WHERE ". $Parameter .">0");
For example, $Parameter might equal to "Depth (m)", and would thus be considered as a function called Depth. I want it to be a complete string, not a function.

Thanks

Re: Inserting variable with parenthesis in sql query

Posted: Tue Dec 13, 2005 9:09 am
by Weirdan
Like this

Code: Select all

$rs= $db_connection->execute("SELECT * FROM table_a WHERE `". $Parameter ."`>0");

Posted: Tue Dec 13, 2005 9:15 am
by mliungman
I should have been clearer...

Actually, that's how I would write the query if $Parameter was an ordinary string. But in this particular case $Parameter is the column name in the database table that I'm querying. Whith single quotes I get the type mismatch error. Without single quotes I get an error saying "Undefined function 'Depth' in expression" (translated from the swedish error message). I have assumed that the string "Depth (m)" that I insert into the query is interpreted as a function. Which brings me back to my problem.

Martin

Posted: Tue Dec 13, 2005 9:52 am
by Burrito
set the query string to a variable then echo it out so we can see what the 'real' query looks like:

Code: Select all

$query = "SELECT * FROM table_a WHERE `". $Parameter ."`>0";
echo $query;
$rs= $db_connection->execute($query);

Posted: Tue Dec 13, 2005 10:04 am
by mliungman
This when using single quotes and $Parameter = Alkalinitet (mekv/l):

Code: Select all

SELECT * FROM data WHERE `Alkalinitet (mekv/l)`>0
Without single quotes I get the undefined function error.

By the way, I edited my second post a bit.

Thanks

Posted: Tue Dec 13, 2005 10:16 am
by Burrito
well *hopefully* from that you should be able to see what the problem is....

if not, let us know and we can point it out 8O

Posted: Tue Dec 13, 2005 11:35 am
by mliungman
I'm sorry but no. My problem is that I need the query to search the "Alkalinitet (mekv/l)" column in the table. The query, however, interprets my column name as a function if I omit the single quotes, and as a simple string if I use single quotes.

*Apparently* I'm not a php wizard, and do not claim to be one either. You are most welcome to point me in the right direction.

Martin

Posted: Tue Dec 13, 2005 12:05 pm
by ryanlwh
use backticks ` (not single quote ') to have mysql parse a column name correctly. All in all, avoid special characters in a column name.

Posted: Tue Dec 13, 2005 12:12 pm
by Chris Corbyn
Escape the parentheses in the column name with backslashes :?

Code: Select all

SELECT * FROM data WHERE `Alkalinitet \(mekv/l\)`>0
You can do that easily with a simple preg_replace() on the $parameter

Code: Select all

$parameter = preg_replace('/(\(|\))/', '\\$1', $parameter);
;)

Posted: Tue Dec 13, 2005 1:58 pm
by onion2k
Change the column name to something that's not so annoying.

Posted: Thu Dec 15, 2005 3:44 am
by mliungman
Finally!

Thank you for several solutions addressing the actual problem. Backticks solved the problem the easiest way. Of course, changing the column names would too, but then I would have to create a new table with the units, and then rewrite my script to search that table also. It seemed easier to have the units together with the actual parameter, in the column name. It is also easier to add a new parameter to only one table.

Thanks for the help, everone.

P.S. Maybe Weirdan actually helped me in the first reply, but I never saw the backticks. I used single quotes all the time...