Hmm, I can't work out the correct SQL query for this. I have 4 tables:
Problems (the questions for the KB)
Solutions (the various solutions for each problem)
Objects (things like floppy drives, CD-ROMs, OS version...)
Requirements (links between solutions and objects, to specify the requirements for each solution to each problem)
The problem is that I can't work out how to specify the requirements in the SQL Query, in terms of what the user has actually got (so structuring an SQL query to return the "burn to CD" solution if the user has a CD-ROM on their Amiga and a burner on their PC, and the floppy solution of the user has OS > 2.0 and a floppy drive on both machines). I can't do something like
SELECT
A.*
FROM
solutions A,
requirements B
WHERE
A.problem_id = 1
AND B.solution_id = A.solution_id
AND B.requirement_id = 1
AND B.requirement_id = 2
because the last two lines mean that no record can ever be returned, since the same field cannot contain both values. If I use
AND (B.requirement_id = 1 OR B.requirement_id = 2)
then the solution would be returned even if the user has just one of the requirements.
I don't know whether you can do this in (My)SQL or whether you would have to return all solutions to a given problem, then get PHP to do the checks. Any help appreciated