从外键表中获取第一张图片 [英] Fetch first image from foreign key table

问题描述

我在这里的线程的帮助下创建了一个发布系统:添加带有详细信息(名称、数量、多张图像)的产品并在另一个页面中检索它们在下面，我想让表的最后 8 个条目显示在索引页面上.我能够在第一个表中为每个人显示产品名称和其他功能，但我想显示与该帖子的 product_id 关联的第一张图像(* 图像具有唯一 id 但 product_id 是外键第一个表中的主键 - 来自产品的 product_id).有人帮我写php脚本吗?

I have created a posting system with the help of the thread here: Add a product with details (name,quantity,multiple images) and retrieve them in another page In the following I want to make the last 8 entries of the table show on the index page. I was able to display the name of the product and the other features in the first table for each one, but I would like to display the first image associated with the product_id of that post (* images have unique id but product_id is foreign key for the primary key in the first table - product_id from products). Someone help me with php script?

php 代码:

$sql = SELECT id, name, quantity, description FROM products ORDER BY id DESC LIMIT 8;
$result = mysqli_query($connection, $sql);

html 代码:

 if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
            echo "id: " . $row["id"]. " - Name: " . $row["name"]. " " . $row["quantity"]. "<br>";
            echo "<a href='getProduct.php?id=$row[id]'>See Product</a> <br>";
    }
} else { echo "0 results";
}

表格

CREATE TABLE `products` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  `description` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `products_images` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(11) unsigned DEFAULT NULL,
  `filename` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `products_images_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

抱歉……这是我在 stackoverflow 上的第一篇文章……

Sorry...is my first post on stackoverflow ...

推荐答案

您可以通过两种方式来检索图像.第一个是对每个图像运行另一个查询，同时遍历结果

There are two ways you could go to retrieve the image. The first one would be to run another query for each image, while you're iterating through your results

像这样:

<?php

$sql = "SELECT id, name, quantity, description FROM products ORDER BY id DESC LIMIT 4";
$result = mysqli_query($connection, $sql);

if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. " " . $row["quantity"]. "<br>";
        echo "<a href='getProduct.php?id=$row[id]'>See Product</a> <br>";
        // load the image
        $imageResult = mysqli_query($connection, "SELECT filename FROM products_images WHERE product_id = " . mysqli_real_escape_string($connection, $row["id"]) . " LIMIT 1");
        if (mysqli_num_rows($imageResult) == 1) {
            $imageRow = mysqli_fetch_assoc($imageResult);
            echo "the image filename is: " . $imageRow["filename"];
        }

    }
} else { echo "0 results"; }

我更喜欢的第二个选项是加入"第二个表，它可能如下所示:

The second option, which I would prefer, is to "Join" the second table, which could look like this:

<?php

$sql = "SELECT p.id, p.name, p.quantity, p.description, i.filename FROM products p LEFT JOIN product_images i on i.product_id = p.id ORDER BY p.id DESC LIMIT 4";
$result = mysqli_query($connection, $sql);

if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. " " . $row["quantity"]. "<br>";
        echo "<a href='getProduct.php?id=$row[id]'>See Product</a> <br>";
        if ($row["filename"]) {
            echo "the image filename is: " . $row["filename"];
        }

    }
} else { echo "0 results"; }

我建议您首先阅读 SQL 中的连接(有很多资源，例如:https://www.w3schools.com/sql/sql_join.asp, https://en.wikipedia.org/wiki/Join_(SQL) 或 http://www.sql-join.com/)

I'd recommend you to read about joins in SQL first (there are numerous resources, ex: https://www.w3schools.com/sql/sql_join.asp, https://en.wikipedia.org/wiki/Join_(SQL) or http://www.sql-join.com/)

接下来，我建议您针对名为 SQL 注入的事物保护您的数据库查询.在第一个示例中，我添加了 mysqli_real_escape_string($connection, $row["id"]) 来执行此操作.更好的方法(通常您应该使用这种技术来编写数据库查询！)是使用准备好的语句.您可以阅读有关它们的信息，例如.在这里:https://websitebeaver.com/prepared-statements-in-php-mysqli-to-prevent-sql-injection

The next thing, I'd recommend to you is securing your database queries against a thing called SQL Injection. In the first example I've added mysqli_real_escape_string($connection, $row["id"]) to do this. A better way (and in general you should use this technique to write database queries!) is to use prepared statements. You can read about them ex. here: https://websitebeaver.com/prepared-statements-in-php-mysqli-to-prevent-sql-injection

接下来我想指出的是，您不需要用 PHP 编写所有内容.

The next thing I'd like to point out, is that you don't need to write every thing in PHP.

if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
            echo "id: " . $row["id"]. " - Name: " . $row["name"]. " " . $row["quantity"]; ?><br>
            <a href="getProduct.php?id=<?php echo $row[id]; ?>">See Product</a><br>
    <?php }
} else { echo "0 results"; }

完全有效！无需使用 echo 输出所有 html 代码，只需暂停"其间的 php 内容即可.

Is perfectly valid! No need to output all html code using echo just "pause" the php stuff in between.

最后我想向您介绍 https://phptherightway.com/，它不仅涵盖基础知识"，但也为您指明了更多资源.

Finally I'd like to introduce you to https://phptherightway.com/ which does not only cover the "basics" but also points you to further resources.

这篇关于从外键表中获取第一张图片的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！