MySQL / PHP Programming (was, what comes next)

[motorollin]

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

[buzz]

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

You can do it in sql. (generally) if you ever have a problem where you think you need to get lots of results and process in php, then the db design is probably not quite right or you need to rethink the query.

in your case you can alias the requirements table.

requirements B,
requirements C,

and join against it also.

[motorollin]

(generally) if you ever have a problem where you think you need to get lots of results and process in php, then the db design is probably not quite right or you need to rethink the query.

I thought as much

in your case you can alias the requirements table.

requirements B,
requirements C,

Ahh, so I would do something like:

SELECT
A.*

FROM
solutions A,
requirements B,
requirements C

WHERE
A.problem_id = 1
AND B.solution_id = A.solution_id
AND (B.requirement_id = 1 AND C.requirement_id = 2)

Would that do the trick?

and join against it also.

Not sure what this means, so I'l read about joining in SQL. I've heard of it, but never used it.

[Karlos]

@moto

You can join a single table multiple times in a query by using an alias name for each invocation of the join. However, building up large queries with a lot of joins and dependencies is not often a good idea, especially where these are generated from code. It's possible to end up generating queries that can never actually complete if you aren't careful.

[buzz]

Would that do the trick?


Not sure what this means, so I'l read about joining in SQL. I've heard of it, but never used it.

almost but you need to join the C table. You may have only heard of joins but you are already using them

AND B.solution_id = A.solution_id

is a join (implicit)

so join the C table also to A

I recommend some reading up a bit of sql and database design. will save time later on not making mistakes early on in your schema.

[buzz]

Could you give me an example of a situation where this might happen? Surely the worst case scenario is that no records are returned?

well say you had facilities at a park and you wanted to match all parks with certain facilities, you could use this and it wouldn't be a huge problem as the facilities table would never be that big etc, but if you had many thousands of facilities and a user chose 1 thousand to match from you would have a thousand joins and that just isnt going to work well. so in that case you would need to rethink how you will store/structure the data and so on.

[motorollin]

well say you had facilities at a park and you wanted to match all parks with certain facilities, you could use this and it wouldn't be a huge problem as the facilities table would never be that big etc, but if you had many thousands of facilities and a user chose 1 thousand to match from you would have a thousand joins and that just isnt going to work well. so in that case you would need to rethink how you will store/structure the data and so on.

I see. Well I don't think the tables will get that big in the case. The following MySQL query works:

Code: [Select]


SELECT DISTINCT
A.name,
B.name

FROM
problems A,
solutions B,
requirements D,
requirements E,
requirements F

WHERE
A.problem_id = 1
AND B.problem_id = A.problem_id
AND D.solution_id = B.solution_id
AND E.solution_id = B.solution_id
AND
(
(D.object_id = 1 AND E.object_id = 2)
OR (D.object_id = 3 AND E.object_id = 4 AND F.object_id = 5)
)


Each line between the brackets in the last "AND" match for the selections the user has made. I tested this with the following data:

• Problems table contains one entry "Copying files from PC to Amiga" (problem_id 1).
• Solutions table has two corresponding entries: "Burn the files to a CD" and "Copy the files to a floppy"
• The former solution has two requirements: "PC CD burner" and "Amiga CD ROM" (object_ids 1 and 2).
• The latter solution has three requirements: "PC floppy drive", "Amiga floppy drive" and "AmigaOS 2.0+" (object_ids 3, 4 and 5).

By adding and removing lines between the brackets in the last AND section of the query, different sets of requirements can be matched, and thus solutions found depending on what equipment the user has available to them. It should be fairly trivial to get PHP to structure the query, just adding as many aliases to the query as there are possible requirements for any given solution to that problem, and then adding the requirement matching bits depending on what the user has got available to them.

Thanks for the help guys. I should be able to knock together a front end for this now to allow people to submit problems/solutions and specify their requirements.

[Speelgoedmannetje]

Hm, my quick reply on this is you want an inner join
(SELECT * FROM Problems A INNER JOIN solution b ON A.problem_id = B.problem_id)

I take it for granted you have explicitly defined the relations, to ensure referential integrity?