php页面中复杂的mySQL查询 [英] Complicated mySQL query in php page
问题描述
如果你看过我之前的请求，你会发现我是一个使用 php 和 mySQL 的菜鸟.我希望我什至没有这样做，但我已经被那些让我失望的人彻底抛弃了.距离完成"还有 1 天的时间，我必须拿起并修复我拥有的这个系统中没有完全运行的部分！
If you have seen my previous requests you will see I am a TOTAL NOOB with php and mySQL. I wish I wasn't even doing this but I have been dumped in the deep end by people that have let me down. It is 1 day away from "completion" and I am left to pick up and mend the pieces of this system I have which doesn't fully function!
我已尝试尽可能合乎逻辑和清晰地解释这一点.如果您需要澄清，请告诉我，我会尽力而为！
I have tried to explain this as logically and clearly as possible. If you need clarification please let me know and I will do my best!
在此先感谢您的帮助，因为我现在迫切希望完成这件事！
Thanks in advance for any help as I am now desperate to get this thing done!
我有 3 个问题！
问题 1
匹配键
(x)
TABLE1
| C_ID | C_Eth_O (x) |
+--------------------+-------------------------+
| 234 | 8 |
| 341 | 11 |
| 440 | 2 |
TABLE2
| Eth_ID (x) | C1_Eth_O |
+-----------------------+------------------------+
| 2 | Label2 |
| 8 | Label8 |
| 9 | Label9 |
| 11 | Label11 |
我需要在多选列表中列出所有C1_Eth_O"值.用户将从该列表中进行多项选择.提交时，我需要获取Eth_ID"值并返回 TABLE1 中的所有值，其中任何选定的选项C_Eth_O"=Eth_ID".每个C_ID"只能有一个C_Eth_O".
I need to list all the "C1_Eth_O" values in a multi select list. The user will make multiple selections from that list. When submitted I need to get the "Eth_ID" value(s) and return all the value(s) from TABLE1 where any of the selected options "C_Eth_O" = "Eth_ID". Each "C_ID" can only have one "C_Eth_O".
问题 2
Matching Keys
(x)
(o)
TABLE1
| C_ID (x) |
+---------------------+
| 234 |
| 341 |
| 440 |
TABLE3
| Ail_ID (o) | Ali_Label |
+-------------------------+-------------------------+
| 1 | Label1 |
| 2 | Label2 |
| 3 | Label3 |
| 4 | Label4 |
| 5 | Label5 |
| 6 | Label6 |
TABLE4
| CA_ID | C1_ID (x) | Ail1_ID (o) |
+---------------------+------------------------+------------------------+
| 1 | 234 | 1 |
| 2 | 341 | 4 |
| 3 | 341 | 6 |
| 4 | 440 | 2 |
我需要在多选列表中列出 TABLE3 中的所有Ali_Label"值.用户将从该列表中进行多项选择.提交时，我需要从 TABLE3 中获取Ail_ID"值并返回 TABLE1 中的所有值，其中 TABLE4 中的任何选定匹配Ali1_ID"=Ail_ID"&C1_ID" = C_ID".每个C_ID"可以有多个Ail_ID"值
I need to list all the "Ali_Label" values from TABLE3 in a multi select list. The user will make multiple selections from that list. When submitted I need to get the "Ail_ID" value(s) from TABLE3 and return all the value(s) from TABLE1 where any of the selected match in TABLE4 "Ali1_ID" = "Ail_ID" & "C1_ID" = "C_ID". Each "C_ID" can have multiple "Ail_ID" values
问题 3
Matching Keys
(x)
(o)
(-)
TABLE1
| C_ID (x) |
+---------------------+
| 234 |
| 341 |
| 440 |
TABLE3
| Ail_ID (o) | Ali_Label |
+------------------------+-------------------------+
| 1 | Label1 |
| 2 | Label2 |
| 3 | Label3 |
| 4 | Label4 |
| 5 | Label5 |
| 6 | Label6 |
TABLE5
| R_ID | C1_ID (x) | Cf1_ID (-) |
+-----------------------+-----------------------+-----------------------+
| 1 | 234 | 768 |
| 2 | 234 | 854 |
| 3 | 234 | 768 |
| 4 | 440 | 854 |
TABLE6
| CA_ID | Cf_ID (-) | Ail1_ID (o) |
+---------------------+------------------------+------------------------+
| 1 | 768 | 1 |
| 2 | 854 | 4 |
| 3 | 768 | 6 |
| 4 | 880 | 2 |
我需要在多选列表中列出 TABLE3 中的所有Ali_Label"值.用户将从该列表中进行多项选择.提交时，我需要从 TABLE3 中获取Ail_ID"值并返回 TABLE1 中的所有值，其中任何选定的Ali_ID"=Ail1_ID"&"C1_ID" = "C_ID" &Cf1_ID" = Cf_ID".每个C_ID"可以有多个Cf1_ID"值，每个Cf_ID"可以有多个Ail1_ID"值.
I need to list all the "Ali_Label" values from TABLE3 in a multi select list. The user will make multiple selections from that list. When submitted I need to get the "Ail_ID" value(s) from TABLE3 and return all the value(s) from TABLE1 where any of the selected "Ali_ID" = "Ail1_ID" & "C1_ID" = "C_ID" & "Cf1_ID" = "Cf_ID". Each "C_ID" can have multiple "Cf1_ID" values AND each "Cf_ID" can have multiple "Ail1_ID" values.
我让系统返回简单的查询，例如
I have the system working returning simple queries such as
SELECT * FROM table1 WHERE C_ID = 234
但没有多个表和每个 C_ID 的多个结果！
but nothing with multiple tables and multiple results per C_ID!
再次感谢！
推荐答案
问题 1 -
SELECT * FROM table1 INNER JOIN table 2 ON table1.C_Eth_O = table2.Eth_ID
问题 2 -
SELECT * FROM table1 INNER JOIN table4 ON table1.C_ID = table4.C1_ID INNER JOIN table3 ON table3.Ail_ID = table4.Ail1_ID
问题 3 -
SELECT * FROM table1 INNER JOIN table5 ON table1.C_ID = table5.C1_ID INNER JOIN table6 ON table5.Cf1_ID = table6.Cf_ID INNER JOIN table3 ON table3.Ail_ID = table6.Ail1_ID
这篇关于php页面中复杂的mySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
