As much as I hate admitting to it at this point...Neil did, at least, have something right. You'll want to be cautious of how you group your tests. If you copy the variable for both tests, you may get: > WHERE name LIKE "ABC" AND listing_type = 1 OR listing_type = 2 But, SQL can only compare 2 tests, separated by AND/OR/etc., at a time. So, it'll assume some grouping because of the order: > WHERE (name LIKE "ABC" AND listing_type = 1) OR listing_type = 2 You'll get a lot more rows than you expect because of "listing_type = 2" being on its own. So, you'll want to group the listing_type tests yourself: > WHERE name LIKE "ABC" AND (listing_type = 1 OR listing_type = 2) Also, reversing the order should work (since grouping is left-to-right): > WHERE listing_type = 1 OR listing_type = 2 AND name LIKE "ABC" This'll be read as: > WHERE (listing_type = 1 OR listing_type = 2) AND name LIKE "ABC" Now, if you use IN, the listing_type tests will already be grouped. > WHERE name LIKE "ABC" AND listing_type IN (1, 2) - Jon L. On Mon, Mar 10, 2008 at 4:02 PM, Jon L. <jonllmsed@xxxxxxxxx> wrote: > > Now I've spoon fed you > > Damn, Neil...you're a d-bag. > Next time you go to offer "help," it may assist you to know that most > people don't respond well to being talked down to. > > (obviously, I'm contradicting my own advice, here; but, I doubt you'll > learn from it, anyways) > > > Now, Ron... > I'm hoping the following can add a few extra tidbits for you over what > Matt already explained. > > The initial error, I think, was an assumption that natural or spoken > language translates directly into SQL. > Or, that you'd say: "does variable equal 1 or 2." > Makes sense to us, right? ;) > > Now, I could be wrong about that. > But, regardless, SQL read your query as: "(does variable equal 1) or (is 2 > non-zero)." > And, off course, 2 isn't 0...so that'll always return true; making the > 'OR' return true as well. > > You'll have to associate the variable with each possible match: "WHERE > (variable = 1) OR (variable = 2)" > Or simplify it, as Matt suggested, using IN: "WHERE variable IN (1, 2)" > > Did that help any? > > - Jon L. > > > On Mon, Mar 10, 2008 at 3:40 PM, Neil Smith [MVP, Digital media] < > php@xxxxxxxxxxxxxxxxxxxxxxxx> wrote: > > > At 19:34 09/03/2008, you wrote: > > >From: Ron Piggott <ron.php@xxxxxxxxxxxxxxxxxx> > > >Date: Sun, 09 Mar 2008 15:34:05 -0400 > > >Message-Id: <1205091245.16466.1.camel@xxxxxxxxxxxxxxxxxxxxx> > > > > > >What is the correct syntax for where the results may be 1 or 2? What > > >have I done wrong? > > > > > >SELECT * FROM table WHERE name LIKE "ABC" AND listing_type = 1 or 2 > > > > > > C'mon Ron, this is basic SQL. The query you provided would have given > > all rows where name was like 'abc', listing type was 1, then returned > > all rows because `OR 2` results in a value of 'true' which matches > > everything (you could also have written OR 2 = 2 with the same effect) > > > > SELECT * FROM table WHERE name LIKE "ABC" AND (listing_type = 1 OR > > listing_type = 2); > > > > SELECT * FROM table WHERE name LIKE "ABC" AND listing_type IN (1, 2); > > > > > > > > Now I've spoon fed you, please read the manual on "Operator > > Precedence" which explains how to combine stuff like this so you > > don't get the "wrong" result > > http://dev.mysql.com/doc/refman/5.0/en/operator-precedence.html : "To > > override this order and group terms explicitly, use parentheses" > > (first query above) > > > > > > > > Cheers - Neil > > > > > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > >
