Ad:

MySQL Discussion Thread
Unsolved
Views: 4965

Oct 13th, 2006

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

Expand Post »

why does

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

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

Similar Threads

Purpose of Pointers? in C++
Object Orientated C+++.net Calculator! in C++
pass the value to the text box in JavaScript / DHTML / AJAX
98% done in C++
muliplying without using a * operator! in C++

kyleknapp

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

15 posts
since Aug 2006

Permalink

Oct 13th, 2006

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

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

kyleknapp

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

15 posts
since Aug 2006

Permalink

Oct 13th, 2006

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

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

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.

sn4rf3r

Reputation Points: 10

Solved Threads: 2

Junior Poster

Offline

133 posts
since Sep 2006

Permalink

Oct 13th, 2006

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

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

kyleknapp

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

15 posts
since Aug 2006

Permalink

Oct 13th, 2006

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

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

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

sn4rf3r

Reputation Points: 10

Solved Threads: 2

Junior Poster

Offline

133 posts
since Sep 2006

Permalink

Oct 13th, 2006

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

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

kyleknapp

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

15 posts
since Aug 2006

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: Extracting data from large SINGLE-table database to MULT-table relational database

Next Thread in MySQL Forum Timeline: I NEED HELP PLEASE:Warning: mysql_num_rows():

DaniWeb Message

Cancel Changes

User Name
Password