SQL command for when number of conditions for searching may vary-CodePudding

So i have a React frontend that implements search operation on a mysql database. And for the search operation it can take 4 conditions (id, name, invoice, posting) as input. But its not necessary that a user needs to give values for all 4 conditions. A user can give values for anywhere between 1-4 condition.

I cant quite figure out mysql command to perform this operation. Can someone help me writing this command using 'like' operator?

Ps- Suppose user just gives id for searching. The all rows with that id should show. If user gives both id and name then rows matching that id and that name should show and so on..

CodePudding user response：

You could write you query using LIKE and % to accept any value if the search term is an empty string.

SELECT * FROM myTable
WHERE
id LIKE '%search_variable_one%', 
AND name LIKE '%search_variable_two%',
AND invoice LIKE '%search_variable_three%',
AND posting LIKE '%search_variable_four%';

so that if any of the variable are empty the query will accept any value.
This may make your search to wide because you will return all values containing the search string.
You could also write your WHERE's as follows

WHERE ( name LIKE 'search_variable_one' OR 'search_variable_one' = '')

so that if search_variable_one is an empty string we accept all values of name but if it contains a value we search for the exact value. It depends how you want to search.