MySQL query with multiple AND's

Question

I'm new to MySQL and I'm having problem with a query using PHP. See the three last lines.

$query = "
SELECT post_content, slug, ID
FROM wp_posts

INNER JOIN wp_term_relationships
ON wp_posts.ID = wp_term_relationships.object_id

INNER JOIN wp_terms
ON wp_term_relationships.term_taxonomy_id = wp_terms.term_id

INNER JOIN wp_postmeta
ON wp_posts.ID = wp_postmeta.post_id

WHERE post_status = 'publish' AND slug = 'a-slug' AND meta_key <> 'include' AND meta_key <> 'exkludera'
OR post_status = 'publish' AND slug = 'a-slug' AND meta_key = 'include' AND meta_value = '72'
OR post_status = 'publish' AND slug = 'a-slug' AND meta_key = 'exclude' AND meta_value <> '72'
";

With the three last lines I want to say:

Row 1) Select query if it: Is publish, Has a-slug and doesnt have meta_key "include" or "exclude"

Row 2) Select query if it: Is publish, Has a-slug, have meta_key "include" and meta_value is '72'

Row 3) Select query if it: Is publish, Has a-slug, have meta_key "exclude" and meta_value is not '72'

But it gives me back double or tripple of each select. It should only be selected if it matchhes one of these three senarios. What am I doing wrong?

jeroen · Accepted Answer · 2011-11-25 22:04:17Z

4

You need to use brackets:

WHERE (post_status = 'publish' AND slug = 'a-slug' AND meta_key <> 'include' AND meta_key <> 'exkludera')
OR (post_status = 'publish' AND slug = 'a-slug' AND meta_key = 'include' AND meta_value = '72')
OR (post_status = 'publish' AND slug = 'a-slug' AND meta_key = 'exclude' AND meta_value <> '72')

Just mixing OR and AND statements like you do in your example evaluates to true for all cases where post_status = 'publish'.

By the way, I am assuming that Row 1) etc. refers to the lines in your code, not the rows you want returned from the database...

answered Nov 25, 2011 at 22:04

jeroen

91.8k22 gold badges117 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Hakan Over a year ago

I have tried that, but no difference. Yes with "row 1 row 2 and row 3" I mean the last three rows in my example. Please have a look again.

jeroen Over a year ago

Perhaps there is an additional problem in your join's; do you get duplicate rows?

Hakan Over a year ago

Yes there are two or three duplicate rows for me. Will try it without joins.

jeroen Over a year ago

@Hakan I'm not that familiar with Wordpress so I don't know where all the fields come from, but adding (left...) joins one by one will probably show you where the problem is.

Daren Chandisingh · Accepted Answer · 2011-11-25 22:06:53Z

1

It should help to put parentheses around the separate sets of criteria.

e.g.

$query = "
...
WHERE 
(post_status = 'publish' AND slug = 'a-slug' AND meta_key <> 'include' AND meta_key <> 'exkludera')
OR 
(post_status = 'publish' AND slug = 'a-slug' AND meta_key = 'include' AND meta_value = '72')
OR 
(post_status = 'publish' AND slug = 'a-slug' AND meta_key = 'exclude' AND meta_value <> '72')
";

In fact, since some of the criteria are common, you could rewrite it like this:

$query = "
...
WHERE 
post_status = 'publish' AND slug = 'a-slug' AND
(
 (meta_key <> 'include' AND meta_key <> 'exkludera')
 OR 
 (meta_key = 'include' AND meta_value = '72')
 OR 
 (meta_key = 'exclude' AND meta_value <> '72')
)
";

You can also use DISTINCT to ensure only one copy of each record is returned, if necessary.

SELECT DISTINCT post_content, slug, ID

answered Nov 25, 2011 at 22:06

Daren Chandisingh

2,1631 gold badge13 silver badges17 bronze badges

1 Comment

Hakan Over a year ago

Didn't slove my problem, but very good to know! Thanks! I have located the problem, the last three row works witout the joins. So it must be something about them...

JoshG · Accepted Answer · 2011-11-25 22:08:38Z

0

I've had this same issue.

I believe the problem is that you aren't wrapping your OR when combining them with the ANDs you are using. When using OR and AND together they must be wrapped in parens "()" or you will have a conflict.

Here's a link showing example and well written descriptions of how this works: http://www.w3schools.com/sql/sql_and_or.asp

answered Nov 25, 2011 at 22:08

JoshG

9513 gold badges14 silver badges20 bronze badges

Collectives™ on Stack Overflow

MySQL query with multiple AND's

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related