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

matthewdacruzAsked:
Who is Participating?
 
Ray PaseurCommented:
I think I would want to do it in PHP.  You can use "heredoc" notation for something like this.

// SET UP THE DEFAULT VALUES
$tks = 'Research';
$tkp = '92';
$ppc = '51';
$pci = '5';

/* HERE, YOU MIGHT CHANGE THE DEFAULT VALUES */

// RENDER THE CLAUSE USING THE DEFAULT OR SUBSTITUTED DATA
$where = <<<WHERECLAUSE
(`theme_keywords`.`SiloFlag` = '$tks' AND `theme_keywords`.`profileid` = $tkp AND `projectprofile`.`ProjectClusterid` = $ppc AND `projectprofile`.`CompanyID` = $pci)
WHERECLAUSE;
0
 
matthewdacruzAuthor Commented:
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

0
 
matthewdacruzAuthor Commented:
Ray, would you apply the usual if statement to the "heredoc"?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Kevin CrossChief Technology OfficerCommented:
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!
0
 
Ray PaseurCommented:
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
0
 
innotionentCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.