Merge result after query with JOIN clause in Mysql

Question

I have two table like this:

Product

id | title 
-------------------------------
1 | Skirt
2 | Pants

Product_thumbnail

id | product_id   | image
-------------------------------
1 | 1             | pant.jpg
2 | 1             | shoes.png

When I want to get product with the thumbnail, I query like this:

SELECT p.*, pt.image FROM product p
LEFT JOIN product_thumbnail pt ON pt.product_id = p.id;

The output I expected

[0]=> [
          [id] => 1
          [image] =>
          [ 
            [0] => pant.jpg
            [1] => shoes.jpg
          ]
      ]

[1]=> [
          [id] => 2
          [image] => null
      ]

But the real output

[0]=> [
          id => 1
          image => pant.jpg
      ]

[1]=> [
          id => 1
          image => shoes.jpg
      ]

[2]=> [
          id => 2
          image => shoes.jpg
      ]

As you see, there is 2 element duplicate, so I need to merge it by hand, is there any way to achieve this more easy? Because my table have many tables relate together more than this, I'm using PHP, I use array_merge_recursive() to merge them but if do like that I get duplicate value in each field, like this:

[0]=> [
          [id] =>
              [
                [0] => 1
                [1] => 1
              ]

          [image] =>
              [
                [0] => pant.jpg
                [1] => shoes.jpg
              ]
      ]

It's not what I want, can anyone give me an idea?

score 0 · Answer 1 · edited Dec 18 '19 at 16:36

Consider the following. The code could I'm sure be written more economically, but hopefully you get the idea...

<?php

/*
DROP TABLE IF EXISTS product;

CREATE TABLE product
(id SERIAL PRIMARY KEY
,title VARCHAR(12) NOT NULL UNIQUE
);

INSERT INTO product VALUES
(1,'Hat'),
(2,'Shoe');

DROP TABLE IF EXISTS product_thumbnail;

CREATE TABLE product_thumbnail
(id SERIAL PRIMARY KEY
,product_id INT NOT NULL
,image VARCHAR(12) NOT NULL UNIQUE
);

INSERT INTO product_thumbnail VALUES
(1,1,'sombrero.jpg'),
(2,1,'stetson.png');

SELECT p.id
     , p.title
     , t.image
  FROM product p
  LEFT
  JOIN product_thumbnail t
    ON t.product_id = p.id;
+----+-------+--------------+
| id | title | image        |
+----+-------+--------------+
|  1 | Hat   | sombrero.jpg |
|  1 | Hat   | stetson.png  |
|  2 | Shoe  | NULL         |
+----+-------+--------------+
*/

require('path/to/connection/stateme.nts');

$query = "
SELECT p.id
     , p.title
     , t.image
  FROM product p
  LEFT
  JOIN product_thumbnail t
    ON t.product_id = p.id;
";

$result = mysqli_query($db,$query);

$old_array = array();

while($row = mysqli_fetch_assoc($result)){
    $old_array[] = $row;
}


$new_array = array();

foreach ($old_array as $row) {
   $new_array[$row['id']]['title'] = $row['title'];
   $new_array[$row['id']]['image'][] = $row['image'];
}

$new_array = array_values($new_array); // reindex


print_r($new_array);

?>

Outputs

Array
(
    [0] => Array
        (
            [title] => Hat
            [image] => Array
                (
                    [0] => sombrero.jpg
                    [1] => stetson.png
                )

        )

    [1] => Array
        (
            [title] => Shoe
            [image] => Array
                (
                    [0] => 
                )

        )

)

Thanks for your work, I was doing the same like this, and when come to query JOIN many tables it become a mess with merge this by hand code, I was think if there is some library or method can support this. — dim0147, Nov 26 '19 at 15:06
You have an error. [`mysqli_error()`](https://www.php.net/manual/en/mysqli.error.php) needs one argument. Please consider switching error mode on instead. [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) — Dharman, Dec 01 '19 at 22:10

score -1 · Answer 2 · answered Nov 26 '19 at 12:19

-1

MySQL doesn't support arrays, but you can aggregate the data into a string:

SELECT p.*, GROUP_CONCAT(pt.image) as images
FROM product p LEFT JOIN
     product_thumbnail pt
     ON pt.product_id = p.id
GROUP BY p.id;

Note that aggregating only by the id and selecting p.* is allowed and even standard SQL -- assuming that id is a primary key or declared to be unique. This is the one situation when this syntax is permitted.

answered Nov 26 '19 at 12:19

Gordon Linoff

1,242,037
58
646
786

Thanks, that's a good idea! But I still want the output as I expected, because some time I still need the ID of product_thumbnail also, like this: `image = [ '1' => pant.jpg , '2' => shoes.jpg]` where 1 and 2 is a `ID` of `product_thumbnail`, I'm finding best way to merge those result without complicate, thank for your help. – dim0147 Nov 26 '19 at 12:31
Plainly, the OP is successfully generating an array from the output (just not the right one). In consequence, this is not helpful. – Strawberry Nov 26 '19 at 12:39
@dim0147 . . . You can include the image id in the string. That, however, is not in your question. – Gordon Linoff Nov 26 '19 at 12:54

Merge result after query with JOIN clause in Mysql

2 Answers2