mysql conditional "not_in" based on another value to parse array into query

Question

I have the following array

$exclude = array(1 => array(1,2,3,4), 2 => array(1,2,3,4));

I need to exclude the values inside the inner array when the key validates. I'm not sure if I should be using CASE or just AND and OR.

Here is the start to what I attempted, but I am missing something, as this doesn't work as intended.. I am appending the query to the end of a WHERE clause, so that is the only part I have access to.

foreach($exclude as $blogID => $post_ids) {
    $ids = implode(',', $post_ids);

    if($blogID == 1) {
        $where .= " AND table.BLOG_ID = {$blogID} AND table.ID NOT IN ($ids)";
    } else {
        $where .= " OR table.BLOG_ID = {$blogID} AND table.ID NOT IN ($ids)";
    }
}

MySQL isn't very good at optimizing complex AND/OR combinations like this. It might be better to generate a query for each pair, and combine them with UNION. — Barmar
– Barmar, Commented Jul 31, 2015 at 16:20

Don't Panic · Accepted Answer · 2015-07-31 16:18:11Z

2

I'm not sure without having the rest of the query to experiment with, but it may help to add some parentheses around all the clauses to make sure all the ANDs and ORs interact as intended. Something like this:

if ($exclude) {
    $where .= ' AND ('; // Enclose all the ORs in one set of parentheses
    $or = '';
    foreach($exclude as $blogID => $post_ids) {
        $ids = implode(',', $post_ids);
        // Each individual OR part is enclosed in parentheses
        $where .= "$or(table.BLOG_ID = {$blogID} AND table.ID NOT IN ($ids))";
        $or = ' OR ';
    }
    $where .= ")";
}

answered Jul 31, 2015 at 16:18

Don't Panic

41.9k11 gold badges70 silver badges87 bronze badges

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

Collectives™ on Stack Overflow

mysql conditional "not_in" based on another value to parse array into query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related