Foreach Loop

[Mythotical]

I'm trying loop categories so I get main category then the sub cats listed below them for a download section mod I'm building.

Here's the code thus far:

PHP Code:

     $cats = $vbulletin->db->query("SELECT * FROM `" . TABLE_PREFIX . "xsdl_cats` WHERE sub_cat = '0' AND par_cat = '0'");

    while($category = $vbulletin->db->fetch_array($cats))
        {
                $cid = $category['cid'];
                $cname = $category['cat_name'];
                // $rating = round($entry['rating']);
                $description = $category['description'];
                $subcat = $category['sub_cat'];
                $file_loc = $category['file_loc'];
                $screen_loc = $category['screen_loc'];
 
                $query = $db->query_first("
                      SELECT COUNT(`dlid`) AS `total`
                    FROM `" . TABLE_PREFIX . "xs_downloads`
                    WHERE cat_id = " . $category['cid'] . "
                ");
 
                $subs = $vbulletin->db->query("SELECT * FROM `" . TABLE_PREFIX . "xsdl_cats` WHERE sub_cat = '1' AND par_cat = '$cid'");
                while($sub_cats = $vbulletin->db->fetch_array($subs))
                {
                    $scid = $sub_cats['cid'];
                    $scname = $sub_cats['cat_name'];
                    // $srating = round($sub_cats['rating']);
                    $sdescription = $sub_cats['description'];
                    $ssubcat = $sub_cats['sub_cat'];
                    $sfile_loc = $sub_cats['file_loc'];
                    $sscreen_loc = $sub_cats['screen_loc'];
                }
 
                $count = $query['total'];
                $key[0] = $cid;
                $key[1] = $cname;
                $key[2] = $description;

                foreach ($key as $value)
                {
                echo $value;
                    $sub[0] = $scid;
                    $sub[1] = $scname;
                    $sub[2] = $sdescription;
                    foreach ($sub as $value)
                        {
                        echo '<a href="downloads.php?do=cat&amp;id=' . $value . '">' . $value . '</a><br />' . $value . '';
                        }
                } 


Any help much appreciated.
Steve

[Marco van Herwaarden]

And the problem is?

[Mythotical]

Oh sorry, the problem is that they will display but I'm not sure how to get them to display like so:

Main Cat 1
- SubCat2 | - SubCat3
- SubCat4
Main Cat 2
- SubCat5 | - SubCat6
- SubCat7 | - SubCat8

But all I get from that foreach is a list of all categories that just repeat. So maybe I'm missing something.

Thanks
Steve

Why not just select all the categories and order them in an array properly in a single loop? You will be generating quite a few queries with your current method....

[Mythotical]

Actually, what you suggested Danny is what I'm trying but failing horribly. Any code suggestions?

[Marco van Herwaarden]

Please provide the table structure and sample data.

[Tefra]

it's a simple structure with only one level of subcategories right ?

PHP Code:

$sql = $db->query_read("SELECT * FROM `" . TABLE_PREFIX . "xsdl_cats` WHERE 1");
while ($row = $db->fetch_array($sql))
{
    if($row['sub_cat'])
    {
        $cats[$row['par_cat']] = $row;
    }
    else
    {
        $cats['0']    =    $row;
    }
}


foreach($cats as $key => $value)
{
    if($key > 0) continue;
    
    echo $value['cat_name'].'<br />';
    
    foreach($cats as $k => $v)
    {
        if($k == 0 OR $k != $key) continue;
        echo '- '.$v['cat_name'].' | ';
    }
} 


Something like:

PHP Code:

$ordered_cats = array();
while ($cat = $vbulletin->db->fetch_array($cats))
{
    if(!$cat['sub_cat'])
    {
         $ordered_cats[$cat['id']] = $cat;
    }
    else {
         $ordered_cats[$cat['parent_cat']]['subcats'] = $cat;
    }
} 


That's not tested..but should point you in the right direction, hopefully. You can order by the sub_cat column and make sure that all parents are selected first.

[Mythotical]

Tefra: sub_cat is no longer used, all I use now is par_cat. Here is an updated piece of code.

PHP Code:

    $number_cats = 0; 
    $cats = $vbulletin->db->query("SELECT * FROM `" . TABLE_PREFIX . "xsdl_cats` WHERE par_cat = '$id' ORDER BY cid"); 
        while($category = $vbulletin->db->fetch_array($cats)) 
        { 
            $cid = $category['cid']; 
            $cname = $category['cat_name']; 
            // $rating = round($entry['rating']); 
            $description = $category['cat_description']; 
            $parcat = $category['par_cat']; 
            $file_loc = $category['file_loc']; 
            $screen_loc = $category['screen_loc']; 
    
            $query = $db->query_first(" 
                SELECT COUNT(`dlid`) AS `total` 
                FROM `" . TABLE_PREFIX . "xs_downloads` 
                WHERE cat_id = " . $category['cid'] . "
            "); 
             
            $count = $query['total'];
            $show['open_row'] = $number_cats++ %2 == 0;
            eval('$downloads_bit .= "' . fetch_template('xs_downloads_bit') . '";'); 
        } 


What I did is use an if condition in my template:

<if condition="$number_cats == '0'">do nothing<else />begin displaying sub cats</if>

Now what I'm thinking to cut down on queries is setup an if statement in the file:

PHP Code:

if(THIS_SCRIPT == 'downloads' AND !empty($_GET['id']))
{
$mid = 0;
} else {
$mid = $id;
} 


That would combine my 2 cat queries I'm having to run in the file.

Now the foreach confused me because I don't use sub_cat any longer, if you could redo the example using par_cat.

Danny.VBT, your more than welcome to answer my questions above. Now the example code you posted I didn't understand, can you explain each portion or what its doing example wise?

Thanks
Steve

[Tefra]

I suggest you one really helpful change to calculate the total entries of each category during the add,delete,move of the download entries. This way you will save the count query.

PHP Code:

// Part One This could be cached in datastore to save even more queries//
// The cache should be re-written each time you manage the categories and after the add/delete/move of a download entry //
$sql = $db->query_read("SELECT * FROM `" . TABLE_PREFIX . "xsdl_cats` WHERE 1");
while ($row = $db->fetch_array($sql))
{
    $catcache[$row['cid']] =  $row;
    $icatcache[$row['par_cat']][$row['cid']] = $row['cid'];
}

$vbulletin->input->clean_array_gpc('g', 'id', TYPE_INT);
$id = (empty($vbulletin->GPC['id'])) 0 : $vbulletin->GPC['id'];

// Part Two Build teh cat list //
foreach ($icatcache[$id] AS $cid)
{
    if (empty($icatcache[$id])) continue;
    
    $cname             = $catcache[$cid]['cat_name'];
    $description     = $catcache[$cid]['cat_description']; 
    $file_loc         = $catcache[$cid]['file_loc']; 
    $screen_loc     = $catcache[$cid]['screen_loc']; 
    $count            = xs_calc_total($cid);
    foreach($icatcache[$cid] AS $subcid) // Grab one level of subcats //
    {
    
        $sub_cname             = $catcache[$subcid]['cat_name'];
        $sub_description     = $catcache[$subcid]['cat_description']; 
        $sub_file_loc         = $catcache[$subcid]['file_loc']; 
        $sub_screen_loc     = $catcache[$subcid]['screen_loc'];
        eval('$downloads_subs_bit .= "' . fetch_template('xs_downloads_subs_bit') . '";'); 
    }    
    eval('$downloads_bit .= "' . fetch_template('xs_downloads_bit') . '";'); 
}

function xs_calc_total($id, $total = 0)
{
    global $icatcache, $catcache;
    
    $total += $catcache[$id]['total']; // Grab the total from the current category //
    
    foreach($icatcache[$id] AS $cid) // Recursive grab the total from all the subs //
    {
        $total += xs_calc_total($cid, $total);
    }
    return $total;
}