View Full Version : Resolved How would I structure a multiple table search?

08-08-2011, 10:03 PM
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:

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

if(preg_match("/[0-9]+/", $_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
//-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
$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>";
echo "Please enter a search query!";

Old Pedant
08-08-2011, 10:19 PM
Can we name your tables? It's terrible to call them Table1, Table2, Table3.

Give them meaningful names:
(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

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:

Table Schools
schoolName VARCHAR

Table SchoolLocations
schoolID INT REFERENCES Schools(schoolID)

Table Programs

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??

08-08-2011, 10:22 PM
...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.

08-08-2011, 10:36 PM
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!

08-08-2011, 10:38 PM
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.

Old Pedant
08-09-2011, 12:18 AM
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.