我试图从3个表中获得结果,但它重复了PART_ID并反复显示相同的id.我怎样才能解决这个问题？
PHP
$product_list = "";
$sql = MysqL_query("SELECT * FROM PART,PART_TYPE,RACK");
$productCount = MysqL_num_rows($sql);
if ($productCount >0){
while($row= MysqL_fetch_array($sql)){
$id = $row["PART_ID"];
$PART_DESC = $row["PART_DESC"];
$SERIAL_NUM = $row["SERIAL_NUM"];
$RACK_NUM = $row["RACK_NUM"];
$PART_TYPE_ID = $row["PART_TYPE_ID"];
$PART_TYPE_DESC = $row["PART_TYPE_DESC"];
$product_list .= " 结果
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R1S1 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC - edit • delete
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R1S2 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC - edit • delete
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R1S3 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC - edit • delete
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R1S4 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC - edit • delete
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R1S5 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC - edit • delete
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R1S6 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC - edit • delete
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R2S1 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC - edit • delete
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R2S2 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC - edit • delete
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R2S3 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC - edit • delete
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R2S4 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC - edit • delete
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R2S5 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC - edit • delete
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R2S6 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC - edit • delete
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R3S1 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC - edit • delete
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R3S2 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC - edit • delete
PART_ID: 1001 -Power Mac G4 Desktop -Product Type XBO31 1WAJ3B - RACK_NUM R3S3 - PART_TYPE_ID 101 - PART_TYPE_DESC MAC - edit • delete
最佳答案
您应该使用某些条件将表连接在一起,例如：
SELECT PART_ID,PART_DESC,SERIAL_NUM,RACK_NUM,PART.PART_TYPE_ID,PART_TYPE_DESC
FROM PART
INNER JOIN PART_TYPE ON PART.PART_TYPE_ID = PART_TYPE.PART_TYPE_ID
基本上,这将实现从PART表中获取所有行,并且对于我们找到的每一行,将该行与PART_TYPE表中的行匹配(条件是它们具有相同的PART_TYPE_ID).如果在PART表中的给定行中找不到PART和PART_TYPE表之间的匹配项,则该行不会包含在结果中.实际上,这意味着您只能获得具有有效对应零件类型的零件.
注意：使用SELECT *从表中选择所有列通常不赞成,因为它使维护变得困难.如果您要添加,删除或重新排列列,则所有代码都会中断.因此,我更改了您的select语句,以显式检索您引用的列,其顺序与代码中引用的顺序相同.
编辑：我在代码中省略了与RACK表的连接,因为您没有引用RACK.LOCATION列.如果这是一个错误,只需抛出另一个连接：
INNER JOIN RACK ON RACK.RACK_NUM = PART.RACK_NUM
并将LOCATION列添加到SELECT语句中要检索的列的列表中.