CODEWITHSUNDEEP
phpmysql
user2411928
user2411928

Reputation: 33

How to get all value in php mysql in a ID?

I have a sample data

product (ID, name)
         1 | 'iPhone'
         2 | 'iPad'
         3 | 'iWatch'
product_meta (ID, product_id, meta_key, meta_value) 
               1      1         image   iPhone.png       
               2      2         view        123

And using query:

SELECT p.*, m.*
FROM product AS p
LEFT JOIN product_meta AS m ON m.product_id = p.ID
WHERE p.ID = 1
GROUP BY p.ID

How to get all value to result is

product(ID, name, image, view) =>  1 | iPhone | iPhone.png | 123

Upvotes: 0

Views: 96

Answers (3)

RandomSeed
RandomSeed

Reputation: 29769

Based on the same assumption as sgeddes:

SELECT p.id, p.name, mimage.meta_value, mview.meta_value
FROM product AS p
LEFT JOIN product_meta AS mimage
    ON mimage.product_id = p.id AND mimage.meta_key = 'image'
LEFT JOIN product_meta AS mview
    ON mview.product_id = p.id AND mview.meta_key = 'view'
WHERE p.id = 1

Upvotes: 0

sgeddes
sgeddes

Reputation: 62841

Assuming your sample data is incorrect and you're trying to PIVOT your results, you can use MAX with CASE for this:

select p.id, 
   p.name, 
   max(case when pm.meta_key = 'image' then pm.meta_value end) image,   
   max(case when pm.meta_key = 'view' then pm.meta_value end) view
from product AS p
    left join product_meta AS pm ON pm.product_id = p.ID
where p.ID = 1
group by p.ID

Upvotes: 1

user2380555
user2380555

Reputation: 13

you can do like select table1.column1, table2.column2 from .......... rest of query.

Upvotes: 0

Related Questions