mysql join one to many with images

Question

I have two tables...

project table

+----+--------+
| id | client |
+----+--------+
|  1 | James  |
|  2 | John   |
+----+--------+

images table

+----+-----------+-------------------+
| id | projectId |     imagePath     |
+----+-----------+-------------------+
|  1 |         1 | images/image1.jpg |
|  2 |         1 | images/image2.jpg |
|  3 |         2 | images/image3.jpg |
|  4 |         2 | images/image4.jpg |
|  5 |         2 | images/image5.jpg |
+----+-----------+-------------------+

As you can see, one project has many images. I want to display that this way...

James

images/img1.jpg
images/img2.jpg

John

images/img3.jpg
images/img4.jpg
images/img5.jpg

This post gave me what I want https://stackoverflow.com/a/2451065/1214535

But when I echo like so

<img src='".$row['imagePath']."/>

the results in image tag I get this

<img src="images/img3.jpg,images/img4.jpg,images/img5.jpg">

instead of

<img src="images/img3.jpg"/> 
<img src="images/img4.jpg"/>
<img src="images/img5.jpg"/>

How can I change the query so that I can display the images properly/separately

this is the query I am using

$sql="SELECT images.projectId,project.client,
GROUP_CONCAT(images.imagePath SEPARATOR ', ') 
AS 'imagePath' 
from project left JOIN images on project.id=images.projectId 
GROUP BY project.id ASC";

thank you guys...

Depending on how large your table and result sets are, you're better off with the GROUP_CONCAT and explode() suggestion that people have made. If you do the query without the GROUP_CONCAT, you'll get the overhead of fetching and sending the project and client data with every row. With the GROUP_CONCAT option, you don't get this overhead. **However**, keep in mind that GROUP_CONCAT is limited to 1024 bytes by default. You can tune this by changing the group_concat_max_len option. — Nim, Sep 18 '12 at 06:50

score 1 · Answer 1 · answered Sep 18 '12 at 06:13

1

you have to break the string like below

$imgArray = explode(',',$row['imagePath']);
//then do 
foreach($imgArray as $im)
{
 if( is_readable($im) ) {
         echo "<img src='$im' title='Image' />";
 }
}

answered Sep 18 '12 at 06:13

xkeshav

53,360
44
177
245

score 1 · Answer 2 · edited May 23 '17 at 12:27

1

I believe that EXPLODE is the answer.Look at PHP explode - running loop through each array item Stack overflow question. I believe that it will answer to your question.

Also, I think (another approach) if you change ur query to

SELECT tp.CLIENT,ti.IMAGEPATH
FROM tblProject tp 
JOIN tblImage ti ON tp.Id = ti.ProjectId

then a simple loop will be easy to get you through.

edited May 23 '17 at 12:27

Community

1
1

answered Sep 18 '12 at 06:14

Niladri Biswas

4,153
2
17
24

score 1 · Accepted Answer · answered Sep 18 '12 at 06:14

1

You could try using foreach to loop through different values.

Syntax:

foreach (array_expression as $value)
    statement

First insert all the values you get in an array like this:

$imgArray = explode(',',$row['imagePath']);

Then loop through this array as:

foreach ($imgArray as $img)
{
 echo "<img src='$img'" />";
}

answered Sep 18 '12 at 06:14

heretolearn

6,387
4
30
53

Thanks thanks guys for all your answers.Using explode and looping through did the trick. – Victor Njoroge Sep 18 '12 at 07:08
i have question about fetching one image against one product like. One product has many (4) images but i only want to show only one image against that product. – Ali Raza Jul 22 '19 at 21:00
@AliRaza In that case you don't have to loop over the list. Once you have the list of image as `$imgArray` is above example, select the image you want using the list index like `echo "";` – heretolearn Jul 23 '19 at 10:45
sorry my question wasnt so clear, Want to show all the products in product table and also there images, but every product should show one image from image table. ( i use the inner join but inner join fetch all the image against one product, that is not what i want) if the question is not clear please do tell, ill try to explain further details. main thing is `All the products with only one image against each product` – Ali Raza Jul 23 '19 at 11:13
This can be solved at the query level itself. if i got the question correctly, you can do this by using group by on product and take min/ max of id. – heretolearn Jul 23 '19 at 11:25

score 0 · Answer 4 · answered Sep 18 '12 at 06:13

0

Because you are using GROUP_CONCAT which is returning all non-null values as string.

You can use below query:

$sql="SELECT images.projectId,project.client,images.imagePath SEPARATOR
AS 'imagePath'
from project left JOIN images on project.id=images.projectId 
GROUP BY project.id ASC";

And loop through result.

answered Sep 18 '12 at 06:13

metalfight - user868766

2,722
1
16
20

it is a good approach you use. but i have question about fetching one image against one product like. One product has many (4) images but i only want to show only one image against that product. – Ali Raza Jul 22 '19 at 21:01

mysql join one to many with images

4 Answers4