Inserting variable with parenthesis in sql query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mliungman
Forum Newbie
Posts: 5
Joined: Tue Dec 13, 2005 8:31 am

Inserting variable with parenthesis in sql query

Post 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
Last edited by mliungman on Tue Dec 13, 2005 11:38 am, edited 2 times in total.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Inserting variable with parenthesis in sql query

Post by Weirdan »

Like this

Code: Select all

$rs= $db_connection->execute("SELECT * FROM table_a WHERE `". $Parameter ."`>0");
mliungman
Forum Newbie
Posts: 5
Joined: Tue Dec 13, 2005 8:31 am

Post 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
Last edited by mliungman on Tue Dec 13, 2005 9:53 am, edited 1 time in total.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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);
mliungman
Forum Newbie
Posts: 5
Joined: Tue Dec 13, 2005 8:31 am

Post 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
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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
mliungman
Forum Newbie
Posts: 5
Joined: Tue Dec 13, 2005 8:31 am

Post 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
ryanlwh
Forum Commoner
Posts: 84
Joined: Wed Sep 14, 2005 1:29 pm

Post 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.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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);
;)
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

Change the column name to something that's not so annoying.
mliungman
Forum Newbie
Posts: 5
Joined: Tue Dec 13, 2005 8:31 am

Post 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...
Post Reply