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
???
Ron
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
------=_Part_6913_6271454.1205091530426
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
I like the "IN" keyword:
SELECT * FROM table WHERE name LIKE "ABC" AND listing_type IN ('1','2');
works kinda like PHP's "in_array"
-- matt
On Sun, Mar 9, 2008 at 1:34 PM, Ron Piggott
wrote:
> 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
>
> ???
>
> Ron
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
------=_Part_6913_6271454.1205091530426--
At 19:34 09/03/2008, you wrote:
>From: Ron Piggott
>Date: Sun, 09 Mar 2008 15:34:05 -0400
>Message-Id: <1205091245.16466.1.camel@localhost.localdomain>
>
>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.h tml : "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
------=_Part_7411_3315600.1205182963054
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
> 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@comatose.freeserve.co.uk> wrote:
> At 19:34 09/03/2008, you wrote:
> >From: Ron Piggott
> >Date: Sun, 09 Mar 2008 15:34:05 -0400
> >Message-Id: <1205091245.16466.1.camel@localhost.localdomain>
> >
> >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.h tml : "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
>
>
------=_Part_7411_3315600.1205182963054--
------=_Part_7469_22012278.1205183918771
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
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.
> > 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@comatose.freeserve.co.uk> wrote:
>
> > At 19:34 09/03/2008, you wrote:
> > >From: Ron Piggott
> > >Date: Sun, 09 Mar 2008 15:34:05 -0400
> > >Message-Id: <1205091245.16466.1.camel@localhost.localdomain>
> > >
> > >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.h tml : "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
> >
> >
>
------=_Part_7469_22012278.1205183918771--
------=_Part_7490_5747371.1205184097026
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
[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.
> 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.
>
> > > 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@comatose.freeserve.co.uk> wrote:
> >
> > > At 19:34 09/03/2008, you wrote:
> > > >From: Ron Piggott
> > > >Date: Sun, 09 Mar 2008 15:34:05 -0400
> > > >Message-Id: <1205091245.16466.1.camel@localhost.localdomain>
> > > >
> > > >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.h tml : "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
> > >
> > >
> >
>
------=_Part_7490_5747371.1205184097026--