[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. <[EMAIL PROTECTED]> 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. <[EMAIL PROTECTED]> 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] <
> > [EMAIL PROTECTED]> wrote:
> >
> > > At 19:34 09/03/2008, you wrote:
> > > >From: Ron Piggott <[EMAIL PROTECTED]>
> > > >Date: Sun, 09 Mar 2008 15:34:05 -0400
> > > >Message-Id: <[EMAIL PROTECTED]>
> > > >
> > > >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
> > >
> > >
> >
>

Reply via email to