[SummerL]

Why is it whenever i do a dual search on the first value of drop down menu and the keyword(text field) search.. No results could be displayed..
I did a test the query on MySQL.. it returns results..

But if i were to search on other values of the drop down menu cept' for the first value together with the keyword search.. There were results..

search Keyword

Code:

 // Get the search variable from URL For Keyword Search 
  $Keyword = @$_GET['Keyword'] ; 
//trim whitespace from the stored variable 
  $trimmedKeyword = trim($Keyword); 
//separate key-phrases into keywords 
  $trimmed_arrayKeyword = explode(" ",$trimmedKeyword); 

// Build SQL Query for each keyword entered 
foreach ($trimmed_arrayKeyword as $trimmKeyword){ 
// EDIT HERE and specify your table and field names for the SQL query 
$queryKeyword = "SELECT * FROM report WHERE DefectInfo LIKE \"%$trimmedKeyword%\" OR Rectifications like \"%$trimmedKeyword%\" OR Comments like \"%$trimmedKeyword%\" OR RelatedDoc like \"%$trimmedKeyword%\" ORDER BY DefectInfo DESC" ; 
// Execute the query to  get number of rows that contain search kewords 
$numresultsKeyword =mysql_query ($queryKeyword); 
$rowKeyword= mysql_fetch_array ($numresultsKeyword); 
$row_num_links_mainKeyword =mysql_num_rows ($numresultsKeyword); 

// next determine if 's' has been passed to script, if not use 0. 
// 's' is a variable that gets set as we navigate the search result pages. 
if (empty($_GET['s'])) 
{ 
$s=0; 
}elseif (is_numeric($_GET['s'])){ 
$s = $_GET['s']; 
}//end if 

// now let's get results. 
$queryKeyword .= " LIMIT $s,$limit" ; 
$numresultsKeyword = mysql_query ($queryKeyword) or die ( "Could not execute query" ); 
$rowKeyword= mysql_fetch_array ($numresultsKeyword); 

//store record id of every item that contains the keyword in the array we need to do this to avoid display of duplicate search result. 
do{ 
$adid_array[] = $rowKeyword[ 'ReportID' ]; 
}while( $rowKeyword= mysql_fetch_array($numresultsKeyword)); 
} //end foreach 

//delete duplicate record id's from the array. To do this we will use array_unique function 
$tmparr = array_unique($adid_array); 
$i=0; 
$newarr = isSet($_POST['Search']) ? $_REQUEST['Search'] : ''; 
foreach ($tmparr as $v) 
{ 
$newarr  .= "'" . $v . "',";  //turn newarr into a comma separated string good for an in clause in a query 
} 
$newarr = substr($newarr,0,strlen($newarr)-1);  //remove the trailing comma

Drop down search

Code:

 
// get the aircraft type 
$queryAircraftType = "SELECT distinct AircraftType FROM report"; 
$resultAircraftType = mysql_query($queryAircraftType) or die ("couldn't execute query."); 
$row_num_links_mainAircraftType =mysql_num_rows ($resultAircraftType); 

<?php while($rowAircraftType = mysql_fetch_array($resultAircraftType)) { 
if($rowAircraftType['AircraftType']==$_GET['AircraftType']) 
{ 
echo "<option selected value='$rowAircraftType[AircraftType]'>$rowAircraftType[AircraftType]</option>"."<BR>";} 
else 
{ 
echo "<option value='$rowAircraftType[AircraftType]'>$rowAircraftType[AircraftType]</option>";} 
} 
?>

Code:

$queryAircraftDisplay = "SELECT * from report where 1"; 

if (!empty($_GET['AircraftType'])) 
{ 
$queryAircraftDisplay  .= " and aircraftType='".$_GET['AircraftType']."' "; 
} 
if (!empty($_GET['Keyword'])) 
{ 
$queryAircraftDisplay  .= " and reportID in ($newarr)"; 
} 
$resultAircraftDisplay = mysql_query($queryAircraftDisplay) or die ("couldn't execute query ".mysql_error()); 
while ($row = mysql_fetch_array($resultAircraftDisplay)) 
{

i did a print " echo $queryAircraftDisplay; "once the final query

It got this statement-
SELECT * from report where 1 and aircraftType='AS-332' and reportID in ('7','10','8','11','12','13','14','15','16','17')

Any mistake in it?

[dennispopel]

Hello,

Although I didn't get the idea of running multiple queries in the foreach loop in the code, I think that you sould replace 'where 1' with 'where 1=1'. Because in the

SELECT * from report where 1 and aircraftType='AS-332' and reportID in ('7','10','8','11','12','13','14','15','16','17')

the expression may be evaluated as where 1 and aircraftType='AS-332' which does not hold.

[SummerL]

dennispopel thanks.. I did as you suggested, 'where 1=1', but the same problem still occurs..

[SummerL]

Anyone know how should i resolve the above problem?