Page 1 of 1

reversing WHERE IN clause?

Posted: Wed Jan 08, 2003 1:40 pm
by virgil
Hi Php's


Quick question,

Ive used this syntax before.......

("number" is a MYSQL Field)

number=11 (already in DB)

($number_string is a page variable)

$number_string="9,10,11,12" ;

Code: Select all

$query = "SELECT * FROM table WHERE number IN ('$number_string')";

$result = mysql_query($query);
           
            $number_exists =mysql_num_rows($result);

that works fine........But I cant seem to reverse it...



("numbers_field" is a MYSQL Field)

numbers_field=9,10,11,12 (already in DB)

($number is a page variable)

$number_var="11";

Code: Select all

$query = "SELECT * FROM table WHERE $number_var IN ('numbers_field') ";

$result = mysql_query($query);
           
            $number_exists =mysql_num_rows($result);
Ive tried alot of variations but $number_exists always=0

Cant seem to find the IN clause in the manuel.

Any Advice?


Thanks Virgil

Posted: Thu Jan 09, 2003 2:35 am
by DaiWelsh
The only way I know of to do this is

WHERE numbers_field LIKE '$number_var,%'
OR numbers_field LIKE '%,$number_var'
OR numbers_field LIKE '%,$number_var,%'
OR numbers_field = '$number_var'

which is a bit of a stinker, hence why it is not a good idea to store multiple values in one column.

You may be able to do something like

WHERE ',' + numbers_field + ',' LIKE '%,$number_var,%'

but I am not in a position to test it right now.

Regards,

Dai

Posted: Thu Jan 09, 2003 2:38 am
by twigletmac
The manual entry for the IN clause:
http://www.mysql.com/doc/en/Comparison_ ... ml#IDX1133

I came across the same problem a while ago, although I can't remember what I did to get around it (sorry) I do know that I couldn't use the syntax you presented because it tested the number against the string '9,10,11,12' instead of testing it against the numbers 9, 10, 11 and 12.

Mac

Posted: Thu Jan 09, 2003 7:59 pm
by virgil
Hi Php's :)

Still cant seem to get it.
There must be a way...

These were in the manuel, but I'm not even sure you can use them this way.

I tried..

Code: Select all

SELECT * FROM table WHERE FIND_IN_SET 
('$number_var',numbers_field)>0;
And...

$

Code: Select all

query = "SELECT * FROM table WHERE LOCATE
($eventnum, unit_eventnum)";



But got syntax errors on both and couldn't work them out ...



Any other inspirations?

Really appreciate the help.

Thanks Virgil

Posted: Fri Jan 10, 2003 12:14 am
by hob_goblin
try

Code: Select all

$query = "SELECT * FROM table WHERE $number_var IN (numbers_field) ";
Just a wild guess, but It might work. Don't have a box to test it on though.

Posted: Fri Jan 10, 2003 6:59 am
by virgil
Sorry Hob, tried it ...no good...

But I finally got it!

Code: Select all

query = "SELECT * FROM table WHERE LOCATE 
('$number', numbers_field)";

:D :D :D

Thanks All :wink: