Usually most queries are like this:
SELECT column FROM table WHERE value = 'value';
or something like that.
But this is my scenario.
I have an online directory that allows people to search for specific data from it. The search form contains a drop down menu and 2 input boxes. The dropdown menu contains values, each value represents different database tables in one single database. The other 2 input boxes will allow the person to search for values in separate databases.
example: if a person searches for "bachelors degree" in "marketing" in "miami", miami is a value that belongs in one table, marketing is a value that belongs in another table, and bachelors degree is a database table itself in which "marketing" is listed in. now, "marketing" could also be listed in the "diplomas" database table, BUT if the person searches for "bachelors degrees", the SQL query will only query the "bachelors degree" table for the "marketing" value.
The problem I am having is coming up with the right SQL query to run.
Ideally, it should be an if, else statement.
Example:
If "bachelors degree" is selected,
then the SQL will query the first database table to find "miami", then it will query the bachelors degree table to find "marketing", then find the name of the college that contains both "miami" and "marketing" (under the bachelors degree table), then it will display a list of every college in Miami that offers a bachelors degree in marketing.
Here is the code I have:
$query = $_GET['submit'];
$userSelection = filter_input(INPUT_POST, "degreetype"); //degreetype is the name attribute on the dropdown menu
if ($userSelection == "Bachelors Degree" ){
$query = $db->prepare("
SELECT b.Name
, b.BachelorsDegrees
, c.City
FROM BachelorsDegrees b
LEFT
JOIN College_Data c
ON c.City = b.Name
WHERE b.BachelorsDegrees = :SelectedDegree
&& c.City = :SelectedCity
");
$query->bindValue(':SelectedDegree', filter_input(INPUT_POST, "Course"));
$query->bindValue(':SelectedCity', filter_input(INPUT_POST, "City"));
//$rows = $query->fetchAll(PDO::FETCH_ASSOC);
$query->execute();
//} catch (PDOException $e) {
} foreach ($query as $row){
echo 'College Name: ' .$row['Name'];
echo '<br /> Accreditation: ' .$row['Accreditation'];
echo '<br /> Term Type: '.$row['TermType'];
} else {
echo "Unable to fetch Degrees!: ".$e->getMessage()."<br/>";
}
For clarification:
College_Data = a table with all of the colleges data (except courses) BachelorsDegrees = a table with all of the bachelor degree courses of every college Name = Name of the college BachelorsDegrees = (if this is repeated...eg:BachelorsDegrees.BachelorsDegrees.....this is a column within the BachelorsDegrees table that contains the courses of the colleges.
