PipBoy2000 PipBoy2000 - 5 months ago 9
MySQL Question

How to combine two MySQL queries to show results in one table row

I have two separate queries showing results in two tables. I want to show result in one table instead. How to make it to show this data?

So in first line i want to have


  • 2

  • GE 32,768 kHz SMD

  • 12,87112

  • Rezonator kwarcowy KX-327S 32,768 kHz 12.5pF, 20/-0,042/50K

  • Rezonatory filtry i źródła częstotliwości > Rezonatory kwarcowe (new line added here instead of "-")

  • 12195

  • 1

  • 1

  • 1.190000



This is how it looks now. I want to add another column "Nazwa kategorii"

enter image description here

<?php
$result = mysql_query('SELECT
prodlang.id_product AS "idprod",
prodlang.name AS "nazwa",
prod.price AS "cena",
prod.minimal_quantity AS "moq",
prod.ean13 AS "nazwag",
prod.reference AS "kod",
prod.quantity AS "ilosc",
prod.active AS "aktywny"
FROM pstest_product_lang prodlang
INNER JOIN pstest_product prod
ON prodlang.id_product=prod.id_product
limit 10');

while ($row = mysql_fetch_array($result)) echo('<tr><td>'.$row['idprod'].'</td><td>'.$row['kod'].'</td><td>'.$row['nazwag'].'</td><td>'.$row['nazwa'].'</td><td> - </td><td>'.$row['ilosc'].'</td><td>'.$row['aktywny'].'</td><td>'.$row['moq'].'</td><td>'.$row['cena'].'</td></tr>');

?>
<?php
$results = mysql_query('SELECT
prodcat.id_product AS "idprod",
catlang.name AS "nazwacat"
FROM pstest_category_product prodcat
INNER JOIN pstest_category_lang catlang
ON prodcat.id_category=catlang.id_category
ORDER BY idprod
limit 10');

while ($row = mysql_fetch_array($results)) echo('<tr><td>'.$row['idprod'].'</td><td>'.$row['nazwacat'].'</td></tr>');
?>

Answer

You can use group_concat with group by. For mor info see group by doc. I change your query and hope it work :

SELECT 
        prodlang.id_product AS "idprod", 
        prodlang.name AS "nazwa", 
        prod.price AS "cena", 
        prod.minimal_quantity AS "moq", 
        prod.ean13 AS "nazwag",
        t2.nazwacat as "nazwacat", 
        prod.reference AS "kod", 
        prod.quantity AS "ilosc",
        prod.active AS "aktywny"
        FROM pstest_product_lang prodlang
        INNER JOIN pstest_product prod
        ON prodlang.id_product=prod.id_product
        join (
        SELECT 
        prodcat.id_product AS "idprod", 
        Group_concat(catlang.name) AS "nazwacat" 
        FROM pstest_category_product prodcat
        INNER JOIN pstest_category_lang catlang
        ON prodcat.id_category=catlang.id_category
group by prodcat.id_product
) t2 on t2.idprod=prodlang.id_product
        limit 10
Comments