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."
  • 1
    Your query is vulnerable to SQL injection. You need to fix this. Don't concatenate user input to query strings. Only include the SQL expressions relevant to user input. Don't search for year = X if the user didn't input a year. – danblack Jan 29 at 4:30
  • i don't get the results that i want We cannot see what parameter values are used and what query text is built using them. We don't know what source data is used, what result you obtain and what is wrong in it... we cannot help you. – Akina Jan 29 at 6:10
  • 1
    Welcome to DBA.SE and thanks for your participation. You might want to have a look at the question using CASE in the WHERE clause over on Stack Overflow. Stack Overflow might even be the better place to ask your question, because you have some programming aspects when you write about using forms in your question. If you could provide us with more information then we might be able to help you (data, results, why your query isn't working, ...). otherwise your question might be closed as unclear what you're asking. – John aka hot2use Jan 29 at 6:59

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.

Your Answer

