MySQL Forums
Forum List  »  PHP

Re: Problem with SQL query :(
Posted by: Rick James
Date: January 20, 2014 03:24PM

> > Simplifying your table & column names
> but I would be happy about any advice

I would start by removing the table name from all column names in that table.
It takes a lot of visual effort to parse and realize that those are two different fields.:

> (`id_marketplace_properties_label` = 6 AND `id_marketplace_properties` = 35 )

Notice how Peter's examples were much more concise. That makes it easier to see the ANDs, ORs, and parens.

As for building an arbitrary list of AND'd clauses in PHP, here is a sample of the code I like to write:
http://forums.mysql.com/read.php?52,264473,264806#msg-264806
It builds the pieces in an array ($wheres[]), then adds the ANDs (implode()). So it automatically handles the case where there is only one test to perform. (The "if" takes care of zero tests.)

> ( label = 6 AND properties = 35 OR label = 6 AND properties = 5 ) AND ( label = 7 AND properties = 7 OR label = 7 AND properties = 8 )

is the same as
( label = 6 AND properties IN (35,5) ) AND
( label = 7 AND properties IN (7,8) )

This latter formulation may be simpler to work with.
But both are probably _wrong_??
You can't have both (label=6) AND (label=7).
Did you mean:
( label = 6 AND properties = 35 OR label = 6 AND properties = 5 ) OR ( label = 7 AND properties = 7 OR label = 7 AND properties = 8 )
which is the same as
(
( label = 6 AND properties IN (35,5) ) OR
( label = 7 AND properties IN (7,8) )
)

My PHP code can be done in a nested way if needed -- the outer $wheres would gather everything that is AND'd together at the outer level. Then, a function could gather the $ors[] and implode them to build an inner OR list.

AND and OR are terrible in English. The 'common' usage has them being synonyms in many contexts. But in programming (PHP, SQL, etc), the precise mathematical meaning (AND == simultaneously both; OR == either) is used.

Note that ANDing two filters can only shrink the number of rows in the result; ORing two filters can only increase the number of rows.

Options: ReplyQuote


Subject
Written By
Posted
January 18, 2014 10:39PM
January 19, 2014 12:30PM
January 19, 2014 12:41PM
Re: Problem with SQL query :(
January 20, 2014 03:24PM
January 21, 2014 06:22PM
January 22, 2014 04:19PM
January 23, 2014 12:15AM
January 23, 2014 10:23AM
January 19, 2014 12:33PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.