reversing WHERE IN clause?

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
virgil
Forum Commoner
Posts: 59
Joined: Thu Jun 13, 2002 11:43 pm
Location: New York, U.S.

reversing WHERE IN clause?

Post 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
DaiWelsh
Forum Commoner
Posts: 36
Joined: Wed Jan 08, 2003 9:39 am
Location: Derbyshire, UK

Post 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
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
virgil
Forum Commoner
Posts: 59
Joined: Thu Jun 13, 2002 11:43 pm
Location: New York, U.S.

Post 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
User avatar
hob_goblin
Forum Regular
Posts: 978
Joined: Sun Apr 28, 2002 9:53 pm
Contact:

Post 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.
virgil
Forum Commoner
Posts: 59
Joined: Thu Jun 13, 2002 11:43 pm
Location: New York, U.S.

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