[Correction] Change: Also, reversing the order should work (since grouping is left-to-right): To: Also, reversing the order should work (since AND/OR/etc. are read left-to-right): - Jon L. On Mon, Mar 10, 2008 at 4:18 PM, Jon L. <jonllmsed@xxxxxxxxx> wrote: > 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 > > > > > > > > >
