How to write a query for PHP and MySQL?

PHP / MySql query and/or script

• tablePkId | userFkId | colorFkId | ----------+----------+-----------| 1 | 11 | 22 | -- ----------+----------+-----------| 2 | 12 | 25 | YES ----------+----------+-----------| 3 | 13 | 25 | YES ----------+----------+-----------| 4 | 14 | 25 | -- ----------+----------+-----------| 5 | 14 | 25 | -- ----------+----------+-----------| 6 | 13 | 23 | no ----------+----------+-----------| 7 | 12 | 22 | YES ----------+----------+-----------| 8 | 11 | 23 | -- ----------+----------+-----------| 9 | 11 | 24 | -- ----------+----------+-----------| 10 | 12 | 24 | no ----------+----------+-----------| 11 | 13 | 25 | YES ----------+----------+-----------| 12 | 14 | 21 | -- ----------+----------+-----------| 13 | 14 | 25 | -- ----------+----------+-----------| 14 | 13 | 21 | YES ----------+----------+-----------| 15 | 13 | 22 | YES ----------+----------+-----------| 16 | 13 | 23 | no ----------+----------+-----------| 17 | 12 | 25 | YES ----------+----------+-----------| 18 | 12 | 24 | no ----------+----------+-----------| 19 | 12 | 21 | YES ----------+----------+-----------| 20 | 11 | 21 | -- ---------------------------------| QUESTION: Need a script and/or query (PHP / MySql) that results in a list (array) of all "tablePkId"'s filtered by a dynamically provided array of userFkId's (1-n; for example userFkId 12 and 13) where the applicable users have the same colorPkId. If the userFkId array would be (12,13) the resulting array of tablePkId's in the above example should be (2,3,7,11,14,15,17,19). I am sure there is a simple solution for this... I just don't seem to be able to find it.

• Answer:

  Klausb-ga, Thanks for your interesting question! The problem can be solved with the following SQL query which returns an array of (2, 3, 7, 11, 14, 15, 17, 19) given input of (12, 13). To generalize this answer substitute your input variables for 12 and 13. Also substitute testtable for the name of your table. SELECT DISTINCT T1.tablePkId FROM testtable T1, testtable T2 WHERE (T1.userFkId=12 OR T1.userFkId=13) AND (T2.userFkId=12 OR T2.userFkId=13) AND (T1.userFkId=T2.userFkId-1 OR T2.userFkId=T1.userFkId-1) AND T1.colorFkId=T2.colorFkId GROUP BY T1.tablePkId ORDER BY T1.tablePkId This works by selecting two copies of the same table, allowing you to compare the data in one query, and not requiring PHP. If you need any help or a clarification, don't hesitate to ask! Sincerely, Andyt-ga