asked on

If or case statement in where clause

HI Experts
I am trying to apply filters to the where clause in mysql.
The user will have the option to filter by keyword or compating pages

The defualt where statement is
(`theme_keywords`.`SiloFlag` = 'Research' AND `theme_keywords`.`profileid` = 92 AND `projectprofile`.`ProjectClusterid` = 51 AND `projectprofile`.`CompanyID` = 5)

How do you apply a case statement or an if statement to factor in the other filter options if used.
Is it better to do this in mysql or in the php of the page?

If the user fills in a keyword and leaves the competing pages blank and submits the page
then
Keyword
========
(`theme_keywords`.`SiloFlag` = 'Research' AND `theme_keywords`.`profileid` = 92 AND `projectprofile`.`ProjectClusterid` = 51 AND `projectprofile`.`CompanyID` = 5 AND`theme_keywords`.`theme_keywords` LIKE '%surfboard%')

If the user only filters by competing pages

competing pages
==============
(`theme_keywords`.`SiloFlag` = 'Research' AND `theme_keywords`.`profileid` = 92 AND `projectprofile`.`ProjectClusterid` = 51 AND `projectprofile`.`CompanyID` = 5 AND`theme_keywords`.`CompetingPages` < 1000000)

if both keywords and competing pages

Both
====
(`theme_keywords`.`SiloFlag` = 'Research' AND `theme_keywords`.`profileid` = 92 AND `projectprofile`.`ProjectClusterid` = 51 AND `projectprofile`.`CompanyID` = 5 AND`theme_keywords`.`theme_keywords` LIKE '%surfboard%' AND`theme_keywords`.`CompetingPages` < 1000000)

WHERE 
(`theme_keywords`.`SiloFlag` = 'Research' AND `theme_keywords`.`profileid` = 92 AND `projectprofile`.`ProjectClusterid` = 51 AND `projectprofile`.`CompanyID` = 5) 
OR (`theme_keywords`.`SiloFlag` = 'Research' AND `theme_keywords`.`profileid` = 92 AND `projectprofile`.`ProjectClusterid` = 51 AND `projectprofile`.`CompanyID` = 5 AND`theme_keywords`.`theme_keywords` LIKE '%surfboard%') 
OR(`theme_keywords`.`SiloFlag` = 'Research' AND `theme_keywords`.`profileid` = 92 AND `projectprofile`.`ProjectClusterid` = 51 AND `projectprofile`.`CompanyID` = 5 AND`theme_keywords`.`CompetingPages` < 1000000)
OR (`theme_keywords`.`SiloFlag` = 'Research' AND `theme_keywords`.`profileid` = 92 AND `projectprofile`.`ProjectClusterid` = 51 AND `projectprofile`.`CompanyID` = 5 AND`theme_keywords`.`theme_keywords` LIKE '%surfboard%' AND`theme_keywords`.`CompetingPages` < 1000000)
ORDER BY RPM DESC

Open in new window

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

matthewdacruz

ASKER

How does the heredoc differentiate between the diffrent if clauses?
I was also thinking php.

Would this work in the where clause of the query in the page?

WHERE ".if(isset($_POST['SubmitFilter'])){
if($_POST["kw"] != '' && $_POST["CMP"] != ''){
echo 'theme_keywords.SiloFlag = 'Research' AND theme_keywords.profileid = %s AND projectprofile.ProjectClusterid = %s AND projectprofile.CompanyID = %s AND themekeywords.theme_keywords LIKE '%.$_POST["kw"].%' and themekeywords.CompetingPages = '.$_POST["CMP"].'  ORDER BY RPM DESC'
}elseif ($_POST["kw"] != '' && $_POST["CMP"] == $_POST["CMP"]){
echo 'theme_keywords.SiloFlag = 'Research' AND theme_keywords.profileid = %s AND projectprofile.ProjectClusterid = %s AND projectprofile.CompanyID = %s AND themekeywords.theme_keywords LIKE '%.$_POST["kw"].%' and themekeywords.CompetingPages = '.$_POST["CMP"].'  ORDER BY RPM DESC'
}
else{ echo "theme_keywords.SiloFlag = 'Research' AND theme_keywords.profileid = %s AND projectprofile.ProjectClusterid = %s AND projectprofile.CompanyID = %s ORDER BY RPM DESC"}}, GetSQLValueString($colname_RS_grid, "int"),GetSQLValueString($colname2_RS_grid, "int"),GetSQLValueString($colname3_RS_grid, "int"));

Open in new window

matthewdacruz

ASKER

Ray, would you apply the usual if statement to the "heredoc"?

Kevin Cross

It seems like you are asking how to make the last two conditions optional? You can try something like this:

`theme_keywords`.`SiloFlag` = 'Research'
AND `theme_keywords`.`profileid` = 92
AND `projectprofile`.`ProjectClusterid` = 51
AND `projectprofile`.`CompanyID` = 5
AND ($keywords = '' OR `theme_keywords`.`theme_keywords` LIKE '%$keywords%')
AND ($pages is null OR `theme_keywords`.`CompetingPages` < $pages)

As you can see, you can have this be a default value (signifying not used) or null and you simply check for the not-used case first otherwise do the comparison with column from your table.

Hope that helps!

Ray Paseur

How does the heredoc differentiate between the different if clauses?

I can't help you with the logic about the forms, but I can show you how, once your script knows what the WHERE clause should contain, you can create the WHERE clause easily. That's what the heredoc syntax helps with. Heredoc syntax lets you create variables, then have them substituted into a longer string.
http://php.net/manual/en/language.types.string.php#language.types.string.syntax.heredoc

innotionent

If I'm understanding this correctly. You have multitple parameters to your query.

select foo1, foo2, foo3 from bar where foo1 = a and foo2 = b and foo3 = c

and then some additional conditions tacked on depending on what textbox a user fills out.

and foo 4 = d etc.

I'm thinking that you can, for example if you are using the $_POST, do a strlen of the variable.
if(strlen($_POST[CompanyID]) > 0){ sqlquery .= " AND foo = d "; }

Hopefully I'm understanding your need correctly.

http://php.net/manual/en/function.strlen.php