Fetch first image from foreign key table

Question

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 code:

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

html code:

 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";
}

Tables

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;

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

Welcome to Stack Overflow! Please edit your question and include the schema of the products table and the images table. I think what you want can be done mostly in the SQL statement. You mention that you want the "last" 8 rows, but your query uses a limit of 4. — Sloan Thrasher, Apr 16 '19 at 16:22

score 1 · Accepted Answer · answered Apr 16 '19 at 16:40

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

Something like this:

<?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"; }

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/)

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

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"; }

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

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.

score 0 · Answer 2 · edited Apr 16 '19 at 16:37

0

If you want to recover the image associated with the product, you must do it using the WHERE cluase or using the JOINS for example:

SELECT 
    id, 
    name, 
    quantity, 
    description 
FROM products, image 
WHERE products.id = image.id 
ORDER BY id DESC 
LIMIT 4;

or also

SELECT 
    id, 
    name, 
    quantity, 
    description 
FROM products 
NATURAL JOIN image 
ORDER BY id DESC 
LIMIT 4;

But that means that the idImage must be as a foreign key in the products table

edited Apr 16 '19 at 16:37

Sloan Thrasher

4,953
3
22
40

answered Apr 16 '19 at 16:26

Diego Hernández

66
9

The OP states that the images table has the product_id as a FK to the products table. – Sloan Thrasher Apr 16 '19 at 16:33
The first form is not current, and shouldn't be used. Use the JOIN syntax instead (as in your 2nd example) – Sloan Thrasher Apr 16 '19 at 16:34
Your join has no ON clause. Also, the image filename/path is missing. – Sloan Thrasher Apr 16 '19 at 16:38
@SloanThrasher a `NATURAL JOIN` doesn't need an `ON` clause, as it will join on columns with the same id. However usually the `products.id` would get joined with `image.product_id` and not `image.id` as the natural join does. – wawa Apr 16 '19 at 16:42
1

@wawa: Of course, but if an index is changed later that breaks the natural join, it can be a difficult bug to track down. – Sloan Thrasher Apr 16 '19 at 18:29

Fetch first image from foreign key table

2 Answers2

Linked