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