Only use where condition if string parameter is not null or empty string

Question

I have a query and I only want to use the where conditions if the parameters are not null or empty strings.

select
users.user_id,
users.fname,
users.lname,
Certifications.certName,
skills.skillName
from users
left join Certifications on users.user_id = Certifications.user_id
left join specializations on users.user_id = specializations.user_id
left join skills on skills.user_id = skills.user_id
where
(Certifications.certName is not null and Certifications.certName like 'CCNA Routing and Switching')  or 
(skills.skillName is not null and skills.skillName like '') or
(users.location is not null and users.location like '') 
group by users.user_id

So if the search term for skillName and location is empty I don't want to include those in the where clause.

Where are these search terms coming from? How are you generating this query? — gen_Eric
– gen_Eric, Commented Sep 3, 2015 at 16:58
Maybe you could try something like ($searchTerm != '' AND skills.skillName like '$searchTerm')? — gen_Eric
– gen_Eric, Commented Sep 3, 2015 at 17:00
I have a jquery method that passed json data to a php file that actually calls to the query to the database. — Jonathan Kittell
– Jonathan Kittell, Commented Sep 3, 2015 at 17:03
Then have PHP add only the clauses it needs when generating the query. — gen_Eric
– gen_Eric, Commented Sep 3, 2015 at 17:04
what Rocket said ↑ . Otherwise you are just saying, oh, sorry, just kidding — Drew
– Drew, Commented Sep 3, 2015 at 17:05

Drew · Accepted Answer · 2015-09-03 17:01:33Z

2

You know this in the front-end. Adjust accordingly.

answered Sep 3, 2015 at 17:01

Drew

25k10 gold badges47 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

brlaranjeira · Accepted Answer · 2015-09-03 17:58:55Z

1

You must build your query only with the parameters that are not null

$whereClauses = array();
if (!empty($certifications)) {
   $whereClauses[] = 'Certifications.certName like ?';
}
if (!empty($skillName)) {
   $whereClauses[] = 'skills.skillName like ?';
}
if (!empty($location)) {
   $whereClauses[] = 'users.location like ?';
}
$query .= implode(' OR ', $whereClauses);

Then, of course, you need to bind the corresponding parameters to the ? characters

answered Sep 3, 2015 at 17:58

brlaranjeira

3672 silver badges11 bronze badges

Collectives™ on Stack Overflow

Only use where condition if string parameter is not null or empty string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related