PHP - mysql query not working when using NOT LIKE

Question

I have a query string being built and it works fine when I use LIKE but it will not work with NOT LIKE

 $sWhere = "";
    if ( $_GET['sSearch'] != "" )
    {

            $aWords = preg_split('/\s+/', $_GET['sSearch']);
            $sWhere = "WHERE (";

            for ( $j=0 ; $j<count($aWords) ; $j++ )
            {
                    if ( $aWords[$j] != "" )
                    {
                            if(substr($aWords[$j], 0, 1) == "!"){
                                    $notString = substr($aWords[$j], 1);
                                    $sWhere .= "(";
                                    for ( $i=0 ; $i<count($aColumns) ; $i++ )
                                    {
                                            $sWhere .= $aColumns[$i]." NOT LIKE '%".mysql_real_escape_string( $notString )."%' OR ";
                                    }
                            }
                            else{
                                    $sWhere .= "(";
                                    for ( $i=0 ; $i<count($aColumns) ; $i++ )
                                    {
                                            $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $aWords[$j] )."%' OR ";
                                    }
                            }
                            $sWhere = substr_replace( $sWhere, "", -3 );
                            $sWhere .= ") AND ";
                    }
            }
            $sWhere = substr_replace( $sWhere, "", -4 );

the part of the code that does work is:

 if(substr($aWords[$j], 0, 1) == "!"){
                                    $notString = substr($aWords[$j], 1);
                                    $sWhere .= "(";
                                    for ( $i=0 ; $i<count($aColumns) ; $i++ )
                                    {
                                            $sWhere .= $aColumns[$i]." NOT LIKE '%".mysql_real_escape_string( $notString )."%' OR ";
                                    }
                            }

if I remove the "NOT" the query works fine but it does nothing when I add the NOT.

thanks

Explain `does nothing`. Obviously when you use `NOT LIKE` instead of `LIKE` you just get the opposite result set — zerkms, Mar 01 '12 at 21:05
Does this post help? http://stackoverflow.com/questions/1638566/sql-not-like-statement-not-working — Donovan, Mar 01 '12 at 21:05
I see a lot of code, but not a single query. Quite odd for a MySQL question... — Ignacio Vazquez-Abrams, Mar 01 '12 at 21:08
it is for the jquery dataTables plugin filtering... when I use like it returns the correct results but when I use NOT LIKE it just show all results and does no filtering... — waa1990, Mar 01 '12 at 21:09
the query is built up throughout much more code there is no issue with the query running just when I add the NOT in there therefore the only code necessary to view is that snippet — waa1990, Mar 01 '12 at 21:10

score 2 · Accepted Answer · answered Mar 01 '12 at 21:13

2

It's because you are using OR in your NOT LIKE condition.

Finding all the rows where column1 NOT LIKE "a" OR column2 NOT LIKE "a" will match everything.

answered Mar 01 '12 at 21:13

Julien

212
1
18
53

are you saying I shoudl be using AND? – waa1990 Mar 01 '12 at 21:18
Yes; to reverse its meaning, you need to do `NOT LIKE x AND NOT LIKE y`. – halfer Mar 01 '12 at 21:19
Learning a little about [De Morgan's law](http://en.wikipedia.org/wiki/De_Morgan's_laws) may help – Mikhail Mar 01 '12 at 21:21

PHP - mysql query not working when using NOT LIKE

1 Answers1