SQL“LIKE”如果为空则返回所有行

Question

Hello I have 2 textboxes and i want to give to the user the option to choose one in order to find results. The user can search through the id or the name. My problem is because i use LIKE%field% when the user chooses to search through the id the name field stays empty and returns all the table rows. I want to have results only if the user enters some value in the textbox. This is my sql query. I'm using mysql

"SELECT * FROM properties WHERE ID='$id' OR Name LIKE '%$name%'"

Thank you all

Answer 1

You can do this in a single query (values are checked from the query itself):

"SELECT * FROM properties WHERE ('$id'='' OR ID='$id') AND ('$name' ='' OR Name LIKE '%$name%')"

Explanation:

1. First condition:

   The query will select records with ID='$id' only when $id is not empty. If $id is empty, query will not go for the second part ID='$id'

2. Second condition:

   The query filters records with Name LIKE '%$name%' only when $name is not empty. If $name is empty, query will not go for Name LIKE '%$name%'.

NB: This technique is extremely useful when you have numerous parameters to check, rather than using a bunch of if...elses at php side.