Hi all,
I have a few select boxes on a form for users to search my database.
Level
Nursery
Kindergarden
Primary
...
Subject
English
Math
Science
...
My db design is as follows I have, a table for teacher details linked by foreign keys to a table called Level and a table called Subject.
What I need is an efficient way to select all levels a user has keyed in the form (be it 1 or more) and match it with my teachers that can teach all these levels and subjects thru searching the columns for the said levels.
eg.
form- level choosen..Nursery, Kindergarden
- subject choosen... English, maths
db- table teacher details linked to level table, where it is stored as such... LevelID, TeacherID, Level
Now the problem is that a teacher can perhaps teach only 1 level or several levels.
AAA kindergarten
AAA Primary
BBB Secondary
BBB Primary
Therefore, I need to search the levels that match the type of levels and number of levels chosen by user in the search form, for each teacher.
Confused, yet? if not please throw me a bone. Thks!
Multiple select query- help
Moderator: General Moderators
sample Code
Here is part of the code
i am sure uget the idea...I am creating a complex query at the end based on user entries, but I cannot use .... eg . level(user selected) like tbl_level AND level2(user selected) like tbl_level AND level3(user selected) like tbl_level... the sql is not correct for multiple searchs of the same column, anyways this was my original approach, I was trying also not to make too many call to the database but the multiple select stop me in my tracks.
Thks 4 your time, JohnM ! Have a nice day
Code: Select all
<?php
// make connection, select database
require_once ("../TutorContact/mysql_connect.inc");
$cond = "AND";
$query = "SELECT DISTINCT TutorID, FirstName, LastName, gender FROM tbl_tutors, tbl_level,tbl_subject ";
//if field are filled or selected by user do a where
if($_POST["FirstName"] != "" || $_POST["LastName"] !=""
|| $_POST["Gender"] !="" ||_POST["level"] !=""||_POST["subject"] !="")
{
$query .= "WHERE ";
if($_POST["FirstName"] != "")
{
$query .= "FirstName LIKE
"".addslashes($_POST["FirstName"]).""";
if($_POST["LastName"] != "" || $_POST["Gender"] != "" || $_POST["Level"] != "" ||$_POST["subject"] != "" ) {
$query .= " $cond " ;
}
} // End of FirstName
//Start of LastName
if($_POST["LastName"] != "")
{
$query .= "LastName LIKE
"".addslashes($_POST["LastName"]).""";
if( $_POST["Gender"] != ""|| $_POST["Level"] != "" ||$_POST ["Subject"] != "" )
{
$query .= " $cond " ;
}
} //End of LastName
// more of the above for each field...u get the idea, i am sure. Below is where it gets sticky.
//Start of Levels
if($_POST["Level[]"] != "")
{
foreach ($_POST["Level"] as $val)
{ $query .= "$val LIKE
"".addslashes ($_POST "tbl_tlevel.LevelCatID"]).""";
}
// add AND condition if needed
if($_POST["Level[]"] != "" )
{
$query .= " $cond " ;
}
*/
} //End of Levels
}
//echo $sql;
$searchResults = mysql_query($query)
or die(mysql_error());
//execute query and print results......
?>i am sure uget the idea...I am creating a complex query at the end based on user entries, but I cannot use .... eg . level(user selected) like tbl_level AND level2(user selected) like tbl_level AND level3(user selected) like tbl_level... the sql is not correct for multiple searchs of the same column, anyways this was my original approach, I was trying also not to make too many call to the database but the multiple select stop me in my tracks.
Thks 4 your time, JohnM ! Have a nice day
sorry duplicate submission
My browser froze when I made submission, so I thought summision may not have gone thru' and submitted again.