PHP search using == and not case sensitive

Question

I have a code in PHP and I want to use == operator to search only exact terms and also have the search not case sensitive. The first thing doesn't work when I change LIKE to ==.

<?php
$con = mysql_connect('********','********','********');
mysql_select_db("********",$con);

function search_results($keywords){
    $returned_results = array(); 
    $where ="";

    $keywords = preg_split('/[\s]+/',$keywords);
    $total_keywords = count($keywords);

    foreach($keywords as $key=>$keyword){
        $where .= "`keywords` LIKE '%$keyword%'";
        if($key != ($total_keywords -1)){
            $where .=" AND ";
        }
    }
    $results = "SELECT name, image_url, game_url, alt FROM search_games WHERE $where";
    $results_num = ($results =mysql_query($results))? mysql_num_rows($results):0;
    if($results_num === 0){
        return false;
    }
    else{
        while ($results_row = mysql_fetch_assoc($results)) {
    $returned_results[] = array(
        'name' => $results_row['name'],
        'image_url' => $results_row['image_url'],
        'game_url' => $results_row['game_url'],
        'alt' => $results_row['alt']
    );
}
        return $returned_results;
    }
}
?>

Please start using `mysqli_` or `PDO` as `mysql_` is deprecated and removed in PHP7 — SuperDJ, Feb 12 '17 at 10:47

score 1 · Answer 1 · answered Feb 12 '17 at 10:45

Use MySql LOWER function to perform incasesensitive search:

...

foreach ($keywords as $key => $keyword) {
    $keyword = strtolower($keyword);
    $where .= " LOWER(`keywords`) LIKE '%$keyword%'";
    if ($key != ($total_keywords -1)) {
        $where .= " AND ";
    }
}

Thanasis1101 · Answer 2 · 2017-02-12T11:33:11.693

0

You could replace the foreach with the code below:

foreach($keywords as $key=>$keyword){
    $keyword = strtoupper($keyword);
    $where .= "upper(`keywords`) = '$keyword'";
    if($key != ($total_keywords -1)){
        $where .=" AND ";
    }
}

The code above, makes the $keyword string to upper case and then in the sql query it compares it with the upper(keywords), so they both are upper case when compared.

Sources:

edited Feb 12 '17 at 11:33

answered Feb 12 '17 at 10:24

Thanasis1101

1,614
4
17
28

I use the solution with lower. This solution is practically the same, but there is wrong position of commas `upper(keywords)`, it should be upper(`keywords`). But = operator doesn't work. – Catchamouse Feb 12 '17 at 11:07
Yes you're right. Thanks for the correction. I edited it. Does it work now? – Thanasis1101 Feb 12 '17 at 11:11
The solution with lower works, but operator = doesn't work. – Catchamouse Feb 12 '17 at 11:15
What do you mean the operator = doesn't work? On which line of code? – Thanasis1101 Feb 12 '17 at 11:16
(by the way in the correction there was deleted ` symbols) – Catchamouse Feb 12 '17 at 11:17
$where .= "upper(`keywords`) LIKE '%$keyword%'"; I want to use = instead of LIKE. – Catchamouse Feb 12 '17 at 11:18
Yes I undertood it. I have edited them. I also replaced the = with LIKE. What is not working? – Thanasis1101 Feb 12 '17 at 11:19
I need to use = or == operator because I don't want to search by nonsense, because LIKE cause that it will search the substring. I want to search by exact phrase. – Catchamouse Feb 12 '17 at 11:21
Oh I get it. Why doesn't = work? It should work (see [here](http://stackoverflow.com/questions/871578/how-to-compare-strings-in-sql-ignoring-case)). Maybe you don't check it correctly? What are the results? Actually you shouldn't use % signs, see my edit. – Thanasis1101 Feb 12 '17 at 11:32
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/135492/discussion-between-thanasis-and-catchamouse). – Thanasis1101 Feb 12 '17 at 11:33

PHP search using == and not case sensitive

2 Answers2