How do I structure a multiple table search?

[slabbius]

Hello All!  I just want to start off by saying i am somewhat of a PHP/mySQL newbie, but I do have a very rough understanding of it.  I know far more HTML, CSS, and Java than I do PHP/mySQL... 

 

I am running server version mySQL 5.1.52

 

I am creating several tables for my company to organize the affiliate schools we work with along with their info

 

I need to create a way to search multiple tables and generate a list of results based on several variables. I need to perform a search based on program type (business, culinary arts, psychology, art and design, education, criminal justice, etc) and by zip code. For example, if you are looking for schools that offer criminal justice programs in the 12345 zip code the search would return whatever results matched those variables. I already have a working search with 1 variable and 1 table, but how do I create a multiple table search?

 

Table1 Contains the following fields: id, school, location, zip, url (this table has many, many records due to the enormous amount of zip codes associated with some of the schools... think 10,000's)

 

Table2 will contain these fields: id, school, program type (this table will have a few hundred records)

 

Table3 will contain these fields: id, school, cost (this table will only have about 50-100 records at most)

 

I need to create a way to search by zip code first to determine prospective student eligibility, and then program type to see if the schools in that zip code offer the program. Once there are results from the first two tables, I also need to reference the cost of the school from the third table in descending order.

 

Currently my results are displayed only by school, location, and url, but I want to include program and cost, but I am only using 1 search variable and 1 table. If someone is looking for schools that offer criminal justice programs in the 12345 zip code. The results must show the schools that offer the programs in the given zip code and be arranged by cost in descending order.

 

How would I structure all of this?

 

 

This is my current HTML and PHP for my 1 table, 1 variable search:

 

   <form  method="post" action="zipsearch.php?go"  id="searchform">
      <input  type="text" name="zip">
      <input  type="submit" name="submit" value="Search">
    </form>

<?php  
      if(isset($_POST['submit'])){  
      if(isset($_GET['go'])){  
      if(preg_match("/[0-9]+/", $_POST['zip'])){  
      $zip=$_POST['zip']; 
//connect  to the database  
      $db=mysql_connect  ("localhost", "user",  "pass") or die ('I cannot connect to the database  because: ' . mysql_error());  
      //-select  the database to use  
      $mydb=mysql_select_db("myDatabase");  
      //-query  the database table  
      $sql="SELECT id, school, location, url FROM Table1 WHERE zip LIKE '$zip'"; 
      //-run  the query against the mysql query function  
      $result=mysql_query($sql) or die (mysql_error ()); 
      //-create  while loop and loop through result set 
      while($row=mysql_fetch_array($result)){ 
          $school=$row['school']; 
          $location=$row['location']; 
          $url=$row['url']; 
          $id=$row['id']; 
          $strap = '<a href="' . $row['url'] . '">' . $row['school'] . '</a><br />'; 

  //-display the result of the array 
  echo "<ul>\n"; 
  echo "<li>" .$strap . " </a></li>"; 
  echo "<li>" . $location ."</a></li>"; 
  echo "</ul>"; 
  } 
      }  
      else{  
      echo  "Please enter a search query!";  
      }  
      }  
      }  
    ?>

 

Thanks for taking time to read this!  Any help would be much appreciated. 

[cunoodle2]

You need to use a Join or an inner join.  I'm not the best at making these efficient but I will give you a pretty good start.  Likely there will be someone on the boards that is more of an expert that will be able to give you a more efficient way to do this..

 

SELECT a.school, a.location, a.url, b.program, c.cost
FROM Table1 a INNER JOIN Table2 b
   ON a.school = b.school JOIN Table3 c
   ON b.school = c.school
WHERE a.zip = 90210
ORDER BY c.cost DESC

 

Start with that and see if that works