MySQL

variable in WHERE...LIKE construct doesn't work

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Aug 2006

Posts: 15

Reputation: kyleknapp is an unknown quantity at this point

Solved Threads: 0

kyleknapp

Offline

Newbie Poster

variable in WHERE...LIKE construct doesn't work

Oct 13th, 2006

why does

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
$sql = 'SELECT userid FROM `phplist_user_user_attribute` WHERE `attributeid`= 7 AND `value` LIKE "68104"';
$query = mysql_query($sql);
$sql = 'SELECT userid FROM `phplist_user_user_attribute` WHERE `attributeid`= 7 AND `value` LIKE "68104"';
$query = mysql_query($sql);

find 23 records, but

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
$testvalue="68104";
$sql = 'SELECT userid FROM `phplist_user_user_attribute` WHERE `attributeid`= 7 AND `value` LIKE "$testvalue"';
$query = mysql_query($sql);
$testvalue="68104";
$sql = 'SELECT userid FROM `phplist_user_user_attribute` WHERE `attributeid`= 7 AND `value` LIKE "$testvalue"';
$query = mysql_query($sql);

finds none? Shouldn't they be the same?

Can I not use a variable as a parameter for LIKE? Or is it a data-type issue (e.g. $testvalue is storing as a number rather than a string?)

Looks to me like these should work the same. I'm baffled (but then that's nothing new...)

Thanks,
~kyle

•

Join Date: Aug 2006

Posts: 15

Reputation: kyleknapp is an unknown quantity at this point

Solved Threads: 0

kyleknapp

Offline

Newbie Poster

Re: variable in WHERE...LIKE construct doesn't work

Oct 13th, 2006

got it figured out. Somehow an escape character was getting inserted in my $testvalue (in real use I was reading values from a file, and the linefeed at the end of each record was getting read into the variable, though why it would happen in the above "hardcoded" example beats me)

I solved the problem by replacing
$testvalue = $zlist[$i];
with
$testvalue = trim($zlist[$i]);

that seemed to fix it

•

Join Date: Sep 2006

Posts: 133

Reputation: sn4rf3r is an unknown quantity at this point

Solved Threads: 2

sn4rf3r

Offline

Junior Poster

Re: variable in WHERE...LIKE construct doesn't work

Oct 13th, 2006

you dont seem to be using any wild cards for your query, so you probibly dont even need to use like

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT * FROM table WHERE id LIKE '123%'
select * from table where id like '123%'

also in your example you had your query in double quotes inside of single quotes, this means that you were looking for $testvalue literally and not the variable.

•

Join Date: Aug 2006

Posts: 15

Reputation: kyleknapp is an unknown quantity at this point

Solved Threads: 0

kyleknapp

Offline

Newbie Poster

Re: variable in WHERE...LIKE construct doesn't work

Oct 13th, 2006

That makes sense to me, but I don't think it's true. If I put the variable in single-quotes the script just crashes. It works well now - it's in double-quotes, but the selection is definitely using the value of the variable, not the literal string.

As for the use of "LIKE", some of the zipcodes in the database are 9-digit, the wildcard is necessary to catch them.

Thanks,
~kyle

•

Join Date: Sep 2006

Posts: 133

Reputation: sn4rf3r is an unknown quantity at this point

Solved Threads: 2

sn4rf3r

Offline

Junior Poster

Re: variable in WHERE...LIKE construct doesn't work

Oct 13th, 2006

youre right, I had the two reversed,
single quotes == literal

I didnt see any wildcard in your query, thats why I made that comment.

•

Join Date: Aug 2006

Posts: 15

Reputation: kyleknapp is an unknown quantity at this point

Solved Threads: 0

kyleknapp

Offline

Newbie Poster

Re: variable in WHERE...LIKE construct doesn't work

Oct 13th, 2006

oops, you're right - I think I added that after my last post...

Thanks for all your help - I have a long ways to go, but at least I'm starting to feel like I have a clue...

~kyle