Multiple case insensitive like comparisons in mysql

312 views Asked by At

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");
1

There are 1 answers

5
user700390 On BEST ANSWER

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.