PHP/AJAX Case Insensitive search

Question

I am trying to do create a case-insensitive search on multiple DB columns in a single search input.

Currently I have the search working for multiple columns using a single search string. The issue is that it does not work case-insensitive.

As an example...if I search a project's status: "paid" it will not find the associated project because the status name in the DB is set to "Paid".

I am trying to make it so a user can search by "paid" or "Paid" returning the same results.

Here is my code:

    if (isset($_GET['keyword'])) {
    $_SESSION['quickSearch']=$_GET['keyword'];
    $keyword = trim($_GET['keyword']);

    $srWords  = explode(' ', $keyword);
    $srString = array();
    foreach ($srWords as $srWord) {
        if (!empty($srWord))
            $srString[] = '[NM] like \'%' . $db->sql_escape($srWord) . '%\'';
    }
    $srStringHving = implode(' AND ', $srString);
    $srString      = implode(' OR ', $srString);

    $query = "SELECT clients.clientName,
       projects.Client_ID,
       projects.projectNumber,
       projects.projectName,
       projects.projectManager,
       projects.expectedDate,
       projects.address,
       projects.CreationDate,
       projects.custom1,
       projects.custom2,
       projects.custom3,
       projects.custom4,
       projects.custom5,
       projects.custom6,
       projects.custom7,
       projects.custom8,
       projects.custom9,
       projects.Status_ID,
       statuses.status, concat(clients.clientName,' ',
       projects.Client_ID,' ',
       projects.projectNumber,' ',
       projects.projectName,' ',
       projects.projectManager,' ',
       projects.expectedDate,' ',
       projects.address,' ',
       projects.CreationDate,' ',
       projects.custom1,' ',
       projects.custom2,' ', 
       projects.custom3,' ',
       projects.custom4,' ', 
       projects.custom5,' ',
       projects.custom6,' ',
       projects.custom7,' ', 
       projects.custom8,' ',
       projects.custom9,' ',
       projects.Status_ID,' ',
       statuses.status) as srterm
FROM projects
JOIN clients ON projects.Client_ID = clients.Client_ID
JOIN statuses ON  projects.Status_ID = statuses.Status_ID
WHERE " . str_ireplace('[NM]', 'clientName', $srString) . "
  OR " . str_ireplace('[NM]', 'projectNumber', $srString) . "
  OR " . str_ireplace('[NM]', 'projectName', $srString) . "
  OR " . str_ireplace('[NM]', 'address', $srString) . "
  OR " . str_ireplace('[NM]', 'area', $srString) . "
  OR " . str_ireplace('[NM]', 'status', $srString) . "
  OR " . str_ireplace('[NM]', 'custom1', $srString) . "
  OR " . str_ireplace('[NM]', 'custom2', $srString) . "
  OR " . str_ireplace('[NM]', 'custom3', $srString) . "
  OR " . str_ireplace('[NM]', 'custom4', $srString) . "
  OR " . str_ireplace('[NM]', 'custom5', $srString) . "
  OR " . str_ireplace('[NM]', 'custom6', $srString) . "
  OR " . str_ireplace('[NM]', 'custom7', $srString) . "
  OR " . str_ireplace('[NM]', 'custom8', $srString) . "
  OR " . str_ireplace('[NM]', 'custom9', $srString) . "  HAVING " . str_ireplace('[NM]', 'srterm', $srStringHving) ."
ORDER BY projects.project_ID DESC
LIMIT 0 , 200";  

$result = mysqli_query($dbc, $query);

thanks, ahead of time for any help!

I think this is the explanation you are looking for : http://stackoverflow.com/questions/2876789/case-insensitive-for-sql-like-wildcard-statement — Arthur Hemery, Jan 11 '14 at 02:20

score 2 · Answer 1 · answered Jan 11 '14 at 02:13

2

Look into the LOWER() method provided by MySQL. You can emulate a case insensitive search by transforming the string to lower case.

SELECT * FROM my_table WHERE LOWER(my_field) = 'my value';

answered Jan 11 '14 at 02:13

Ryan

14,392
8
62
102

score 1 · Answer 2 · edited May 23 '17 at 10:33

1

From this answer:

You can lowercase the value and the passed parameter :

SELECT * FROM `table` WHERE LOWER(`Value`) = LOWER("DickSavagewood")

Another (better) way would be to use the COLLATE operator as said in the documentation

edited May 23 '17 at 10:33

Community

1
1

answered Jan 11 '14 at 02:14

Alfie

2,341
2
28
45

PHP/AJAX Case Insensitive search

2 Answers2