PDA

View Full Version : Searching problems


grahambucknall
10-16-2008, 11:04 PM
I am developing a database which records information about different types of projects. There are several tables but the ones relevant to this are:
1.projects (contains an id, a regionid, and a typeid as well as the project title)
2.regions (contains regionid and regionname)
3. type (contains typeid and typetext)

I want to be able to search from projects by region and type on the same search page (eg region=london and type=research).
I can do this and it works fine using a drop down list in each of two select boxes in a form. However, both need to have a choice made in them. I want it to work if only one box is selected. (eg region=london but type box left blank)

How do I do that??

davidj
10-16-2008, 11:22 PM
you have to dynamically pass the search criteria into your SQL query but the answer is in the way you join the tables in your SQL Query


SELECT * FROM projects LEFT JOIN regions ON regions.regionid = projects.regionid RIGHT JOIN type ON type.typeid = projects.typeid
WHERE {dynamic query here}

NOTE: above code not tested

grahambucknall
10-21-2008, 02:20 PM
Sorry - it's not quite as simple as that and my fault for not giving more detail. The projects could have more than one type so are joined by a junction table (juncid, typeid, projectid). Also each project is associated with a school, the details of which are kept in a schools table (schoolid, schoolname). For the results of the search I want to see the project name, type, region and school and to be able to search by region and type but have either selection box left blank and still get a positive result (ie don't select a type but select london as region gives all projects in london.)
The code I have in the recordset is:
SELECT juncid, project.title, region.region, type.type, schools. Name
FROM juncprojecttype INNER JOIN activity ON juncprojecttype.projectid=project. projectid
INNER JOIN schools ON schools.schoolid=project.schoolid
INNER JOIN region ON region.regionid=schools.regionid
INNER JOIN projecttype ON projecttype.typeid=juncprojecttype.typeid
WHERE juncprojecttype.typeid = colname AND schools.regionid=colname2