PHP MySQL - How to fetch the SELECT GROUP_CONCAT,GROUP_BY two table?

Question

I have two database tables like this and want to fetch to my website like this(see the screenshot)

But I can only fetch one table. I don't know how to use group by with JOIN
here is my code

$sql = "SELECT photographer,GROUP_CONCAT(free_image)  
                FROM free_images_table  
                GROUP BY photographer";
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_assoc($result))
{
  $free_image = explode(',', $row['GROUP_CONCAT(free_image)']);

  echo "<tr>";
  echo "<td>".$row['photographer_id']."</td>"; ?>

  <td> 
        <?php 
      for($i=0; $i < count($free_image); $i++ )
            { 
                echo $free_image[$i]; 
            }
?></td>

  echo "</tr>";
}

The special table may not have photographer (my website require only freeimage, the special image is optional.

will a photographer always have a record in both tables? If not, which table will always have a value? This could impact whether to do a `JOIN` or `LEFT JOIN`, etc. — Sean, Dec 09 '16 at 04:58
No. the special table may not have photographer (my website require only freeimage, the special image is optional. — doflamingo, Dec 09 '16 at 05:00

Sean · Accepted Answer · 2016-12-09T05:49:10.370

2

This could be done using a LEFT JOIN (added column aliases for simplicity in php) -

SELECT free_images_table.photographer, 
       GROUP_CONCAT(DISTINCT free_images_table.free_image) as free_images, 
       GROUP_CONCAT(DISTINCT special_images_table.special_image) as special_images
FROM free_images_table
LEFT JOIN special_images_table 
    ON special_images_table.photographer = free_images_table.photographer 
GROUP BY photographer

LEFT JOIN is used when you have a record in the 1st table, but not always a matching record in the 2nd table

Then in php, you would create your special image cells the same as your free image cells

$free_image = explode(',', $row['free_images']);
$special_image = explode(',', $row['special_images']);
...

edited Dec 09 '16 at 05:49

answered Dec 09 '16 at 05:06

Sean

12,443
3
29
47

I run in phpmyadmin and get this error : Column 'photographer' in field list is ambiguous – doflamingo Dec 09 '16 at 05:22
1

Add a table identifier - `free_images_table.photographer`. I will update my answer as well. – Sean Dec 09 '16 at 05:24
it seem have a problem. the result image for each photograph in free_image_table seem to have multiple quantity. for example, for photographer 1: result is foo1.jpg foo2.jpg foo3.jpg foo1.jpg foo2.jpg foo3.jpg , for photographer2 it have 5 times quantity like this, : foo4 foo5 foo4.jpg foo5 foo4 foo5 foo4 foo5 foo4 foo5 – doflamingo Dec 09 '16 at 05:43
1

You can use the `DISTINCT` attribute -> `GROUP_CONCAT( DISTINCT free_images_table.free_image)` - to remove the duplicates. see http://stackoverflow.com/a/3083527/689579 / http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat – Sean Dec 09 '16 at 05:48

score 0 · Answer 2 · answered Dec 09 '16 at 05:06

0

SELECT table_1.free_image, table_2.special_image FROM table1 INNER JOIN table_2 ON table_1.photographer = table_2.photographer

In this case can use INNER JOIN.

Add table name before the field and INNER JOIN with same photographer.

answered Dec 09 '16 at 05:06

No name

136
1
7

2

Problem with an `INNER JOIN` is that it requires a value in both tables, but the OP stated in [this comment](http://stackoverflow.com/questions/41053299/php-mysql-how-to-fetch-the-select-group-concat-group-by-two-table/41053456#comment69314052_41053299) that *the special table may not have photographer (my website require only freeimage, the special image is optional.* see http://stackoverflow.com/a/6188334/689579 – Sean Dec 09 '16 at 05:10

PHP MySQL - How to fetch the SELECT GROUP_CONCAT,GROUP_BY two table?

2 Answers2