Optional Search fields

[NoobPHP]

What should to do allow optional search? I have 2 drop down box and a search keyword textfield. It should allow me to search results either from a single field or multiple fields. That is to say, narrow of results when multiple fields were selected.

Drop down Box Search Query

// 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);

// get the aircraft tail number
$queryAircraftTailNo = "SELECT distinct AircraftTailNo FROM report";
$resultAircraftTailNo = mysql_query($queryAircraftTailNo) or die ("couldn't execute query.");
$row_num_links_mainAircraftTailNo =mysql_num_rows ($resultAircraftTailNo);

Populate Drop Down List from database

<td><select name="AircraftType" id="AircraftType"">
<?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>";}
}
?>

<select name="AircraftTailNo" id="AircraftTailNo"">
<?php while($rowAircraftTailNo = mysql_fetch_array($resultAircraftTailNo)) {
if($rowAircraftType['AircraftTailNo']==$_GET['AircraftTailNo'])
{
echo "<option selected value='$rowAircraftTailNo[AircraftTailNo]'>$rowAircraftTailNo[AircraftTailNo]</option>"."<BR>";}
else
{
echo "<option value='$rowAircraftTailNo[AircraftTailNo]'>$rowAircraftTailNo[AircraftTailNo]</option>";}
}
?>
</select>
<input type="submit" name="Submit" value="Search" />

Display of results in table
The thing is how should i allow filtering results.. if the user selects a value from either of the drop down list, it should display results in the table. if both of the drop down lists were selected it should further filter the results base on the both the drop down lists.

<?php
if (isset($_GET['AircraftTailNo']))
{
echo "Search results for: <b>" .$_GET['AircraftType']. "</b><br>";
 //end highlight
   $cntr = 1 ;  //counter for the table columns

   echo "<table width=90% align=center border=1><tr>
   <td align=center bgcolor=#00FFFF>AirCraft Tail No.</td>
   <td align=center bgcolor=#00FFFF>AirCraft Type</td>
   <td align=center bgcolor=#00FFFF>Servicing Type</td>
   <td align=center bgcolor=#00FFFF>Airframe Hours</td>
   <td align=center bgcolor=#00FFFF>Defect Info</td>
   <td align=center bgcolor=#00FFFF>Rectifications</td>
   <td align=center bgcolor=#00FFFF>Comments</td>
   <td align=center bgcolor=#00FFFF>Related Documents</td>
   </tr><tr>";

$queryAircraftTypeDisplay = "SELECT * from report where aircraftType='".$_GET['AircraftType']."' or aircraftTailNo='".$_GET['AircraftTailNo']."'";
//echo $queryAircraftTypeDisplay;
$resultAircraftTypeDisplay = mysql_query($queryAircraftTypeDisplay) or die ("couldn't execute query ".mysql_error());
while ($row = mysql_fetch_array($resultAircraftTypeDisplay))
{

//now let's make the keywords bold. To do that we will use preg_replace function.
		//Replace field
  $aircraftTailNo   = $row[ '1' ] ;
  $aircraftType     = $row[ '2' ] ;
  $servicingType	= $row[ '3' ] ;
  $airFrameHours    = $row[ '4' ] ;
  $defectInfo       = $row[ '5' ] ;
  $rectifications   = $row[ '6' ] ;
  $comments         = $row[ '7' ] ;
  $relatedDoc       = $row[ '8' ] ;
  //if there are 7 cols, write the <tr> tag and reset the counter
  if ($cntr == 2)
  {
    echo "</tr><tr>";
    $cntr =1;
  }

   echo "
   <td>$aircraftTailNo </td>
   <td>$aircraftType   </td>
   <td>$servicingType  </td>
   <td>$airFrameHours  </td>
   <td>$defectInfo     </td>
   <td>$rectifications </td>
   <td>$comments       </td>
   <td>$relatedDoc     </td>
   ";
  $cntr++;  //increment the counter
}//end while

echo "</tr></table>";
}

?>

[vaaaska]

How I would do this though is create a variable that holds the extra part of your search.

if ($_POST['pulldown'] != '') {
  $pulldown = " AND field_name LIKE '%$_POST[pulldown]%' ";
} else {
  $pulldown = '';
}

// insert into your query - works whether it's selected or not

$rs = mysql_query("SELECT cells FROM table WHERE field = '$whatever' $pulldown ORDER BY id DESC");

This is an extremely crude example to just explain an idea... 😉

[NoobPHP]

Thanks for the reply, vaaaska..
I did as you suggested..
However, It returned a parse error statement - Parse error: parse error in c:\inetpub\wwwroot\searchAircraftTailNo2.php on line 99

Line 99 - if ($_GET['AircraftType']))

<?php
if ($POST['ServicingType'] != '') {
$pulldownServicingType = " AND ServicingType LIKE '%$POST[ServicingType]%' ";
} else {
$pulldownServicingType = '';
}
?>

<?php
if ($GET['AircraftType']))
{
echo "Search results for: <b>" .$GET['AircraftType']. "</b><br>";
//end highlight
$cntr = 1 ; //counter for the table columns

echo "<table width=90% align=center border=1><tr>
<td align=center bgcolor=#00FFFF>AirCraft Tail No.</td>
<td align=center bgcolor=#00FFFF>AirCraft Type</td>
<td align=center bgcolor=#00FFFF>Servicing Type</td>
<td align=center bgcolor=#00FFFF>Airframe Hours</td>
<td align=center bgcolor=#00FFFF>Defect Info</td>
<td align=center bgcolor=#00FFFF>Rectifications</td>
<td align=center bgcolor=#00FFFF>Comments</td>
<td align=center bgcolor=#00FFFF>Related Documents</td>
</tr><tr>";

$queryAircraftTypeDisplay = "SELECT * from report where AircraftType='".$_GET['AircraftType']."' $pulldownServicingType";
//echo $queryAircraftTypeDisplay;
$resultAircraftTypeDisplay = mysql_query($queryAircraftTypeDisplay) or die ("couldn't execute query ".mysql_error());
while ($row = mysql_fetch_array($resultAircraftTypeDisplay))
{

	//now let's make the keywords bold. To do that we will use preg_replace function.
	//Replace field

$aircraftTailNo = $row[ '1' ] ;
$aircraftType = $row[ '2' ] ;
$servicingType = $row[ '3' ] ;
$airFrameHours = $row[ '4' ] ;
$defectInfo = $row[ '5' ] ;
$rectifications = $row[ '6' ] ;
$comments = $row[ '7' ] ;
$relatedDoc = $row[ '8' ] ;

//if there are 7 cols, write the <tr> tag and reset the counter
if ($cntr == 2)
{
echo "</tr><tr>";
$cntr =1;
}

echo "
<td>$aircraftTailNo </td>
<td>$aircraftType </td>
<td>$servicingType </td>
<td>$airFrameHours </td>
<td>$defectInfo </td>
<td>$rectifications </td>
<td>$comments </td>
<td>$relatedDoc </td>
";
$cntr++; //increment the counter
}//end while

echo "</tr></table>";
}

?>

[vaaaska]

Line 99 has an extra )

Line 99 - if ($_GET['AircraftType']))

// should be

Line 99 - if ($_GET['AircraftType'])

[NoobPHP]

:o I removed the extra )
I get another parse error..

Parse error: parse error, expecting ','' or')'' in c:\inetpub\wwwroot\searchAircraftTailNo.php on line 91

Line91 - if (isset($_POST['ServicingType'] != '')){

<?php
if (isset($POST['ServicingType'] != '')){
$pulldownServicingType = " AND ServicingType LIKE '%$POST[ServicingType]%' ";
} else {
$pulldownServicingType = '';
}
?>