Yoeri Plovie Yoeri Plovie - 7 months ago 9
SQL Question

mysql list by category 3 tables

i use to make a product list from an mysql db.

my tables:



klantartikels:
klantart_art_nummer klantart_klant_id
29216 1
15151 1
18215 2

artikels
art_naam art_nummer art_cat_id
artnaam1 29216 12
artnaam2 15151 11
artnaam3 18215 6

categorie
cat_id cat_naam
12 category1
11 category2
6 category3


table klantartikles holds the user definend products

when user with id "1" goes to his/her list page, i want to display them like this

 
category1
artnaam1

catgory2
art_naam2

etc.


mayby somthing like this?


$subcatsql = mysql_query("SELECT *
FROM klantartikels AS k
INNER JOIN artikels AS a
ON a.art_nummer = k.klantart_art_nummer
WHERE k.klantart_klant_id = '1' GROUP BY a.art_cat_id")or die(mysql_error());


sorry for my bad english

thanks a lot ;)

Answer

You can use JOIN to do this:

SELECT art.*, cat.cat_naam as category_name FROM artikels art JOIN categorie cat ON art.art_cat_id = cat.cat_id JOIN klantartikels kart ON kart.klantart_art_nummer = art.art_nummer WHERE kart.klantart_klant_id = '1'

You can access everything from artikels table, and from the result, you can access the category name by: category_name

EDIT

For the additional question, the answer is:

// FETCH FROM THE DATABASE

$theArray = array();
while($row = mysql_fetch_array($query))
{
    $category = $row["category_name"];
    $art_naam = $row["art_naam"];

    if(isset($theArray[$category]))
        array_push($theArray[$category], $art_naam);
    else
        $theArray[$category] = array("0" => $art_naam);
}

// PRINT IT

foreach($theArray as $category => $item)
{
    print "<h1>" . $category . "</h1>";
    foreach($item as $name)
    {
        print "<div>" . $name . "</div>";
    }
}