• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

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

0
matthewdacruz
Asked:
matthewdacruz
1 Solution
 
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now