php - 在一个查询中从多个表中选择记录

Question

我有 4 个彼此不相关的表。我想使用一个查询而不是 4 个查询从这些表中获取几条记录。

目前这是通过 4 个查询完成的。代码:

<?php

$result1 = $mysqli->query("SELECT val1, val2 FROM `table1` WHERE id='1'") or die(mysqli_error());
    $row1 = mysqli_fetch_array($result1, MYSQLI_ASSOC);
    $val1 = $row1['val1'];
    $val2 = $row1['val2'];
    
$result2 = $mysqli->query("SELECT val3, val4 FROM `table2` WHERE id='2'") or die(mysqli_error());
    $row2 = mysqli_fetch_array($result2, MYSQLI_ASSOC);
    $val3 = $row2['val3'];
    $val4 = $row2['val4'];  

$result3 = $mysqli->query("SELECT val5, val6 FROM `table3` WHERE id='3'") or die(mysqli_error());
    $row3 = mysqli_fetch_array($result3, MYSQLI_ASSOC);
    $val5 = $row3['val5'];
    $val6 = $row3['val6'];  
    
$result4 = $mysqli->query("SELECT val7, val8 FROM `table4` WHERE id='4'") or die(mysqli_error());
    $row4 = mysqli_fetch_array($result4, MYSQLI_ASSOC);
    $val7 = $row4['val7'];
    $val8 = $row4['val8'];
    
?>

以及我在一个查询中实现这一目标的不成功方法。代码:

<?php
$resultglobal = $mysqli->query("
SELECT
tab1.val1 AS val1,
tab1.val2 AS val2,
tab2.val3 AS Val3,
tab2.val4 AS val4,
tab3.val5 AS val5,
tab3.val6 AS val6,
tab4.val7 AS val7,
tab4.val8 AS val8

FROM 

table1 as tab1, 
table2 as tab2,
table3 as tab3,    
table4 as tab4

WHERE 

tab1.id='1', 
tab2.id='2', 
tab3.id='3', 
tab4.id='4' 
") or die(mysqli_error());

$rowg = mysqli_fetch_array($resultglobal, MYSQLI_ASSOC);
$val1 = $rowg['val1'];
$val2 = $rowg['val2']; 
$val3 = $rowg['val3'];
$val4 = $rowg['val4'];
$val5 = $rowg['val5'];
$val6 = $rowg['val6']; 
$val7 = $rowg['val7'];
$val8 = $rowg['val8'];   
?>

怎么了？这里有什么解决方案吗？

Answer 1

您只需将 WHERE 子句中的 , 替换为 AND 即可。像这样:

WHERE
tab1.id='1' AND 
tab2.id='2' AND
... 

只要 ID 是唯一的(例如主键)，它就可以正常工作。这是一个隐式的交叉连接。