Categories & Sub-Categories with php, mysql and smarty

Question

I'm building a custom shopping cart for a supermarket with php, mysql and smarty template the products categories and sub categories must be in tow separate tables categories table (id, catname) subcategories table (id,cat_id,subcategory) where cat_id refers to the category let say I have 2 categories and 2 subcategories for each category my php code is

$smarty = new Smarty;
$smarty->caching = true;
$smarty->cache_lifetime = 120;
$category_mysql = "SELECT * FROM category";
$run_category = mysql_query($category_mysql, $link) or die(mysql_error());
$all_category = mysql_num_rows($run_category);

$category_link = array();
$i=0;
while ($category = mysql_fetch_assoc($run_category)) {
            $ml = array(
                'id' => $category['id'],
                'catname'=> $category['catname'],
            );
            $category_link[$i++] = $ml;
}
$smarty->assign('category_link', $category_link);
$smarty->display("products.tpl");

and my html code is:

{section name=cats loop=$category_link}
<div class="category">{$category_link[cats].catname}</div>
{/section}

How could I view each sub-category below the category it belongs to?? like:

<div class="category">Category 1</div>
<div class="subcat">Sub Cat 1</div>
<div class="subcat">Sub Cat 2</div>
<div class="category">Category 2</div>
<div class="subcat">Sub Cat 3</div>
<div class="subcat">Sub Cat 4</div>

Regards

How are sub-categories associated with their respective parent categories? — RobertB
@RobertB That's exactly what I want to know I want to make subcategories show for every category and serached for solution and found none — Nova Design
I re-read your question, and see that you provided the information I was looking for. — RobertB

RobertB RobertB · Accepted Answer · 2013-01-01T02:10:19

There are any number of different ways to skin this particular cat, some more efficient than others. Below is off the top of my head and meant to get you headed in the right general direction.

In addition to your $category_link you need something that indexes your subcategories by category. Something like $subcategory_link which might be an array of arrays, indexed first by the category id (or name), and then by an incrementing integer for each subcategory (or its id, or whatever). In addition to your existing query and while loop, run a query that retrieves all of the subcategories, and then fills in $subcategory_link with the relevant information in a second, similar while loop.

$sc = array(
    'id' => $subcategory['id'],
    'subcategory' => $subcategory['subcategory'],
);
$subcategory_link[$subcategory['cat_id']][$j++] = $sc;

Note that this array is first indexed by the category id. Then in your template:

{foreach $category_link as $cat}
<div class="category">{$cat.catname}</div>
    {foreach $subcategory_link[$cat.cat_id] as $subcat}
<div class="subcat">{$subcat.subcategory}</div>
    {/foreach}
{/foreach}

Note: The above has not been checked for proper Smarty syntax. You should get the idea though.

It is left as an exercise for you to determine how to handle categories with no subcategories, and to improve efficiency.

Categories & Sub-Categories with php, mysql and smarty

1 Answers