Justin Bland Justin Bland - 18 days ago 6
HTML Question

Displaying Categories and Subcategories in php from mysql database

I'm trying to display a list of categories and subcatagories on a website that are populated from a MySQL database and I'm a bit rusty with how to do it properly..

basically I want to achieve this

Parent Cat 1
--Child Cat1
--Child Cat2
Parent Cat 2
Parent Cat 3
--Child Cat 1
--Child Cat 2


and the database is layed out

ParentCat1, ChildCat1, Item1
ParentCat1, ChildCat1, Item2
ParentCat1, ChildCat2, Item1
ParentCat2, Item1
ParentCat3, ChildCat1, Item1
ParentCat3, ChildCat2, Item2


EDIT
this is what I have so far big thanks to Gowtham

<?php
$conn = mysql_connect("localhost", "USER", "PASS");

if (!$conn) {
echo "Unable to connect to DB: " . mysql_error();
exit;
}

if (!mysql_select_db("DB-Store")) {
echo "Unable to select DB-Store: " . mysql_error();
exit;
}

$sql = "SELECT * FROM menu";

$result = mysql_query($sql);

if (!$result) {
echo "Could not successfully run query ($sql) from DB: " . mysql_error();
exit;
}

if (mysql_num_rows($result) == 0) {
echo "No rows found, nothing to print";
exit;
}

$result = mysql_query($sql);
$menu = array();
echo "Start of Array";
echo "<br>";
while ($row = mysql_fetch_assoc($result)) {
$menu['category'][] = $result['cat'];
if (!empty($result['subcat']))
$menu['subcat'][$result['cat']][] = $result['subcat'];
}

foreach ($menu['category'] as $cat) {
echo $cat."<br>";
foreach ($menu['subcat'][$cat] as $subcat) {
echo "--" . $subcat."<br>";
}
}
echo "<br>";
echo "End of Array";
mysql_free_result($result);
?>

Answer

The first thing I will do to achieve it is, getting value from the database and store it in the array and run a loop to print the array.

Here is my code. Let's consider category field name is cat and subcategory field name is subcat and table name is menu, then

<?php

$sql = 'select * from menu';
$con = mysqli_connect("localhost", "my_user", "my_password", "my_db");
// Check connection
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// Perform queries
$result = mysqli_query($con, $sql);

$menu = array();
while ($row = mysqli_fetch_assoc($result)) {
    if (!in_array($row['cat'], $menu['category'])) {

            $menu['category'][] = $row['cat'];
    }
    if (!empty($row['subcat']))
        $menu['subcat'][$row['cat']][] = $row['subcat'];
}

foreach ($menu['category'] as $cat) {
    echo $cat."<br>";
    foreach ($menu['subcat'][$cat] as $subcat) {
        echo "--" . $subcat."<br>";
    }
}
?>

And the above program will satisfy your need.