Search code examples
mysqlsqlsql-likecase-insensitive

Multiple case insensitive like comparisons in mysql

I am trying to create a book store search field that matches keywords in a book's title, subtitle, and author columns. I can get a single case insensitive like statement to work, but it seems to not work when I try under multiple columns. Can anyone help me find what is wrong? Or is there an easier way to accomplish this?

    $getbooks= mysql_query("SELECT * FROM books WHERE (
    LOWER(title) LIKE LOWER('%,".$_GET['search'].",%') 
    OR LOWER(author) LIKE LOWER('%,".$_GET['search'].",%') 
    OR LOWER(subtitle) LIKE LOWER('%,".$_GET['search'].",%')
    ) 
    AND status='1' 
    ORDER BY id DESC");
Solution

  • You need to remove the commas in your LIKE clauses:

    e.g., instead of :

    LIKE LOWER('%,".$_GET['search'].",%')

    do this:

    LIKE LOWER('%".$_GET['search']."%')

    Otherwise you will only match on items that are surrounded by commas!

    You should also give some serious credence to the comments indicating SQL Injection attack vulnerability.