Query with advance Grouping
Hi, I want to do a query where I have a table for products and this table is linked to a category table so for 1 category I have 4 or 5 products, the tables are linked like this:
products.prodCATID = category.categoryID
|PRODUCT | CATEGORY |
-------------------------------------
|Product 1 | hammers
|Product 2 | hammers
|Product 3 | screwdrivers
|Product 4 | screwdrivers
|Product 5 | sockets
I want to do a recordset that when I repeat it in the page I can have a list like this (were the categories won't repeat with each product, but with the group of products:
Hammers
Product 1
Product 2
Screwdrivers
Product 3
Product 4
Sockets
Product 5
My recordset is build like this:
$products = new WA_MySQLi_RS("products",$conn_mysqli_base,3);
$products->setQuery("SELECT products.productsID, products.productsNOM, products.productsADD, products.productsSUBCAT, products.productsORD, categoy.categoryID, category.categoryNOM, subcategory.subcatID, subcategory.subcatNOM, subcategory.subcatCATID FROM (((products LEFT JOIN subcategory ON products.productsSUBCAT = subcategory.subcatID) LEFT JOIN category ON subcategory.subcatCATID = category.categoryID WHERE subcategory.subcatCATID = ? ORDER BY products.productsORD ASC");
$products->bindParam("i", "".$_GET['cat'] ."", "-1"); //colname_1
$products->execute();