SQL "LIKE" If empty returns all rows

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

It doesn't work this way in PHP, I think you also need ( ) around OR conditions — Hituptony, Apr 25 '14 at 17:42
OMG! Its 2014! Parameterize your queries! http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — John Hartsock, Apr 25 '14 at 17:45

score 3 · Answer 1 · answered Apr 25 '14 at 17:42

3

If the user has to select which field to search, you can do:

if ($_POST['search'] == 'id') {
    $sql = "SELECT * FROM properties WHERE ID='$id'"
} else {
    $sql = "SELECT * FROM properties WHERE Name LIKE '%$name%'"
}

answered Apr 25 '14 at 17:42

Barmar

741,623
53
500
612

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

2

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:

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

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 25 '14 at 17:46

Raging Bull

18,593
13
50
55

1

Almost right. It should be `('$name' != '' AND ...)` – Barmar Apr 25 '14 at 17:47
1

@Barmar: It should be `='' OR`. See the explanation in my answer. – Raging Bull Apr 25 '14 at 18:03
When I made my comment, you connected the ID and Name conditions with `OR`, so `AND` was appropriate within the Name-specific part. Since you changed the overall logic, now `OR` is appropriate within each part. – Barmar Apr 25 '14 at 18:11
It's far too convoluted to be a good idea, whether it works or not. Putting all this logic directly into the query, instead of using PHP and SQL each where appropriate, just obfuscates your intent. Save it for code golf. – Air Apr 25 '14 at 18:12
@AirThomas: You know, I was surprised when I see this logic for the first time. And it will work like charm. Trust me! I have played with such queries several times. – Raging Bull Apr 25 '14 at 18:14
One question please, explain to me `('$id'='' OR ID='$id')`how it works? – user3537552 Apr 25 '14 at 18:23
@user3537552: For simple understanding, where clause will look for these two conditions (id,name) to return true. And `('$id'='' OR ID='$id')` will return true when **one of these** conditions are true. `'$id'=''` will be true when it is an empty string and `ID='$id'` will return true by filtering the result whereas `'$id'=''` will return just true (with no filtering). – Raging Bull Apr 25 '14 at 18:28
@user3537552: The same happens for the other condition (with name). Please let me know, if you need further clarification. – Raging Bull Apr 25 '14 at 18:30

SQL "LIKE" If empty returns all rows

2 Answers2