Board index   FAQ   Search  
Register  Login
Board index php forum :: Database Other Database Engines

Building a SQL query with LIKE and 's

Do you have questions regarding other database enginges (not MySQL) -- ask here!

Moderators: macek, egami, gesf

Building a SQL query with LIKE and 's

Postby meiyu » Tue Oct 25, 2011 8:14 pm

Hello, I'm trying to build a SQL query for odbc_exec that includes a LIKE clause with wildcards (ie, '%a'). The query never returns results, but if I use a query for a record value (ie, where FIELD = 15678) this works as expected. I've tried to use the addslashes() function and tried using `s instead of 's.
How can I do this? Thanks!
Mei
meiyu
New php-forum User
New php-forum User
 
Posts: 4
Joined: Tue Oct 25, 2011 8:07 pm

Re: Building a SQL query with LIKE and 's

Postby egami » Wed Oct 26, 2011 4:40 am

SELECT * FROM table WHERE field LIKE '%s%'
User avatar
egami
php-forum GURU
php-forum GURU
 
Posts: 2197
Joined: Wed Oct 06, 2010 11:19 am
Location: Happy Valley, UT

Re: Building a SQL query with LIKE and 's

Postby meiyu » Wed Oct 26, 2011 5:25 am

Thanks egami. I guess I forgot to mention that I'm not a complete idiot! :)
I can issue the SQL query within all the normal interfaces as you wrote without any problem, but how do I escape out the 's in the like clause to put into a query string for use in odbc_exec? For example, odbc_exec( $conn, "SELECT * FROM table WHERE field LIKE '%s%'" ) doesn't work. Neither does odbc_exec( $conn, addslashes("SELECT * FROM table WHERE field LIKE '%s%'") ). Replacing the 's with `s also has no effect.
The query string is built dynamically using variable values during page execution.
Mei
meiyu
New php-forum User
New php-forum User
 
Posts: 4
Joined: Tue Oct 25, 2011 8:07 pm

Re: Building a SQL query with LIKE and 's

Postby Nullsig » Wed Oct 26, 2011 9:43 am

Have you tried echo'ing out the dynamically produced query and executing it on the SQL server (through some method other than utilizing your PHP server)

Sometimes actually reading through the query once it is completely generated helps ascertain what is incorrect about it. From your description (and no knowledge about the content or structure of your database) your query looks correct and the type of slashes should not be causing the problem.

All of this is assuming that you are, as you say, not a complete idiot.
User avatar
Nullsig
php-forum Fan User
php-forum Fan User
 
Posts: 981
Joined: Thu Feb 17, 2011 6:52 am
Location: Racine, WI

Re: Building a SQL query with LIKE and 's

Postby egami » Wed Oct 26, 2011 11:57 am

After smoking a huge blunt, and taking a couple of vicadine, I think I know what the issue is.

IF your variable that you're doing the search on as a tick or a back-tick in the variable, that will end the "string" to search for, but that leaves one open tick left, and then the script bombs.

$variable = addslashes($variable);

before your query and see if that makes the difference.
User avatar
egami
php-forum GURU
php-forum GURU
 
Posts: 2197
Joined: Wed Oct 06, 2010 11:19 am
Location: Happy Valley, UT

Re: Building a SQL query with LIKE and 's

Postby meiyu » Wed Oct 26, 2011 4:20 pm

I prefer opium myself :) but the effect is much the same!
I had tried addslashes() as part of the odbc_exec() function call and also as part of the assignment statement for the variable. Neither had any effect.
A dynamic query that doesn't use the LIKE (which necessitates the 's) works as expected.
Echoing the variable ($sql) and using the result within Management Studio, minus the \s (SELECT * from dbname WHERE field LIKE 'a%'), works as expected. The echoed variable value was SELECT * FROM dbname WHERE field LIKE \'a%\'
I would pull my hair out but they may not recognize me at the opium den and I gots to have some fun! :)
meiyu
New php-forum User
New php-forum User
 
Posts: 4
Joined: Tue Oct 25, 2011 8:07 pm

Re: Building a SQL query with LIKE and 's

Postby Nullsig » Thu Oct 27, 2011 7:49 am

So just to clarify...

The query shows up as:
Code: Select all
SELECT * FROM dbname WHERE field LIKE \'a%\'


When you use addslashes OR does it happen when you don't use addslashes.

If the latter is the case it seems like your server utilizes magic quotes or something of the sort. In which case you would need to use "stripslashes()" and it should work.
User avatar
Nullsig
php-forum Fan User
php-forum Fan User
 
Posts: 981
Joined: Thu Feb 17, 2011 6:52 am
Location: Racine, WI

Re: Building a SQL query with LIKE and 's

Postby meiyu » Thu Oct 27, 2011 10:11 am

Thanks Nullsig! stripslashes() worked.
FYI, using addslashes() OR not using addslashes() didn't make any difference. The only thing that worked was stripslashes().
Thanks again!
BTW, MagicQuotes is not enabled on the server for this PHP install. There may be something else loaded elsewhere, but it's not in the PHP install.
meiyu
New php-forum User
New php-forum User
 
Posts: 4
Joined: Tue Oct 25, 2011 8:07 pm

Re: Building a SQL query with LIKE and 's

Postby ashksngh » Fri Feb 17, 2012 12:04 am

use* '%s%' for building SQL query.
ashksngh
New php-forum User
New php-forum User
 
Posts: 3
Joined: Thu Feb 16, 2012 12:21 am


Return to Other Database Engines

Who is online

Users browsing this forum: No registered users and 3 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.