How to display data one to many relationship using php and mysql

I have a lot of products and each product has many flavour. How can i display one product name with multiple flavor. below is the script that i have tried. It doesn’t display anything. I’m really new to php.

$res = $conn->query("SELECT as_product.p_id,GROUP_CONCAT(as_product_flavour.pfl_prod_id) as flavours FROM as_product
LEFT JOIN as_product_flavour ON as_product.p_id = as_product_flavour.pfl_prod_id 
WHERE as_product.p_id = 28 GROUP BY as_product.p_id");      

        $currGroup = -1;
        while($row = $res->fetch_assoc())
        {
            echo 'Product Name: '.$row['product_name'];
            if($row['flavours'] != $currGroup)
            {
                $currGroup = $row['p_id'];

                echo 'Flavour Id: ' . $row['pfl_id'] . 'n';
                echo 'Falvour Name: ' . $row['pfl_flavour'] . 'n';                
            }

        }

Answer

I would start with a simpler query…

SELECT p.p_id
     , f.pfl_prod_id
  FROM as_product p
  LEFT 
  JOIN as_product_flavour f
     ON p.p_id = f.pfl_prod_id 
 WHERE p.p_id = 28

…and handle any remaining display issues in the application code, so a simple transformation of your resulting array.

Advertisement

Answer