How to split links of image after GROUP_CONCAT php MySQL-CodePudding

I make GROUP_CONCAT between two tables to collect similar data with LEFT JOIN.

This is old data before GROUP_CONCAT:

Output:
 ----------- -------- ---------- ----------- 
|id| name   | Cmpany | POSTID   |ImageTopic |
|1 | John   |  Js    |  1       |1png       |
|1 | John   |  Js    |  1       |2png       |
|1 | John   |  Js    |  1       |3png       |
|1 | John   |  Js    |  1       |4png       |
 ----------- -------- ---------- -----------

New data with GROUP_CONCAT:

Output:
 --- ------ ----------- -------- ------------------------ 
|id | name |    Cmpany | POSTID |   ImageTopic           |
|1  | John |      Js   |  1     |   1png,2png,3png,4png  |
 --- ------ ----------- -------- ------------------------

Now my problem is with Column of Image Topic in new data. I get link Images in this Column but it's coming without sprit so I can't click it or use it in other place.

Example image coming like that:

ImageTopic: "https://png.pngtree.com/element_our/20200703/ourlarge/pngtree-butterfly-purple-red-wings-ink-transparent-png-bright-image_2300442.jpg,https://www.picng.com/upload/butterfly/png_butterfly_61701.png,https://www.picng.com/upload/butterfly/png_butterfly_61701.pnghttps://www.picng.com/upload/butterfly/png_butterfly_61700.png"

As you can see above this is not correct format.

And If I try to click it I get this:

This XML file does not appear to have any style information associated with it. The document tree is shown below.
<Error>
<Code>AccessDenied</Code>
<Message>Access Denied</Message>
<RequestId>JF8C24VHE7Z9NKAC</RequestId>
<HostId> yKClkshmm8kCqYydoRsaPDwjkkM anF0vQI6nt0eTY6TvCuQ5QDv7hfq//eTxUJ9ApNMNWpPfc=</HostId>
</Error>

My code:


<?php
require_once 'con.php';

$id=$_GET['id'];


$sql= "SELECT * FROM topics

LEFT JOIN (SELECT POSTID, GROUP_CONCAT(DISTINCT ImageTopic  ) ImageTopic 
FROM ImagePost GROUP BY POSTID
) ImageTopic ON topics.id = ImageTopic.POSTID


where topics.id=? "
;

$stmt = $con->prepare($sql); 

$stmt->bind_param("s",$id);

$stmt->execute();

$result = $stmt->get_result();

if ($result->num_rows >0) {
 
 
     while($row[] = $result->fetch_assoc()) {
     
     $item = $row;
     
     $json = json_encode($item, JSON_NUMERIC_CHECK);
     
     }
 
} else {

    $json = json_encode(["result" => "No Data Foun"]);
}
 echo $json;

 
$con->close();
 



?>

How I can solve this problem?

You can try it here: https://onecompiler.com/mysql/3yhc5dnkh

This URL of images I will use it also later in my app.

CodePudding user response：

If you further process your data in PHP you can explode them into an array using explode function.

$imageTopicArray = explode(',', $imageTopic);
foreach ($imageTopicItem as $imageTopicArray) {
    echo '<img src="'. $imageTopicItem .'" />';
}

This gives you an array of strings and you can print them one by one.

Another option is to use JSON_ARRAYAGG function which is available since MySQL 5.7.22. Instead of GROUP_CONCAT. This returns you JSON array instead of comma separated values which might be more comfortable in some cases.

Then use json_decode to get the array of items and process them as mentioned above.