CODEWITHSUNDEEP
phpmysqlarrayssql-like
brandoncluff
brandoncluff

Reputation: 305

MySQL and PHP - using 'LIKE' AND 'NOT LIKE'

I need some help with using LIKE and NOT LIKE together... I have a query that I pass WHERE clauses through based on what my request variable is from another server. One of the queries is like the following: 

    'CONNECT' =>
    "( detail_head.comment LIKE '%port%'
      or detail_head.comment LIKE '%forward%'
      or detail_head.comment LIKE '%connect%'
      or detail_head.comment LIKE '%router%'
      or detail_head.comment LIKE '%fire%wall%'
      or detail_head.comment LIKE '%sonic%'
      ) AND (
      detail_head.comment NOT LIKE '%report%'
      OR detail_head.comment NOT LIKE '%portal%'
      )",

You can see that I am using LIKE and NOT LIKE. This unfortunately does not work the way I hoped it would. I am guessing it's because I am asking for PORT, but not REPORT, so it gives me the LIKE regardless.

I was wondering what I should do in a case like this. I was thinking about making another query or array that I would use as an 'exclude list'. Where the queries would be LIKE statements, that I could use in my WHERE clause to say 'table_uid NOT IN(LIST OF COMMA SEPARATED UIDs)'.

I have my LIKE statements that I'd like to exclude: 

$exclude_where_clauses = array(
        'CC'            => "(detail_head.comment LIKE '%ccb%') ",
        'CONNECT'       => "(detail_head.comment LIKE '%report%' OR detail_head.comment LIKE '%portal%') ",
        'EO'            => "(detail_head.comment LIKE '%OCU%' AND detail_head.comment LIKE '%KS%' AND detail_head.comment LIKE '%screen%' AND detail_head.comment LIKE '%term%') ",
        'INVENTORY'     => "(detail_head.comment LIKE '%discount%') ",
        'KS'            => "(detail_head.comment LIKE '%panel%' or detail_head.comment LIKE '%PMIX%' or detail_head.comment LIKE '%pmix%') ",
        'OCUS'          => "(detail_head.comment LIKE '%document%') ",
        'SALES'         => "(detail_head.comment LIKE '%point%') ",
        'SECURITY'      => "(detail_head.comment LIKE '%km%') ",
        'TERMS'         => "(detail_head.comment LIKE '%forward%' or detail_head.comment LIKE '%sales%' or detail_head.comment LIKE '%intermittent%' or detail_head.comment LIKE '%print%' or detail_head.comment LIKE '%de%min%' or detail_head.comment LIKE '%reciept%' or detail_head.comment LIKE '%time%') ",
);

So, in the end, I'd like to be converting my current array of queries to say "(detail_head.comment LIKE '%port%' or detail_head.comment LIKE '%forward%' or detail_head.comment LIKE '%connect%' or detail_head.comment LIKE '%router%' or detail_head.comment LIKE '%fire%wall%' or detail_head.comment LIKE '%sonic%') AND table_uid NOT IN(LIST OF COMMA SEPARATED UIDs) "

Upvotes: 1

Views: 7216

Answers (2)

J.D. Pace
J.D. Pace

Reputation: 616

Try this:

'CONNECT' => "
    (  detail_head.comment LIKE '%port%'
    OR detail_head.comment LIKE '%forward%'
    OR detail_head.comment LIKE '%connect%'
    OR detail_head.comment LIKE '%router%'
    OR detail_head.comment LIKE '%fire%wall%'
    OR detail_head.comment LIKE '%sonic%'
    )
    AND NOT (
           detail_head.comment LIKE '%ccb%'
        OR detail_head.comment LIKE '%report%' 
        OR detail_head.comment LIKE '%portal%'
        OR detail_head.comment LIKE '%OCU%'
        OR detail_head.comment LIKE '%KS%'
        OR detail_head.comment LIKE '%screen%'
        OR detail_head.comment LIKE '%term%'
        OR detail_head.comment LIKE '%discount%'
        OR detail_head.comment LIKE '%panel%'
        OR detail_head.comment LIKE '%PMIX%'
        OR detail_head.comment LIKE '%pmix%'
        OR detail_head.comment LIKE '%document%'
        OR detail_head.comment LIKE '%point%'
        OR detail_head.comment LIKE '%km%'
        OR detail_head.comment LIKE '%forward%'
        OR detail_head.comment LIKE '%sales%'
        OR detail_head.comment LIKE '%intermittent%'
        OR detail_head.comment LIKE '%print%'
        OR detail_head.comment LIKE '%de%min%'
        OR detail_head.comment LIKE '%reciept%'
        OR detail_head.comment LIKE '%time%'
    )
",

Upvotes: 1

dougBTV
dougBTV

Reputation: 1878

I don't believe this will be any more efficient (it may indeed be less efficient), but, it may be a way to define your specifications more soundly -- using word boundaries in a regexp().

This returns 0:

SELECT 'foo report bar' REGEXP '[[:<:]]port[[:>:]]';

Where this returns 1:

SELECT 'foo report bar' REGEXP '[[:<:]]report[[:>:]]';

MySQL has more info in the manual. (5.1 manual linked)

Depending on the nature of the task at hand, and how regularly and how much strain this will put on my database server, I might consider adding fields or a related table to help me do the processing up front (when I insert the data) so I can run reports like this after the fact in a lighter fashion -- instead of having to do heavy textual processing in fulltext fields.

Upvotes: 0

Related Questions