SELECT Multiple Images URL and load in Rows

Question

I am new to PHP programming and I need someone help now. I have 2 tables. In table 2 I have image links stored like

id           image URL     order
12345   www.abc.com/xxx.jpg 0
12345   www.abc.com/yyy.jpg 1
12346   www.abc.com/zzz.jpg 0
12346   www.abc.com/aaa.jpg 1
12346   www.abc.com/vvv.jpg 2
12346   www.abc.com/333.jpg 3
12347   www.abc.com/vvf.jpg 0
12347   www.abc.com/111.jpg 1

In table 1

id         name
12345   something1
12346   something2
12347   something3
.   
.

I have to load the data after LEFT JOIN with table 1 with key Id. Assume table1 have 7 variables too.the output I need is

id  name(table1)    img1(oforder0)  img2(oforder1)  img3(of order2) img4(oforder3)
12345   something1  www.abc.com/xxx.jpg www.abc.com/yyy.jpg -   -
12346   something2  www.abc.com/zzz.jpg www.abc.com/aaa.jpg     www.abc.com/vvv.jpg www.abc.com/333.jpg
12347   something 3 www.abc.com/vvf.jpg www.abc.com/111.jpg -   -

is it possible to do first of all? All I have tried until now is put for loop for image names & using Where order='$i' and create own variables and that’s doesn’t seems to work.
Because I don’t want the id and name field repeat again and again.I need someone help here to choose the right way to get the URL in the same row(if it is possible)

Is the number of images exactly 3 or <= 3, or is it variable/unknown, possibly > 3? — Michael Berkowski
– Michael Berkowski, Commented Jan 12, 2014 at 21:35
It is possible and there are 2 ways to approach it depending on the answer to my 1st question. — Michael Berkowski
– Michael Berkowski, Commented Jan 12, 2014 at 21:36
Micheal,i have upto 11 images in the database for each id. all i need is 4 images URL. — Selva
– Selva, Commented Jan 12, 2014 at 21:38
So you want only items 0-3 for each id, though you may have more? — Michael Berkowski
– Michael Berkowski, Commented Jan 12, 2014 at 21:40
yes thats right ! is it possible mate! i get stuck here for more than 8 hours micheal. — Selva
– Selva, Commented Jan 12, 2014 at 21:43

peterm · Accepted Answer · 2014-01-12 21:59:52Z

2

If you need exactly 4 out of 11 image urls you can use conditional aggregation

SELECT t1.id, t1.name,
       MAX(CASE WHEN t2.order = 0 THEN image_url END) img1,
       MAX(CASE WHEN t2.order = 1 THEN image_url END) img2,
       MAX(CASE WHEN t2.order = 2 THEN image_url END) img3,
       MAX(CASE WHEN t2.order = 3 THEN image_url END) img4
  FROM table1 t1 LEFT JOIN table2 t2
    ON t1.id = t2.id 
  GROUP BY t1.id

Output:

|    ID |       NAME |                IMG1 |                IMG2 |                IMG3 |                IMG4 |
|-------|------------|---------------------|---------------------|---------------------|---------------------|
| 12345 | something1 | www.abc.com/xxx.jpg | www.abc.com/yyy.jpg |              (null) |              (null) |
| 12346 | something2 | www.abc.com/zzz.jpg | www.abc.com/aaa.jpg | www.abc.com/vvv.jpg | www.abc.com/333.jpg |
| 12347 | something3 | www.abc.com/vvf.jpg | www.abc.com/111.jpg |              (null) |              (null) |

An alternative solution might be to pack all image urls into one column (let's call it urls) per id using GROUP_CONCAT() like this

SELECT t1.id, t1.name,
       GROUP_CONCAT(CONCAT(t2.order + 1, '|', t2.image_url) ORDER BY t2.order) urls
  FROM table1 t1 LEFT JOIN table2 t2
    ON t1.id = t2.id 
   AND t2.order <= 3
  GROUP BY t1.id

and then explode (split) urls values first by comma , then by a pipe | while you're iterating over the resultset.

Note: This approach is more dynamic. You can easily change how many image urls you want without changing the query itself.

Output:

|    ID |       NAME |                                                                                    URLS |
|-------|------------|-----------------------------------------------------------------------------------------|
| 12345 | something1 |                                             1|www.abc.com/xxx.jpg,2|www.abc.com/yyy.jpg |
| 12346 | something2 | 1|www.abc.com/zzz.jpg,2|www.abc.com/aaa.jpg,3|www.abc.com/vvv.jpg,4|www.abc.com/333.jpg |
| 12347 | something3 |                                             1|www.abc.com/vvf.jpg,2|www.abc.com/111.jpg |

Here is SQLFiddle demo

edited Jan 12, 2014 at 21:59

answered Jan 12, 2014 at 21:42

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Michael Berkowski Over a year ago

You can have my SQLfiddle. I was in the middle of a longer explanation.

peterm Over a year ago

@MichaelBerkowski I've almost done with mine (sqlfiddle), but thank you very much :)

Selva Over a year ago

i will do the query now and i will let you the output asap!! thanks for your help

Collectives™ on Stack Overflow

SELECT Multiple Images URL and load in Rows

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related