Categories with Subcategories in table PHP + MYSQL

[JacKy_X]

Hello Freaks;

I have Categories and Sub-Categories on my database. Database table contain "cid" , "pid" and "cname" coloums.

 

Cid = Categories ID

 

Pid = If Category is a Sub-Category , then Pid = that belong which category. ( Also each sub category own Cid.. Main Categories Pid = 0 in database)

 

Cname = Category Name

 

For Example Software is a category and id = 15 and "IOS Software is a sub-category and id= 30 and pid = 15

 

Now i want to show this Cat. And Sub-Cat in a table. Here is my code

function categories() {

$categorysql = $DB['DB_Database']->query("SELECT cid, pid, cname FROM categories GROUP BY cid");
while ($row = $DB['DB_Database']->fetch_assoc($categorysql))
{
if ($row['pid'] > 0 {
}
else {
$catid = $row['cid'];
$catname = $row['cname']; }

And here is my template codes;

<table width="268" border="1">

<tr>
<td width="52" rowspan="2" valign="top">Main Cat Image</td>
<td width="200" height="23"><a href="{$catid}">{$catname}<a></td>
</tr>
<tr>
<td height="36" valign="top">This area for sub cat.</td>
</tr>
</table>

That function and html code it look like this;
 

 

But I want to make it like this;

 

 

 

 

So how can i match main and sub category eachother in sql query ? 

Edited July 28, 2013 by JacKy_X

[Psycho]

 

    $query = "SELECT cid, pid, cname, IF(pid=0, cid, pid) as sortID
              FROM categories
              ORDER BY sortID, pid";
    $result = $DB['DB_Database']->query($query);

    while ($row = $DB['DB_Database']->fetch_assoc($result))
    {
        if($row['pid'] == 0)
        {
            //Insert code for displaying parent category
        }
        else
        {
            //Insert code for displaying child cateory
        }
    }

[JacKy_X]

    $query = "SELECT cid, pid, cname, IF(pid=0, cid, pid) as sortID
              FROM categories
              ORDER BY sortID, pid";
    $result = $DB['DB_Database']->query($query);

    while ($row = $DB['DB_Database']->fetch_assoc($result))
    {
        if($row['pid'] == 0)
        {
            //Insert code for displaying parent category
        }
        else
        {
            //Insert code for displaying child cateory
        }
    }

 

Thank you for answer. But i couldn't make it run.

 

I Forget the mention that i am using also eval for the calling template;

 

Here is my full code

 

function category() {
global $DB;
$category ='';
$categorysql = $DB['DB_Database']->query("SELECT cid, pid, sort, cname FROM categories GROUP BY cid ORDER BY sort ASC");

while ($row = $DB['DB_Database']->fetch_assoc($categorysql))
{
if ($row['pid'] > 0) {

}
else {
$catid = $row['cid'];
$catname = $row['cname'];
eval("\$category_row = \"".$DB['DB_Template']->LoadTemplate('category_row')."\";");
$callrow .= $kategor_row;}
}
if($category_row == '')
{
$category = '';
}
else{
eval("\$category = \"".$DB['DB_Template']->LoadTemplate('category')."\";");
return $category;
}
}

 

I'm putting this codes in a template named as category_row

<table width="268" border="1">

<tr>
<td width="52" rowspan="2" valign="top">Main Cat Image</td>
<td width="200" height="23"><a href="{$catid}">{$catname}<a></td>
</tr>
<tr>
<td height="36" valign="top">This area for sub cat.</td>
</tr>
</table>

And I'm putting this codes in a template named as category

 

<div class="widget">
<h4>Categories</h4>
</div>
$callrow

Edited July 28, 2013 by JacKy_X

[Psycho]

Try the query I gave you in PHPMyAdmin or whatever DB management app you are using. Then go from there.