Jump to content


Photo

Multiple Where Clauses

where select

  • Please log in to reply
1 reply to this topic

#1 ghouri

ghouri

    Newbie

  • New Members
  • Pip
  • 1 posts

Posted 25 February 2013 - 02:01 AM

I am Simplifying my Question here with data as well.

*I have a String of keywords that i have to search with my database's fields.
*I have 4 fields(variables) upon which i have to apply Where clause.
* I wanted to have a query that fetch data either one of 4 where clauses or all of them(OR operation in Where)

Here is the Php Code that extracts keywords from string and make query.

$condition = strtoupper($_GET['iname']); //for keywords string
$operation = $_GET['operation'];//Operation Type AND OR between Keywords
//these are four flags determining that which where clause is present
$titleflag = "false";
$descriptionflag = "false";
$productoldflag = "false";
$productnewflag = "false";
//if title present
if(/*title present or Not*/)//if Not
{
		 $titleflag = "false";
$title ='';
		 $titlearray[] = "upper(name) LIKE '%%'";	
}
else
{
$title = $condition;
$titleflag = "true";
		 //***************************
		 $search = strtoupper($title);
		 $search = trim($search);
		 $terms = explode(' ', $search);
		 $count = 0;
		 $line="";
		 foreach ($terms as $term) {
			 if(($term == " "))
			 {					
				 if($count == 0)
					 {
						 $count++;
						 $titlearray[] = "upper(name) LIKE '%".$term."%'";							
					 }
				 else			
					 continue;
			 }
			 else	
			 {
				 $titlearray[] = "upper(name) LIKE '%".$term."%'";					
			 }
		 }			
}
//This is it. i did for three other where clauses
//description,misold,misnew
//below down is my query where i construct the actual query
$sql = "Select distinct id,name from item WHERE
			 (".implode( $operation ." ", $titlearray).") AND ".$titleflag."
		 OR (".implode( $operation ." ", $descriptionarray).") AND ". $descriptionflag. "
		 OR (".implode( $operation ." ", $oldmisarray).") AND ". $productoldflag. "
		 OR (".implode( $operation ." ", $newmisarray).") AND ". $productnewflag. "
		 order by name asc";
//Provided Search String
/*
$condition = "Altera EMP";
*/
//If value in $operation = AND then produced query will be look like
/* AND OPeration
Select distinct id,name from item
WHERE (upper(name) LIKE '%ALTERA%' AND upper(name) LIKE '%EMP%') AND true
OR (upper(description) LIKE '%ALTERA%' AND upper(description) LIKE '%EMP%') AND true
OR (upper(productnoold) LIKE '%%') AND false
OR (upper(productnonew) LIKE '%%') AND false
order by name asc
*/
/* Or Operation
Select distinct id,name from item
WHERE (upper(name) LIKE '%ALTERA%'OR upper(name) LIKE '%EMP%') AND true
OR (upper(description) LIKE '%ALTERA%'OR upper(description) LIKE '%EMP%') AND true
OR (upper(productnoold) LIKE '%%') AND false
OR (upper(productnonew) LIKE '%%') AND false
order by name asc
*/
Now what the requirement is that I must use OR in every Keyword for all Where and hence forth for every keyword. and combine the result of all keyword search either AND or OR


#2 fenway

fenway

    MySQL Si-Fu / PHP Resident Alien

  • Moderators
  • 16,195 posts
  • LocationToronto, ON

Posted 28 February 2013 - 09:01 PM

Requirement? Whose?


Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com