CODEWITHSUNDEEP
mysql
PipBoy2000
PipBoy2000

Reputation: 440

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

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>');                                               
?>

Upvotes: 0

Views: 42

Answers (1)

Mostafa Vatanpour
Mostafa Vatanpour

Reputation: 1408

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

Upvotes: 1

Related Questions