Mysql – How to search multiple values using LIKE

MySQLquerywhere

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."

Best Answer

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.

Related Solutions

Mysql search matching multiple values in multiple columns

You're correct: FULLTEXT search didn't hit InnoDB until MySQL 5.6. This leaves you with a few options:

Update to MySQL 5.6 and use a FULLTEXT index
Change the contract of your function to only allow prefix searches; that is, 'term%'. It will fulfill many use cases while saving your DB.
Convert to a MyISAM table, or create a spare MyISAM table that you can join on specifically for searches.

Since you're building the query in Java, I'd also suggest removing the CONCAT('%' stuff in favor of just creating a second variable that has the %s already applied. That saves the DB from the extra work of parsing it and lets it utilize indexes better. Speaking of indexes, be sure you have indexes that span the columns where possible.

Regarding the Duplicate removal, you may be better off performing a GROUP BY p.idNumber to save the extra effort and data transfer in Java.

MySQL – How to Select Using REGEXP

It depends on how strict you want to be, and on what you can expect from the form.

If all that's needed is the presence of 3 #'s, it would suffice to use

SELECT text_message
FROM incoming_sms
WHERE text_message LIKE '%#%#%#%';

If you want to have at least one character in each field, you can use (as you suggested yourself):

SELECT text_message
FROM incoming_sms
WHERE text_message REGEXP '^([^#]+#){3}[^#]+$';

If you know that leading whitespace is trimmed from each field and you want to ensure you have at least one non-whitespace character, use:

SELECT text_message
FROM incoming_sms
WHERE text_message REGEXP '^([^#\s]+[^#]*#){3}[^#\s]+[^#]*$';

Best Answer

Related Solutions

Mysql search matching multiple values in multiple columns

MySQL – How to Select Using REGEXP

Related Question