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

Question

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) "

Please post an example comment, too, so we can see "report" and "port" in context. — J.D. Pace, May 13 '13 at 13:35
Also note, I think I have most of what I wanted to do coded out... I'm just not sure how it will work: — brandoncluff, May 13 '13 at 13:36
$exclude_where_clause = $exclude_where_clauses[$_REQUEST['count_request']]; $not_like_qry = "SELECT detail_head.scr FROM detail_head WHERE detail_head.call_status != 'c' AND call_origins_uid != 5 AND $exclude_where_clause "; $qry .= "AND call_origins_uid != 5 GROUP BY detail_head.scr "; $dob->setQuery($not_like_qry); while ($dob->fetch(MYSQL_ASSOC)) { $rows[] = $dob->getRowAsArray(); } foreach ($rows as $row) { fputcsv($tmpdata, $row, '|'); } — brandoncluff, May 13 '13 at 13:37
Beware of the performance implications of using `LIKE '%keyword%'`. Using `LIKE` in this way can seriously slow down your queries, especially if you have a large amount of data. — Spudley, May 13 '13 at 13:38

J.D. Pace · Accepted Answer · 2013-05-13T18:50:31.073

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%'
    )
",

score 0 · Answer 2 · answered May 13 '13 at 15:24

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.

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

2 Answers2