Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Jul 2011
    Posts
    43
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Question How would I structure a multiple table search?

    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. 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 records at most)

    I need to create a way to searching two tables 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:


    Code:
        <form  method="post" action="zipsearch.php?go"  id="searchform">
          <input  type="text" name="zip">
          <input  type="submit" name="submit" value="Search">
        </form>
    PHP Code:
    <?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!"
          } 
          } 
          } 
        
    ?>
    Last edited by dareichmann; 08-08-2011 at 10:49 PM. Reason: Old Pedant is very helpful. I am learning a lot! Thanks!

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,602
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    Can we name your tables? It's terrible to call them Table1, Table2, Table3.

    Give them meaningful names:
    Schools
    SchoolPrograms
    SchoolCosts
    (and I see no reason at all to have an ID field for any table but Schools)


    And how will you let people choose program type? I would think you'd want to do that via a <select> which gets its <option>s from something like
    Code:
    SELECT DISTINCT program_type FROM SchoolPrograms
    And will you let people choose a maximum cost? Or a range of cost? Or???

    It strikes me that your first table is not itself well normalized. If a school has multiple locations, the school should occur only once and you should have another SCHOOL_LOCATIONS table that is many-to-one to hold just the locations.

    Actually, the more I look at it, the more I'm convinced of this. If you only have 50 records in SchoolCosts, then that implies that there can't be more than 50 actual Schools.

    So your table design is hosed.

    If each school has a UNIQUE cost, then the cost should be in the Schools table.

    PROBABLY you should have:
    Code:
    Table Schools
        schoolID  INT PRIMARY KEY
        schoolName  VARCHAR
        url  VARCHAR
        cost  NUMERIC 
    
    Table SchoolLocations
        schoolID INT REFERENCES Schools(schoolID)
        zipcode
        address
    
    Table Programs
        programID INT PRIMARY KEY
        programName
        programDescription
    
    Table SchoolPrograms
        schoolID INT REFERENCES Schools(schoolID)
        programID INT REFERENCES Programs(programID)
    At least, that would be my first take on the likely better design. Convince me otherwise??

  • Users who have thanked Old Pedant for this post:

    dareichmann (08-08-2011)

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Quote Originally Posted by dareichmann View Post
    ...many, many records due to the enormous amount of zip codes associated with some of the schools... think 10,000's)
    Just to set your mind at ease, this is a small table, negligible in size to mysql. When you talk hundreds of millions of rows then you are talking very large/enormous.

  • #4
    New Coder
    Join Date
    Jul 2011
    Posts
    43
    Thanks
    11
    Thanked 0 Times in 0 Posts
    I only named them Table1, 2, 3, for sake of example...


    Table1 is actually zip_search, table2 is... well... it doesn't matter now. I like your organization much better than mine. I'm going to restructure everything and then report back. Thanks!


    I am much more familiar with HTML and CSS. Database creation and management was something I was more or less tossed into. But I understand you ways clearly! It makes much more sense than mine! Thank you!

  • #5
    New Coder
    Join Date
    Jul 2011
    Posts
    43
    Thanks
    11
    Thanked 0 Times in 0 Posts
    The form and search is for internal use only. I put cost instead of payout. Our company is paid by the leads it generates for the schools. We want to submit leads to the schools with the highest payout rate per lead first.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,602
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    LOL! Well, that's easy enough. Just add the payout field to the SCHOOLS table (along with cost) and ORDER BY SCHOOLS.PAYOUT DESC in the final stage.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •