|
|
Posted by Bob Stearns on 10/13/98 11:17
Tony wrote:
> "Bob Stearns" <rstearns1241@charter.net> wrote in message
> news:agTme.17634$cP2.440@fe06.lga...
>
>>Mark & Ingrid Nugent wrote:
>>
>>>One table of my database could contain multiple values eg
>>>student_courses, each student could have done many different courses.
>>>
>>>For a search engine, I want to be able to tick a checkbox and get all the
>>>students who have done that course. However, if I tick two or more
>>>checkboxes I want to get only the students that have done all the
>>>selected courses, not the students that have done just any of the
>>>selected courses.
>>>
>>>I think that means I will have to run a query "SELECT student_id FROM
>>>student_courses WHERE course_id = $id OR course_id = $id etc etc , and
>>>filter the results with PHP.
>>>Has anyone got a neat solution? Is there any way that MySQL could refine
>>>the query results?
>>>
>>>Mark
>>
>>SELECT student_id FROM student_courses WHERE course_id = $id1
>>INTERSECT
>>SELECT student_id FROM student_courses WHERE course_id = $id2
>>
>>should do what you want.
>
>
> Umm - according to the MySQL website (
> http://dev.mysql.com/doc/mysql/en/todo-sometime.html ), INTERSECT is not yet
> supported - it's on the To-Do list. Is that information incorrect?
>
> I'll have to give it a try on my server later :)
>
> Mark:
>
> As I understand it, you are going to have a list of checkboxes that may or
> may not be checked, right? Let's say you have 3 checkboxes - if one is
> checked, you want everyone who has taken that one course. If two are
> checked, you want everyone who has taken BOTH courses, not one or the other,
> right?
>
> What is the actual database structure? Is it set so that you have a separate
> entry for each course (i.e. a student who takes 3 courses would have 3 rows
> entered in the database)?
>
>
I didn't see a MySQL requirement. Another approach would be (in pseudo
code, adjust for your cgi):
DELETE temp
INSERT INTO temp
SELECT student_id FROM student_courses WHERE course_id = $id[1]
for(i=2;i<=number_of_ids;i++) {
DELETE temp2
INSERT INTO temp2
SELECT student_id FROM student_courses
WHERE course_id = $id1
AND student_id IN (
SELECT student_id FROM temp)
DELETE TEMP
INSERT INTO TEMP
SELECT student_id FROM temp2 WHERE course_id = $id[$i]
}
The result is in temp, no matter how many boxes are checked. Temp and
temp2 are tables with one column. student_id. A faster algorithm would
switch between temp and temp2 rather than copying temp2 to temp each pass.
In SQLs that support them, and where there a limited number of $id
values this can be done with subqueries:
SELECT student_id FROM student_courses WHERE course_id = $id[1]
for one id
SELECT student_id FROM student_courses
WHERE course_id = $id[1]
AND student_id IN (
SELECT student_id FROM student_courses
WHERE course_id = $id[2])
for two id values
SELECT student_id FROM student_courses
WHERE course_id = $id[1]
AND student_id IN (
SELECT student_id FROM student_courses
WHERE course_id = $id[2])
AND student_id IN (
SELECT student_id FROM student_courses
WHERE course_id = $id[3])
for three id values
Navigation:
[Reply to this message]
|