1 Vote Vote

Align information from MySQL in 1 row

Posted by topdog 494 days ago Questions| null img product All

I this three tables in MySQL.

Table with names of Artists (m) Table with Product iformations, in this case Picture addresses (p) Table that has id of Artists and id of Products. (pmx)

I'm trying to Join them together so I get 1 row for each Artists. There are several images in p table for 1 Artists. I added a column called mf_prior where are the numbers of images (user assigns the numbers hiself and select those 3 images that he wants to be displayd on the front-end). I'm searching Artists (manufacturers) by Letters for ex. "M"

The idea is that I'd like to get

  1. manufacturer_id | mf_name | img1_id | img1_address | img2_id | img2_address | img3_id | img3_address

I'm getting

  1. manufacturer_id1 | mf_name1 | img1_id | img1_address | NULL | NULL | NULL | NULL
  2. manufacturer_id1 | mf_name1 | NULL | NULL | img2_id | img2_address | NULL | NULL
  3. manufacturer_id1 | mf_name1 | NULL | MULL | NULL | NULL | img3_id | img3_address

I have this MySQL SELECT:

SELECT
 m.manufacturer_id ,
 m.mf_name ,
 p1.product_id AS pimg1 ,
 p1.product_full_image AS p1 ,
 p2.product_id AS pimg2 ,
 p2.product_full_image AS p2 ,
 p3.product_id AS pimg3 ,
 p3.product_full_image AS p3 
FROM
 jos_vm_product_mf_xref AS pmx 
JOIN
 jos_vm_manufacturer AS m ON m.manufacturer_id = pmx.manufacturer_id 
JOIN
 jos_vm_product AS p1 ON p1.product_id = pmx.product_id AND p1.mf_prior = 1
JOIN
 jos_vm_product AS p2 ON p2.product_id = pmx.product_id AND p2.mf_prior = 2
JOIN
 jos_vm_product AS p3 ON p3.product_id = pmx.product_id AND p3.mf_prior = 3

WHERE
 m.mf_chars = 'm'

as a result I get 3 rows and in places I get NULL - as shown above

Maybe anyone can help me with this one - to write a better SELECT or make smth with the result with PHP

Originally asked by: Mikey on Stack Overflow

Discuss Bury


Who Voted for this Question