how to show categories and its sub categories

[amith_ami]

hi all pls help me...
im trying to display categories and its sub categories... but i dont know how to display subcategory.. ijust only know to display the category.. i created two tables in the database named tbl_category and tbl_subcategory in both table the field cat_id is common....

here is my code for displaying category list..

<?php 
                  $sql="Select  * from tbl_category";
                  $obj->query($sql);
                  $nume=$obj->num_rows();
                  while($row=$obj->query_fetch()) {
                  $c_id = $row['cat_id']
                  ?>
<ul>
<li><a href="products.php?cat_id=<?php echo $row['cat_id'];?>&name=<?php echo $row['cat_name'];?>"><?php echo $row['cat_name']; ?></a>
    </li>

</ul>
<?php } ?>

pls help me to display sub category corresponding to the categorylist.

tnx in advnc..

[minitauros]

You can either create a JOIN in your original query, or execute a subquery for each subcategory. In the first case you'd have to use PHP to detect the start of a new section.

With a JOIN:

<?php
$q = 'SELECT tbl_categories.cat_id,
        tbl_categories.cat_name,
        tbl_subcategories.subcat_id,
        tbl_subcategories.subcat_name,
    FROM tbl_subcategories
    JOIN tbl_categories ON tbl_subcategories.cat_id = tbl_categories.cat_id
    ORDER BY tbl_categories.cat_id DESC';
$rq = mysql_query($q);

while($fetch = mysql_fetch_assoc($rq))
{
    $current_category = $fetch['cat_id'];

    if($current_category != $old_category)
    {
        // We've arrived at a new category.

        // << Do some stuff >>
    }

    // Remember the last category we've used.
    $old_category = $current_category;
}

Without a JOIN (with subqueries):

<?php
// Retrieve categories.
$q = 'SELECT tbl_categories.cat_id,
        tbl_categories.cat_name
    FROM tbl_categories
    ORDER BY tbl_categories.cat_id DESC';
$rq = mysql_query($q);

while($fetch = mysql_fetch_assoc($rq))
{
    //* Looping through categories.

    // Retrieve subcategories.
    $q2 = 'SELECT tbl_subcategories.subcat_id,
            tbl_subcategories.subcat_name
        FROM tbl_subcategories
        WHERE tbl_subcategories.cat_id = "' . $fetch['cat_id'] . '"
        ORDER BY tbl_subcategories.subcat_id DESC';
    $rq2 = mysql_query($q);

    while($fetch2 = mysql_fetch_assoc($rq2))
    {
        //* Looping through subcategories

        // << Do some stuff >>
    }
}

[amith_ami]

hi minitauros... tnx for ur reply.. its working fine now but i want to show sub category list only when mouse over on the particular category... now its showing both category and sub category when page is loaded.

pls help me to fix this...
tnx in advnc...

[minitauros]

So then you have the option to either load the subcategories while loading the page and then hide them, and show them when the user hovers over a category, OR to fetch them using an AJAX call.

Using the first option you would put your categories in a <div>, and your subcategories inside a <div> within that <div>. Give the category <div>s a recognizable class, e.g. <div class="category">, and the subcategory <div>s as well, e.g. <div class="subcategory">.

Then you include the following Javascript code on your page (assuming you're using jQuery):

$(document).ready(function()
{
    $('div.category').hover(function()
    {
        //* What happens when the user hovers over the target?

        // Show the subcategories.
        $(this).find('div.subcategories').show();
    },
    function()
    {
        //* What happens when the user's mouse leaves the target?

        // Hide the subcategories.
        $(this).find('div.subcategories').hide();
    });
});

If you want to load your subcategories using AJAX, you'd have to create a separate PHP file that retrieves only the subcategories for the category with the given ID. Then you'd use the following Javascript code (using the same setup as before):

$(document).ready(function()
{
    $('div.category').hover(function()
    {
        //* What happens when the user hovers over the target?

        // Do we need to load subcategories?
        if($(this).find('div.subcategories').html() == '')
        {
            //* The subcategories <div> is empty.

            // Load subcategories.
            $.ajax({
                // Look into how AJAX works youself ^^.
            });
        }
    },
    function()
    {
        //* What happens when the user's mouse leaves the target?

        // Hide the subcategories.
        $(this).find('div.subcategories').hide();
    });
});

Hope this helps! The jQuery AJAX documentation can be found here.

[amith_ami]

hi minitauros..... tnx for ur reply... i fixed that issue by doing some css changes now its working fine. and i will also try ajax for loading subcategories as like you mentioned.