Page 1 of 1

Multiple select query- help

Posted: Thu Jul 10, 2003 6:32 am
by rave
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!

Posted: Thu Jul 10, 2003 7:20 am
by Johnm
What do you have so far?

sample Code

Posted: Fri Jul 11, 2003 12:05 am
by rave
Here is part of the code

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 :D

sorry duplicate submission

Posted: Fri Jul 11, 2003 12:23 am
by rave
:oops: sorry!

My browser froze when I made submission, so I thought summision may not have gone thru' and submitted again.

Posted: Fri Jul 11, 2003 7:22 am
by Johnm
No worries,
My browser froze when I made submission
I took care of the double post.

John M