Treeview - Produkte zur Kategorie

Baasel · 21. September 2016

Hi,

ich will Produkte unter der Kategorie listen. Habe irgendwo bei einem Left Join oder dem Where ein Fehler drin, sodass ich die Produkte nicht unter die Kategorie gelistet bekomme. Kann mir wer weiterhelfen?

Code

$query = "            SELECT                 cat.`name_".$language."` AS catname,                cat.category_id AS category_id,                 prod.`name_".$language."` AS prodname,                prod.product_id AS product_id            FROM                 `#__jshopping_products` AS prod            INNER JOIN                 `#__jshopping_products_to_categories` AS pr_cat             ON                 pr_cat.product_id = prod.product_id            LEFT JOIN                 `#__jshopping_categories` AS cat             ON                 pr_cat.category_id = cat.category_id            WHERE                 prod.product_publish = '1'            AND                 cat.category_publish='1'            AND                 pr_cat.product_id = prod.product_id            ";    $db->setQuery($query);    $row = $db->loadObjectList();    foreach ($row as $value) {        print_r("Katname: ".$value->catname."<br>Produkte zur Kategorie: ".$value->prodname."<br><br>");    }

SQL Tabellen

Code

categoriescategory_id, nameproductsproduct_id, nameproducts_to_categoriescategory_id, product_id

print_r zeigt momentan folgendes:

Code

Katname: WandbilderProdukte: Acryl SchildKatname: DekorationProdukte: PappdisplayKatname: WandbilderProdukte: PappdisplayKatname: DekorationProdukte: Foto-Tischläufer & Tischdecke...

soll aber folgendes Anzeigen

Code

Katname: Wandbilder Produkte: Acryl Schild, Pappdisplay, ...
Katname: Dekoration Produkte: Pappdisplay, Foto-Tischläufer & Tischdecke, ...
....

..

Baasel · 21. September 2016

Hier die Lösung:

Code

$query = "
            SELECT 
                cat.`name_".$language."` AS catname,
                cat.category_id AS category_id
            FROM 
                `#__jshopping_categories` AS cat


            WHERE
                cat.category_publish='1'


            ";
    $db->setQuery($query);
    $row = $db->loadObjectList();


    foreach ($row as $value) {


        print_r("<br><br>Katname: ".$value->catname."<br><br>\n");


    $query = "
            SELECT 
                prod.`name_".$language."` AS prodname,
                prod.product_id AS product_id
            FROM 
                `#__jshopping_products` AS prod
            LEFT JOIN 
                `#__jshopping_products_to_categories` AS pr_cat 
            ON 
                pr_cat.product_id = prod.product_id
            WHERE
                prod.product_publish='1'
            AND
                pr_cat.category_id = ".$value->category_id."


            ";
    $db->setQuery($query);
    $row = $db->loadObjectList();


    foreach ($row as $value) {


        print_r("Produkte: ".$value->prodname."<br>\n");
    }


}

Alles anzeigen