-----Original Message----- From: S.D.Price [mailto:S.D....@open.ac.uk] Sent: 18 October 2004 11:14 To: win32 Subject: PHP/SQL to filter data from MySQL

Hi I wonder if anyone can help. I am building a PHP MySQL application which should allow a user to search a staff database.

The search application allows the user to select a query and then select the unit that staff are responsible for. The idea is that staff can be responsible for multiple queries in one area (unit), multiple units or all units.

My table structure looks like this:

Tblstaff - the main staff table with contact details (e.g. ext, email)

Tblquery - stores details of all the queries

Tblunit - stores details of all the units. Unitid '47' represents an ALL group

Tbljob_title - stores details of the job_titles

Tblstaffqueryunit - joins together the Tblstaff table with the Tblunit and Tblquery tables in many to many relationship.

Now this SQL statement works fine. It extract the relevant details out of the tblstaff table based on the query and the unit that the user has selected.

$query = "SELECT CONCAT(tblstaff.fname,' ',tblstaff.sname) AS name, /* concatenate the users first and surname */

tbljob_title.job_title AS job_title, tblstaff.ext AS ext, tblstaff.email AS email, tblstaff.web_page AS web_page, tblunit.unit AS unit

FROM tblstaff, tblunit, tbljob_title, tblstaffqueryunit

WHERE tblstaff.job_titleid = tbljob_title.job_titleid /* join the record from the tblstaff and tbljob_title tables */

AND tblstaffqueryunit.queryid = '$q' /* filter the staff record based on the query selected */ AND tblstaffqueryunit.unitid = '$u' /* filter the staff record based on the unit selected */

AND tblstaffqueryunit.unitid = tblunit.unitid /*join the tblunit and tblstaffqueryunit tables to extract the unit */ AND tblstaffqueryunit.staffid = tblstaff.staffid /*join the tblstaff and tblstaffqueryunit tables to extract the staff record */

However the problem is that many people will be responsible for all areas. That is why I included Unitid '47' for an ALL group in tblunit. Then I can simply allocate '47' to unitid (Foreign Key) in the tblstaffqueryunit table rather than having to complete 47 records for each person for each unit.

However how do I allow someone to search on any of the units and always be presented with staff belonging to the unit they have searched on as well as anyone with a unitid of '47' (responsible for all units)? I think it is probably quite simple in SQL but I can't seem to make progress. Here is what I tried (it was extracting multiple copies of records for the wrong searches).

$query = "SELECT CONCAT(tblstaff.fname,' ',tblstaff.sname) AS name, /* concatenate the users first and surname */

tbljob_title.job_title AS job_title, tblstaff.ext AS ext, tblstaff.email AS email, tblstaff.web_page AS web_page, tblunit.unit AS unit

FROM tblstaff, tblunit, tbljob_title, tblstaffqueryunit

WHERE tblstaff.job_titleid = tbljob_title.job_titleid /* join the record from the tblstaff and tbljob_title tables */

AND tblstaffqueryunit.queryid = '$q' /* filter the staff record based on the query selected */ AND tblstaffqueryunit.unitid = '$u' /* filter the staff record based on the unit selected */

((AND tblstaffqueryunit.queryid = '$q' /* filter the staff record based on the query and unit selected */ AND tblstaffqueryunit.unitid = '$u') AND (AND tblstaffqueryunit.queryid = '$q'

AND tblstaffqueryunit.unitid = '47')) /* include staff records based on the query and 'ALL' group selected */

AND tblstaffqueryunit.unitid = tblunit.unitid /*join the tblunit and tblstaffqueryunit tables to extract the unit */ AND tblstaffqueryunit.staffid = tblstaff.staffid /*join the tblstaff and tblstaffqueryunit tables to extract the staff record */

Can anyone see where I am going wrong? I would greatly appreciate any help.

Steven Price Web Developer