php - 查询多个表，显示错误

Question

<?php

DEFINE ('DB_USER', 'username');
DEFINE ('DB_PASSWORD', 'password');
DEFINE ('DB_HOST', 'xxxxxxxxxxxxxxxx');
DEFINE ('DB_NAME', 'bigbobsveterinarysurgery');


$dbc = @mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) OR die ('Could not connect, check the details ' . mysqli_connect_error());
mysqli_set_charset($dbc, 'utf8');
$q = "SELECT dog_name, owner_name, vet_date, Breed, cost, address, contact_number FROM owner, dogs ORDER BY dog_name ASC";      
$r = @mysqli_query ($dbc, $q);


    echo '<table>
    <tr>
    <th scope="col">Dog</th>
    <th scope="col">Owner</th>
    <th scope="col">Entry Date</th>
    <th scope="col">Breed</th>
    <th scope="col">Cost</th>
    <th scope="col">Address</th>
    <th scope="col">Contact Number</th>
    </tr>'
    ;


    while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
        echo '<tr><td>' . $row['Dog_name'] . '</td><td>' . $row['owner_name'] . '</td><td>' . $row['vet_Date'] . 
        '</td></tr>' . $row['Breed'] . '</td></tr> ' . $row['cost'] . '</td></tr> '. $row['address'] . '</td></tr> ' . $row['contact_number'] . '</td></tr> ';
    }

echo '</table>';
?>

它将所有数据的品种、价格和联系电话显示在一大堆中。我想要的表格格式可以在下面找到，但是每只狗都列出给其他每个主人，并且所有其他列都是空白的。发布日期未显示，但我认为这是我从 Excel 工作表导入数据且日期格式错误时出现的问题

编辑

我已将其更新为:

while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
        echo '<tr><td>' . $row['Dog_name'] . '</td><td>' . $row['owner_name'] . '</td><td>' . $row['vet_Date'] . 
    '</td><td>' . $row['Breed'] . '</td><td> ' . $row['cost'] . '</td><td> '. $row['address'] . '</td><td> ' . $row['contact_number'] . '</td></tr> ';
}

这从一开始就阻止了整个困惑，所有数据现在都格式化在表格中。然而，每只狗仍然与每个主人相匹配。这是这篇文章中的代码还是数据库的问题？

Answer 1

我认为你需要连接两个表

$q = "SELECT dog_name, owner_name, vet_date, Breed, cost, address, contact_number 
      FROM owner o
      LEFT JOIN dogs d ON o.id = d.owner_id 
      ORDER BY d.dog_name ASC";      
// make sure you join on valid columns.
// also, if there is a column that appears in both tables, 
// to avoid ambiguity, prefix it with the corresponding table alias
$r = @mysqli_query ($dbc, $q);

echo "<table>
    <tr>
    // skipping this so it doesn't eat up too much space
    </tr>";

// also, every `$row[something]` should be placed inside a `<td></td>`, like so:
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
    echo '<tr>
            <td>' . $row['Dog_name'] . '</td>
            <td>' . $row['owner_name'] . '</td>
            <td>' . $row['vet_Date'] . '</td>
            <td>' . $row['Breed'] . '</td>
            <td>' . $row['cost'] . '</td>
            <td>' . $row['address'] . '</td>
            <td>' . $row['contact_number'] . '</td>
        </tr> ';
}

从两个表中进行选择会导致它们之间产生笛卡尔积。
使用 join，您只能获得相应的行(根据 ON 子句)。

在您的特定情况下，使用join将维护狗与主人的关系，而使用FROMowner, dogs，您将获得每个主人的所有狗，这可能不是你想要的。