Category Totals - Counting sub categories

[calumbo]

I have searched about this but can only find posts that are a year or so old. It's about counting sub category totals along with the main totals. I have a setup where I need the count to go more than one level deep. I know that you are aware of this and has been in your roadmap for the last couple of years but I'm willing to hack the code myself if you can point me in the right direction. I know that you mentioned that the forum would take a performance hit (more SQL queries I guess) but for the time being that is O.K. Any chance you could point me in the right direction or is this something you hope to implement in the very near future? Regards. ( the site is at www.trawlerpictures.net )

Edit - Other threads that mention this are:

http://www.smfhacks.com/index.php/topic,1559.0.html

http://www.smfhacks.com/index.php/topic,1100.0.html

http://www.smfhacks.com/index.php/topic,1633.0.html

[SMFHacks]

I looked at the code tonight and it would be performance hit if it was added.

Check under Classifieds.template.php then find the function GetListingTotals

This counts the totals for each category and one deep and also generates the sub categories link tree.

[calumbo]

I don't have that file - and I don't have that function anywhere in any of my files. I think you are thinking about your classifieds mod maybe getting mixed up? I am talking about Gallery Pro. I know there would be a performance hit already but it's quite important for me to have this working. I have found the equivalent code in gallery.php for SETTING (not reading) the totals, but I'm sure that If I modify the totals for the sub sections it will mean I don't need to touch gallery.template.php (for reading the totals back into the pages). The function that sets the totals is called UpdateCategoryTotals. I'm just having a bit of trouble working out the correct queries. Are your databases normalized? My head is pickled from looking at code and everything I've tried thus far hasn't worked. Regards

[SMFHacks]

Sorry meant Gallery.template.php if you need count the totals I would probably start at GetTotalByCATID

UpdateCategoryTotals just updates the total for a specific category

GetTotalByCATID get's total's from the total column for the category passed and one level deep subcategories undereath.

Each Category has  a total pictures column contains. ID_CAT which is main ID of the category and ID_PARENT which holds parent category of the category. If it is zero there is no parent and is just showed on the main gallery index.

[calumbo]

Hi Hacks,I managed to get it done and will post my code here in case anyone else is interested. Please note this code will probably hammer performance but it gets the job done. (P.S If your not happy having your code butchered in public like this then by all means edit this post). If anyone can oprtimise or otherwise clean this code up or improve it please let me know.

To get gallery totals working 3 levels deep (although you can get it as many as you want - I stopped at 3 because that's all I needed - replace the (function GetPictureTotals($ID_CAT)) in your gallery.template.php with the following code.

Code: [Select]


function GetPictureTotals($ID_CAT)
{
global $modSettings, $db_prefix, $subcats_linktree, $scripturl;

$total = 0;
$all_cat_ids = array();
$layer1_cat_ids = array();
$layer2_cat_ids = array();
$layer3_cat_ids = array();
$total += GetTotalByCATID($ID_CAT);
$subcats_linktree = '';

// Get the child categories to this category
if ($modSettings['gallery_set_count_child'])

{
$dbresult3 = db_query("
SELECT
ID_CAT, total, title
FROM {$db_prefix}gallery_cat
WHERE ID_PARENT = $ID_CAT ORDER BY roworder ASC", __FILE__, __LINE__);
while($row3 = mysql_fetch_assoc($dbresult3))
{
$subcats_linktree .= '<a href="' . $scripturl . '?action=gallery;cat=' . $row3['ID_CAT'] . '">' . $row3['title'] . '</a>&nbsp;&nbsp;';

if ($row3['total'] == -1)
{
$dbresult = db_query("SELECT COUNT(*) AS total FROM {$db_prefix}gallery_pic WHERE ID_CAT = " . $row3['ID_CAT'] . " AND approved = 1", __FILE__, __LINE__);
$row = mysql_fetch_assoc($dbresult);
$total2 = $row['total'];
mysql_free_result($dbresult);


$dbresult = db_query("UPDATE {$db_prefix}gallery_cat SET total = $total2 WHERE ID_CAT =  " . $row3['ID_CAT'] . " LIMIT 1", __FILE__, __LINE__);
}
}
mysql_free_result($dbresult3);

}

// Start Experimental
$dbresult3 = db_query("SELECT ID_CAT FROM {$db_prefix}gallery_cat WHERE ID_PARENT = $ID_CAT", __FILE__, __LINE__);
while ($layer1_cat_ids_temp = mysql_fetch_assoc($dbresult3)) {
$layer1_cat_ids[]=$layer1_cat_ids_temp['ID_CAT'];
}
mysql_free_result($dbresult3);
//YEAH! Now $layer1_cat_ids contains of all the categories one level below our target cat.

foreach ($layer1_cat_ids as $ID_CAT) {

$dbresult_temp = db_query("SELECT ID_CAT FROM {$db_prefix}gallery_cat WHERE ID_PARENT = $ID_CAT", __FILE__, __LINE__);
while ($layer2_cat_ids_temp = mysql_fetch_assoc($dbresult_temp)) {
$layer2_cat_ids[]=$layer2_cat_ids_temp['ID_CAT'];
}
}
mysql_free_result($dbresult_temp);
//YEAH! Now $layer2_cat_ids contains of all the categories two levels below our target cat.

foreach ($layer2_cat_ids as $ID_CAT) {

$dbresult_temp = db_query("SELECT ID_CAT FROM {$db_prefix}gallery_cat WHERE ID_PARENT = $ID_CAT", __FILE__, __LINE__);
while ($layer3_cat_ids_temp = mysql_fetch_assoc($dbresult_temp)) {
$layer3_cat_ids[]=$layer3_cat_ids_temp['ID_CAT'];
}
}
mysql_free_result($dbresult_temp);
//YEAH! Now $layer3_cat_ids contains of all the categories three levels below our target cat.

// Now Merge all ID_CATS together
$all_cat_ids = array_merge($layer1_cat_ids, $layer2_cat_ids, $layer3_cat_ids);

// Now Count the total for every ID_CAT and append them to total
foreach ($all_cat_ids as $ID_CAT) {

$total += GetTotalByCATID($ID_CAT);

}
// End Experimental

return $total;
}