Fetch First Image From Foreign Key Table

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 wa

Solution 1:

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><ahref="getProduct.php?id=<?phpecho$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.

Solution 2:

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 
ORDERBY id DESC 
LIMIT 4;

or also

SELECT 
    id, 
    name, 
    quantity, 
    description 
FROM products 
NATURALJOIN image 
ORDERBY id DESC 
LIMIT 4;

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