CODEWITHSUNDEEP
phpsqljoin
Jurgen
Jurgen

Reputation: 274

SQL/PHP join products table on multiple images table

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????
        ..............

Upvotes: 1

Views: 1784

Answers (1)

Ermac
Ermac

Reputation: 1250

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

Upvotes: 2

Related Questions