Categories With Subcategories In Table - Php + Mysql

I have Categories and Sub-Categories on my database. Database table contain 'cid' , 'pid' and 'cname' columns. Cid = Categories ID Pid = If Category is a Sub-Category , then Pid =

Solution 1:

For easier implementation your can make two quires

This will give all the categories. ( Let say root category pid will be 0

SELECT name FROM categories where pid = 0

In that loop use 2nd query

SELECT name FROM categories where pid = $catId

Here is the php code example to populate the array you can use

function runQuery($sql) {

global$DB;
    $dbResource = $DB['DB_Database']->query($sql);
    $rows = array();
    while ($row = $DB['DB_Database']->fetch_assoc($dbResource))
    {
         $rows[] = $row;
    }
    return$rows;
}

functiongetSubCat($pid)
{
    subCategorySql = "SELECT name FROM categories where pid = "+ $pid; 
    return runQuery($subCategorySql);
}

$categorySql = "SELECT name FROM categories where pid = 0"$categories = runQuery(categorySql)
$i=0;
foreach($categoriesas$aCat)
{
    $categories[$i++]['sub_cat'] = getSubCat($aCat['id']);
} 

now you can use $categories[index]['name'] for category and $categories[index] ['sub_cat'][sub_cat_index][name] for subcategory name.

This is not a good approach though as it will hit database so many time.

Solution 2:

I would suggest changing your php code to work with the following sql that uses an OUTER JOIN:

select c.cname, c2.cname subname
from categories c
  left join categories c2 on c.cid = c2.pid
where c.pid isnull

• SQL Fiddle Demo

This assumes the parent's pid field is null. Alternatively you could use GROUP_CONCAT to return all your subcategories in a single row instead of multiple rows -- this might be easier for your implementation.