There's a lot of text here, but it goes fast and there is an SQL query below. Also posting table structure. Any help - very appreciated.
I am trying to display a sub-set of a result set and I can't figure out how to get it to work. The code below sort of works, but doesn't give me the correct results. It seems to skip some records, but not consistently?
Here's my table structure and my query. We are using MySQL v. 3.23.47.
scholarships table:
scholarship_id | scholarship title | sponsor id | deadline
sponsors table:
sponsor_id | sponsor name
fub table (funding may be used by these people):
fub_id | people
(eg., first year college students, part time students, etc.)
fuf table (funding may be used for these programs):
fuf_id | program
(eg., graduate programs, undergrad programs, etc.)
residential_requirement table (scholarship targets these locations):
residence_id | city
(eg., reno, los angeles, san francisco, etc.)
target_audiences table (scholarship targets these audiences):
audience_id | audience
(eg., veterans, women, first time college students, etc.)
lookup_scholarship_fuf table (links scholarship and fuf data)
scholarship_id | fuf_id
lookup_scholarship_fub table (links scholarship and fub data)
scholarship_id | fub_id
lookup_scholarship_audience table (links scholarship and audience data)
scholarship_id | audience_id
lookup_scholarship_residence table (links scholarship and residence data)
scholarship_id | residence_id
We want to set this up so that people first are presented with a search form that provides all of the residential_requirement and target_audiences options as check boxes. Submitting those selections brings up a result set that we then want to let people limit down further by selecting any/all options from the fub and/or fuf tables. that's where we're stuck.
We're able to get the first result set using the query below. We are able to display the fub and fuf data in the result page by doing a second and third query/while loop on their lookup tables based on the scholarship_id returned in the first result set.
But we can't figure out how to get just a sub-set of that result set based on fub and fuf selections. We'd like to show the result page and give people the option to "Narrow your results:" and then they check off their fub and fuf limiters.
I've tried including fub and fuf in the query below too - but that totally doesn't work, and has even brought down a 3rd party server. Part of the trouble, I think, is that the fub and fuf info will aways use AND, not or. Whereas audience and residence info will always use OR -- the more that applies to you, the more results you'll get.
Here's the query that works for the first part. Where do we go from here?? Any help with the second query, or changes to the first query to make this all happen in one shot - much appreciated!
SELECT
lookup_scholarship_residence.residence_id,
lookup_scholarship_audience.audience_id,
scholarships.scholarship_title,
scholarships.scholarship_id,
scholarships.deadline_day,
scholarships.deadline_month,
scholarships.deadline_year,
sponsors.org_name
FROM
lookup_scholarship_audience,
lookup_scholarship_residence,
scholarships,
sponsors
WHERE
scholarships.sponsor_id = sponsors.sponsor_id
AND
lookup_scholarship_audience.scholarship_id = scholarships.scholarship_id
AND
lookup_scholarship_residence.scholarship_id = scholarships.scholarship_id
GROUP BY
scholarships.scholarship_id
HAVING
lookup_scholarship_residence.residence_id = '1'
AND
lookup_scholarship_audience.audience_id = '1'
OR
// whatever checkboxes were selected for audience, eg:
lookup_scholarship_audience.audience_id = 'x' OR lookup_scholarship_audience.audience_id = 'y' OR
// whatever checkboxes were selected for residence, eg:
lookup_scholarship_residence.residence_id = 'x' OR
lookup_scholarship_residence.residence_id = 'y'