How to search multiple values using LIKE I'm using MySQL and I have a table with these fields :


id
name
kode_fakultas
year


I want to create a form for searching based on user input. Example, if the user search is based on name, but the fields kode_fakultas and year are not filled with a value, then the form should retrieve the results based on name. And if the user wants to search based on kode_fakultas, but name and year are not filled  in, then the form would retrieve results based on kode_fakultas only. 

My query seems wrong, because I don't get the results that I want.

select * from teacher
    where name like '%".$name."%' or year = '".$year."' or kode_fakultas = '".$fak."
Do not try to do it all in a single query.  But first, I will digress...

If you said AND instead of OR, that query needs

INDEX(year, kode_fakultas)  -- in either order


Indexing name is useless because of the leading wildcard on the LIKE expression.

But you have OR, so no indexing is useful; the entire table will be scanned, checking each row for 3 conditions to see if any match.

But that is not really what your UI is asking for.  To rephrase your requirements:


  The user will enter one of part of a name, the year, or kode_fakultas.


Your code must then build a query with only one of the tests in it, then execute it.  At that point, you would need

INDEX(year), INDEX(kode_fakultas)


but be stuck with a table scan if you need the leading wildcard on the LIKE.