Multiple select query- help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rave
Forum Newbie
Posts: 7
Joined: Thu Jul 10, 2003 6:32 am

Multiple select query- help

Post 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!
User avatar
Johnm
Forum Contributor
Posts: 344
Joined: Mon May 13, 2002 12:05 pm
Location: Michigan, USA
Contact:

Post by Johnm »

What do you have so far?
Last edited by Johnm on Fri Jul 11, 2003 7:28 am, edited 1 time in total.
rave
Forum Newbie
Posts: 7
Joined: Thu Jul 10, 2003 6:32 am

sample Code

Post 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
rave
Forum Newbie
Posts: 7
Joined: Thu Jul 10, 2003 6:32 am

sorry duplicate submission

Post by rave »

:oops: sorry!

My browser froze when I made submission, so I thought summision may not have gone thru' and submitted again.
User avatar
Johnm
Forum Contributor
Posts: 344
Joined: Mon May 13, 2002 12:05 pm
Location: Michigan, USA
Contact:

Post by Johnm »

No worries,
My browser froze when I made submission
I took care of the double post.

John M
Post Reply