php - SQL如何让所有图像显示各自的单位

Question

我的问题是如何让多个图像显示在各自的单位信息旁边。我可以打印出下面的所有图像，但无法让每个列表打印出其各自的图像。最初我试图将其作为单个查询来执行，但它只是多次打印出相同的图像，所以我尝试执行两个单独的查询，但它不起作用，我该如何修复它？

// Define the query but only for info:
  $querystats = 'SELECT 8052monticello.UNIT, 8052monticello.SIZE, 8052monticello.id, 8052monticello.PRICE';
  $querystats.= ' FROM 8052monticello'; 

if ($r = mysqli_query($connection, $querystats)) { // Run the query.
    // Retrieve and print every record:
    while ($row = mysqli_fetch_assoc($r)) {

    if ($row['PRICE']){
        // echo "<img src='images/".$row['image_name']."' width='100'>";

        print "<p><h3>{$row['UNIT']} Unit #</h3>    
        {$row['SIZE']} Sq Feet<br>
        {$row['PRICE']} Monthly rent<br>
        <a href=\"edit_UNIT.php?id={$row['id']}\">Edit</a>
        <a href=\"delete_UNIT.php?id={$row['id']}\">Delete</a>
        </p><hr>\n";
    }
    }
} 
//get the images
  $queryimage = 'SELECT photos.image_name, photos.fk_unit, 8052monticello.UNIT';
  $queryimage.= ' FROM photos, 8052monticello';
  $queryimage.= ' WHERE photos.fk_unit= 8052monticello.unit';



if ($r = mysqli_query($connection, $queryimage)) { // Run the query.
    // Retrieve and print every record:
    while ($row = mysqli_fetch_assoc($r)) {
    if ($row['image_name']){
    echo "<img src='images/".$row['image_name']."' width='100'>";
     }
    }
}

Answer 1

您可以使用 JOIN在您的查询中加入 monticello 和 photos 表:

$query = '
SELECT 
    m.UNIT, m.SIZE, m.id, m.PRICE,
    p.image_name
FROM 8052monticello m
INNER JOIN photos p ON p.fk_unit = m.unit'; 

现在您可以在循环中访问 $row['image_name']。

while ($row = mysqli_fetch_assoc($r)) {
    if ($row['PRICE']){
        print "<img src=\"images/{$row['image_name']}\" width='100'>
        <p><h3>{$row['UNIT']} Unit #</h3>    
        {$row['SIZE']} Sq Feet<br>
        {$row['PRICE']} Monthly rent<br>
        <a href=\"edit_UNIT.php?id={$row['id']}\">Edit</a>
        <a href=\"delete_UNIT.php?id={$row['id']}\">Delete</a>
        </p><hr>\n";
    }
}

更新:对于每一行，都会从数据库中获取图像。

$query = "SELECT 
      m.UNIT, m.SIZE, m.id, m.PRICE
    FROM 8052monticello m "; 
$res = mysqli_query($connection, $query);
if (!$res){
   // throw error here
}
$query2 = "SELECT p.image_name 
     FROM photos p 
     WHERE p.fk_unit = '%s' ";
while ($row = mysqli_fetch_assoc($res)) {
    $res2 = mysqli_query($connection, sprintf($query2, $row["UNIT"]));
    while($img = mysqli_fetch_assoc($res2)) {
       echo "<img src=\"images/{$img['image_name']}\" width='100'>";
    }
    print "<p><h3>{$row['UNIT']} Unit #</h3>    
        {$row['SIZE']} Sq Feet<br>
        {$row['PRICE']} Monthly rent<br>
        <a href=\"edit_UNIT.php?id={$row['id']}\">Edit</a>
        <a href=\"delete_UNIT.php?id={$row['id']}\">Delete</a>
        </p><hr>\n";
}