[SOLVED] Search Query Help

inspireddesign · July 28, 2009

Hello All,

I used the search tutorial by premiso which works great. My problem is that I'm searching more then one table in the database. I have this working but the results it returns needs to include information from the contacts table.

Here is what I have so far which does return the results but not everything:

<?php

$searchSQL = "SELECT * FROM contacts WHERE ";
$searchSQL .= "contact_primary LIKE '%{$searchTermDB}%' OR contact_zip LIKE '%{$searchTermDB}%' ";
$searchSQL .= "UNION ALL SELECT * FROM aircraft WHERE ";
$searchSQL .= "aircraft_n LIKE '%{$searchTermDB}%'";   
$searchResult = mysql_query($searchSQL) or die("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");

?>

The information in the aircraft table relates to the information in the contact table. I have (of course) an ID that relates to both tables. This is the contact_id in the contact table and aircraft_contact in the aircraft table. Both have the same ID's

Can someone tell me how to get the results to show the contact information for the aircraft number entered in the search? I would like to return the contact information only for the aircraft number entered. Here is the code in its entirety: Thanks for any help on this one.

<?php
// Set up our error check and result check array
$error = array();
$results = array();

// First check if a form was submitted. 
// Since this is a search we will use $_GET
if (isset($_GET['search'])) {
   $searchTerms = trim($_GET['search']);
   $searchTerms = strip_tags($searchTerms); // remove any html/javascript.
   
   if (strlen($searchTerms) < 3) {
      $error[] = "Search terms must be longer than 3 characters.";
   }else {
      $searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection.
   }
      
  
   // If there are no errors, lets get the search going.
   if (count($error) < 1) {
      $searchSQL = "SELECT * FROM contacts WHERE ";
      $searchSQL .= "contact_primary LIKE '%{$searchTermDB}%' OR contact_zip LIKE '%{$searchTermDB}%' ";
  $searchSQL .= "UNION ALL SELECT * FROM aircraft WHERE ";
      $searchSQL .= "aircraft_n LIKE '%{$searchTermDB}%'";   
      $searchResult = mysql_query($searchSQL) or die("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");
      

      if (mysql_num_rows($searchResult) < 1) {
         $error[] = "The search term provided {$searchTerms} yielded no results.";
      }else {
         $results = array(); // the result array
         $i = 1;
         while ($row = mysql_fetch_assoc($searchResult)) {
            $results[] = "{$i}: {$row['contact_primary']}<br />{$row['contact_street']}<br />{$row['contact_zip']}<br /><br />";
            $i++;
         }
      }
   }
}

function removeEmpty($var) {
   return (!empty($var)); 
}
?>
<html>
   <title>My Simple Search Form</title>
   <style type="text/css">
      #error {
         color: red;
      }
   </style>
   <body>
      <?php echo (count($error) > 0)?"The following had errors:<br /><span id=\"error\">" . implode("<br />", $error) . "</span><br /><br />":""; ?>
      <form method="GET" action="<?php echo $_SERVER['PHP_SELF'];?>" name="searchForm">
         Search For: <input type="text" name="search" value="<?php echo isset($searchTerms)?$searchTerms:''; ?>" />
         <br />
<br />
         <input type="submit" name="submit" value="Search!" />
      </form>
      <?php echo (count($results) > 0)?"Your search term: {$searchTerms} returned:<br /><br />" . implode("", $results):""; ?>
   </body>
</html>

?>

inspireddesign · July 29, 2009

I've solved my issue by using JOIN which allows me to JOIN the tables by ID. Here's the code I used:

<?php

      $searchSQL = "SELECT * FROM contacts ";
  $searchSQL .= "JOIN aircraft ON (aircraft.aircraft_contact = contacts.contact_id) ";
  $searchSQL .= "JOIN policies ON (policies.policy_contact = contacts.contact_id) ";
      $searchSQL .= "WHERE policies.policy_num LIKE '%{$searchTermDB}%' OR aircraft.aircraft_n LIKE '%{$searchTermDB}%' OR contacts.contact_primary LIKE '%{$searchTermDB}%' OR contacts.contact_zip LIKE '%{$searchTermDB}%' ";   


?>

Sign In

[SOLVED] Search Query Help

Recommended Posts

inspireddesign

Link to comment

Share on other sites

inspireddesign

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information