Repeat results query with LEFT JOIN in php MySQL-CodePudding

I try to get data from 3 tables use LEFT JOIN. The problem in one table (image table) when I save images of this topics and as example each topic have 4 images in that table so now when I make query to get those data the results is repeat 4 times in each time all that get from 2 other table repeated but the picture is changing in each query and at the end I get 4 results. How I can solve this problem? I need 4 image but I don't need 4 times query.

As you will see in this is example of my table the topic id 1 have 4 images:

table-1 : topic 
 ------- ----------- ----------- ------------------- 
|   id  |   name    |    time   |       data        |
 ------- ----------- ----------- ------------------- 
|   1   |   John    |     1     |   214-444-1234    |
|   2   |   Mary    |     1     |   555-111-1234    |
|   3   |   Jeff    |     1     |   214-222-1234    |
|   4   |   Bill    |     1     |   817-333-1234    |
|   5   |   Bob     |     1     |   214-555-1234    |
 ------- ----------- ----------- -------------------


table-2 : image
 ------- ----------- ----------- ------------- 
|   id  |   name    |   image   | Id_Topic    |
 ------- ----------- ----------- ------------- 
|   1   |   John    |     png   |   1         |
|   2   |   Mary    |     png   |   1         |
|   3   |   Jeff    |     png   |   1         |
|   4   |   Bill    |     png   |   1         |
|   5   |   Bob     |     png   |   5         |
 ------- ----------- ----------- -------------

table-3 : others
 ------- ----------- ----------- ------------------- 
|   id  |   name    |   city    |       phone       |
 ------- ----------- ----------- ------------------- 
|   1   |   John    |     Uk    |   214-444-1234    |
|   2   |   Mary    |     Uk    |   555-111-1234    |
|   3   |   Jeff    |     Uk    |   214-222-1234    |
|   4   |   Bill    |     Uk    |   817-333-1234    |
|   5   |   Bob     |     Uk    |   214-555-1234    |
 ------- ----------- ----------- -------------------

My query like:

SELECT * FROM topics
  LEFT JOIN image ON topics.id = image.Id_Topic 
  inner join Others ON topics.id = Others .id
  where topics.id = ?

CodePudding user response：

Does MySQL GROUP_CONCAT do what you need?

SELECT topic.name, images.images
FROM topic
JOIN (
  SELECT id_Topic, GROUP_CONCAT(DISTINCT image) images
  FROM image GROUP BY id_Topic
) images ON topic.id = images.id_Topic
;

Outputs:

| name | images              |
|------|---------------------|
| John | 1png,2png,3png,4png |
| Bob  | 5png                |

As you can see, GROUP_CONCAT concatenates the several image links using commas, so to display each image you'll need to use your back-end programming language to split the images string on comma, then loop the resulting array and present each image individually.

If you have PHP, explode()'ing and looping may look like this:

<?php

// get images string from database: $images_str
// ...

$images_arr = explode($images_str, ',');
foreach ( $images_arr as $image ) {
  echo <<<IMG
    <img src="$image">
IMG;
}

?>

Example above, topic name "John", outputs:

    <img src="1png">
    <img src="2png">
    <img src="3png">
    <img src="4png">

-- create
CREATE TABLE topic (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE image (
  id INTEGER PRIMARY KEY,
  id_Topic INTEGER,
  image TEXT NOT NULL,
  name TEXT NOT NULL
);

-- insert
INSERT INTO topic VALUES (1, 'John'), (2, 'Mary'), (3, 'Jeff'), (4, 'Bill'), (5, 'Bob');
INSERT INTO image VALUES (1, 1, '1png', 'John'), (2, 1, '2png', 'Mary'), (3, 1, '3png', 'Jeff'), (4, 1, '4png', 'Bill'), (5, 5, '5png', 'Bob');

-- fetch 
SELECT topic.name, images.images
FROM topic
JOIN (
  SELECT id_Topic, GROUP_CONCAT(DISTINCT image) images
  FROM image GROUP BY id_Topic
) images ON topic.id = images.id_Topic
;

Try it here: https://onecompiler.com/mysql/3yhasjhut