SQL/PHP join products table on multiple images table

Question

I have two tables (products and images). Images contains with none, one, or even more records per product.

I'd like to join two images (if available) to each product and display them somehow. I'm talking about products list, not a single product. Here comes the problem.

Let's say I have these tables:

Products:

id     | name
1      | Lenovo V310
2      | Adapter 5v
3      | Mousepad
4      | Gamepad Logitech
5      | Nokia 3210

Images:

id      | image_name    | product_id
1       | lenovo1.jpg   | 1
2       | lenovo2.jpg   | 1
3       | lenovo3.jpg   | 1
4       | lenovo4.jpg   | 1
5       | mousepad1.jpg | 3
6       | mousepad2.jpg | 3
7       | gamepad1.jpg  | 4
8       | gamepad2.jpg  | 4
9       | nokia1.png    | 5
10      | nokia2.png    | 5
11      | nokia3.png    | 5
12      | nokia4.png    | 5

I would like my products (products list, not a single item) to be displayed with two images each:

Example: Lenovo V310 + lenovo1.jpg + lenovo2.jpg

I tried it in some ways, but those didn't work. As for example:

    $q = "SELECT products.id, products.name, images.image_name FROM products LEFT JOIN images ON products.id = (SELECT product_id FROM images) LIMIT 6";
    $query = mysqli_query($dbc, $q);
    while ($row = mysqli_fetch_assoc($query)) {
        $id = $row['id'];
        $name = $row['name'];
        $image1 = $row['image_name']; // ain't working also with the query above
        $image2 = $row['image_name']; // how to take the second img????
        ..............

[I think this will helpful for you](https://stackoverflow.com/questions/2451026/minimizing-sql-queries-using-join-with-one-to-many-relationship/2451065#2451065) — Oleksii Semeniuk, Nov 04 '18 at 15:21
Do you have access to latest version of MySQL (8.0.2. and above) ? — Madhur Bhaiya, Nov 04 '18 at 15:28

Ermac · Answer 1 · 2018-11-04T15:34:25.933

2

You could do it using GROUP_CONCAT

SQL:

SELECT p.id,
    p.name,
    GROUP_CONCAT(i.image_name ORDER BY i.image_name) AS images
FROM products p
LEFT JOIN images i ON p.id = i.product_id
GROUP BY p.id
LIMIT 6;

PHP:

$query = mysqli_query($dbc, $q);
while ($row = mysqli_fetch_assoc($query)) {
    $id = $row['id'];
    $name = $row['name'];
    $images = explode(',', $row['images']);
    $image1 = (count($images) > 0) ? $images[0] : null;
    $image2 = (count($images) > 1) ? $images[1] : null;
}

edited Nov 04 '18 at 15:34

answered Nov 04 '18 at 15:22

Ermac

1,181
1
8
12

I would suggest `$images = explode(',', $row['images']);` and then assign `$image1` and `$image2` as needed from that array. Also: why do you have `LIMIT 6` in your query? – Daan Wilmer Nov 04 '18 at 15:28
Agreed @DaanWilmer thanks. Have updated the answer. For the `LIMIT 6` it was in the original query so I kept it. – Ermac Nov 04 '18 at 15:35
I've just tried the solution. Now I have something new to study :) GROUP_CONCAT. Interesting. For a newbie it's not that easy. Regarding your question about the LIMIT 6 - just imagine 6 best selling products on the front page. Where is nothing special about that. Sorry if it confused you. You probably thought that I was trying to limit some images or something else. LOVELY done. Cheers – Jurgen Nov 04 '18 at 16:12
Hi, agreed it is not that easy, I have discovered `GROUP_CONCAT` after like seven years of experience on PHP development, just remember it is a grouping function as is the `SUM` or `MAX` functions, so you usually would have a column with repeated values (here the `p.id` column) and you group with this column, and then you do what you want with the values that was grouped on the other columns (here we was interested in the `i.image_name` column). – Ermac Nov 04 '18 at 16:17
@ankabout In addition, if someone knows where am I supposed to insert WHERE clause, please let me know. I'd like to show only active products, so I tried to insert `WHERE products.status = 1` at some different positions, but the query has failed. – Jurgen Nov 05 '18 at 09:44
@Jurgen The `Where` should be between the `LEFT JOIN` clause and the `GROUP BY` clause. – Ermac Nov 05 '18 at 15:33
@ankabout I've already tried all the possible positions. And no results. I'll keep looking for the solution. – Jurgen Nov 05 '18 at 15:52

SQL/PHP join products table on multiple images table

1 Answers1