Welcome, Guest. Please login or register.

Author Topic: MySQL / PHP Programming (was, what comes next)  (Read 3957 times)

Description:

0 Members and 1 Guest are viewing this topic.

Offline motorollinTopic starter

  • Hero Member
  • *****
  • Join Date: Nov 2005
  • Posts: 8669
    • Show all replies
MySQL / PHP Programming (was, what comes next)
« on: June 11, 2009, 10:23:30 AM »
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 :)
Code: [Select]
10  IT\'S THE FINAL COUNTDOWN
20  FOR C = 1 TO 2
30     DA-NA-NAAAA-NAAAA DA-NA-NA-NA-NAAAA
40     DA-NA-NAAAA-NAAAA DA-NA-NA-NA-NA-NA-NAAAAA
50  NEXT C
60  NA-NA-NAAAA
70  NA-NA NA-NA-NA-NA-NAAAA NAAA-NAAAAAAAAAAA
80  GOTO 10
 

Offline motorollinTopic starter

  • Hero Member
  • *****
  • Join Date: Nov 2005
  • Posts: 8669
    • Show all replies
Re: (RFD) Which would you prefer next?
« Reply #1 on: June 11, 2009, 01:33:22 PM »
Quote from: buzz;510580
(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 ;)

Quote from: buzz;510580
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?

Quote from: buzz;510580
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.
Code: [Select]
10  IT\'S THE FINAL COUNTDOWN
20  FOR C = 1 TO 2
30     DA-NA-NAAAA-NAAAA DA-NA-NA-NA-NAAAA
40     DA-NA-NAAAA-NAAAA DA-NA-NA-NA-NA-NA-NAAAAA
50  NEXT C
60  NA-NA-NAAAA
70  NA-NA NA-NA-NA-NA-NAAAA NAAA-NAAAAAAAAAAA
80  GOTO 10
 

Offline motorollinTopic starter

  • Hero Member
  • *****
  • Join Date: Nov 2005
  • Posts: 8669
    • Show all replies
Re: (RFD) Which would you prefer next?
« Reply #2 on: June 11, 2009, 03:31:08 PM »
Quote from: buzz;510597
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.
Code: [Select]
10  IT\'S THE FINAL COUNTDOWN
20  FOR C = 1 TO 2
30     DA-NA-NAAAA-NAAAA DA-NA-NA-NA-NAAAA
40     DA-NA-NAAAA-NAAAA DA-NA-NA-NA-NA-NA-NAAAAA
50  NEXT C
60  NA-NA-NAAAA
70  NA-NA NA-NA-NA-NA-NAAAA NAAA-NAAAAAAAAAAA
80  GOTO 10