Search w/ MySQL/PHP but not Case-Sensitive

Question

I learned in school to program a search with MySQL and PHP.

I have this code for my search.php:

<?php
mysql_connect("localhost", "root", "");
mysql_select_db("db1");

$sql = "SELECT * from tuser";
$Namen = mysql_query ($sql);
$user_id = $_GET['user_id'];
$search = $_GET['search'];

while ($ergebnis_datensatz = mysql_fetch_array($Names)){
    $user_id = $ergebnis_datensatz['user_id'];
    $name = $ergebnis_datensatz['name'];
    $surname = $ergebnis_datensatz['surname'];

    if ($name == "$search") {
        echo "$name $surname";
    }

    else {
        echo "";
    }


    if ($surname == "$search") {
        echo "$name $surname";
    }

    else {
        echo "";
    }

}


?>

And on my Search input site i have an simple input with the "name=search"

If I use this search now, it's case sensitive. Example: I search for "*k*elvin" and nothing appears. But if search for "*K*elvin" i get my results.

How can i make it not case-sensitive?

score 4 · Answer 1 · answered Mar 27 '13 at 16:47

Check the collation of your table. By default (depending on your configuration) MySQL will use utf8_general_ci where ci is case-insensitive. If you use utf8_bin on the other hand you will get a case-sensitive collation and all your searches will be case-sensitive.

Other common collations are latin1_general_ci (and *_cs, and latin1_bin).

score 1 · Answer 2 · answered Mar 27 '13 at 16:48

You really should optimize your query and search only relevant options.

Though you can search case insensitive by turning both parameters to lowercase (For example). That way, your code would become: if (strtolower($name) == strtolower($search)) { and if (strtolower($surname) == strtolower($search)) {

However, I'd instead limit your query to something like: SELECT * from tuser WHERE name LIKE $search OR surname LIKE $search, which will yield all results that match your search parameter.

Don't forget to escape $search

score 1 · Answer 3 · edited May 23 '17 at 12:21

As already mentioned, most common MySQL collations are case insensitive. You can however get around this with the BINARY operator if you don't wish to modify collations.

http://dev.mysql.com/doc/refman/5.0/en/charset-binary-op.html

i.e.

SELECT * 
FROM tuser 
WHERE BINARY $search IN (name,surname)

So giving

$sql = 'SELECT * FROM tuser WHERE BINARY "'.$search.'" IN (name,surname)';

IMPORTANT Be careful with this. At present this would potentiallly leave your SQL open to injection. This will be more efficient than selecting EVERYTHING and then looping over it in PHP but you will need some code improvements first.

Here's an answer on here about SQL injection How can I prevent SQL injection in PHP?

score 0 · Answer 4 · answered Mar 27 '13 at 16:50

0

You can use the strcasecmp() function to compare two strings and to ignore case sensitivity.

if ($name == "$search") {

to:

if (strcasecmp($name, $search)) {

answered Mar 27 '13 at 16:50

llanato

2,508
6
37
59

If you wanna see if "$name == $search" with "strcasecmp", the code would look more like "if (strcasecmp($name, $search) == 0)", this returns 0 if the binairy string are equals otherwise >0 or <0. – Booba__2012 Sep 23 '14 at 08:02

Search w/ MySQL/PHP but not Case-Sensitive

4 Answers4