I am developing a simple system that has unlimited categories and items in each category. For instance there could be categories inside categories etc (Category 1 -> Category 2 -> Category 3) all containing items. I want to display the total items for each category and ALL of their subcategories. I am trying to figure out a loop to do this but basically am coming up short. I am building in PHP/MySQL. My category table schema is category(id, id_parent) id being auto increment and id_parent being whether it resides inside another category (0 is default). My items schema is items(id, id_category). Obviously there are other columns but these are the only ones that matter I believe. Can someone please help me develop a loop structure come up with a total amount of items for the category they are in (counting all subcategories items as well). Here is what I started although I feel it is very, very wrong.
function CountChildDownloads($id_category)
{
global $smcFunc;
$x = array();
$total = 0;
$request = $smcFunc['db_query']('', '
SELECT COUNT(*) AS items FROM {db_prefix}xld_downloads
WHERE id_category = '.$id_category.''
);
$total += $request['items'];
$request = $smcFunc['db_query']('', '
SELECT id FROM {db_prefix}xld_categories
WHERE id_parent = '.$id_category.''
);
if($smcFunc['db_num_rows']($request) > 0) {
while($row = $smcFunc['db_fetch_assoc']($request)) {
$x[] = $row['id'];
}
}
foreach ($x as $id)
{
$y = array();
$z = 0;
$request = $smcFunc['db_query']('', '
SELECT COUNT(*) AS items FROM {db_prefix}xld_downloads
WHERE id_category = '.$id.''
);
$z += $request['items'];
$request = $smcFunc['db_query']('', '
SELECT id FROM {db_prefix}xld_categories
WHERE id_parent = '.$id.''
);
if($smcFunc['db_num_rows']($request) > 0) {
while($row = $smcFunc['db_fetch_assoc']($request)) {
$y[] = $row['id'];
}
}
while (count($y) > 0)
{
$id_y = $y[0];
$request = $smcFunc['db_query']('', '
SELECT id FROM {db_prefix}xld_categories
WHERE id_parent = '.$id_y.''
);
if($smcFunc['db_num_rows']($request) > 0) {
while($row = $smcFunc['db_fetch_assoc']($request)) {
$y[] = $row['id'];
}
}
$request = $smcFunc['db_query']('', '
SELECT COUNT(*) AS items FROM {db_prefix}xld_downloads
WHERE id_category = '.$id_y.''
);
$z += $request['items'];
unset($y[0]);
array_values($y);
}
$total += $z;
}
return $total;
}
The $smcFunc is just the systems way to do queries. It is a MySQL backend.
Working function (will need to update to standard MySQL calls if not using SMF) Thanks Lucas.
function CountChildDownloads($id_category)
{
global $smcFunc;
$x = array();
$total = array();
$total['downloads'] = 0;
$total['views'] = 0;
$request = $smcFunc['db_query']('', '
SELECT views FROM {db_prefix}xld_downloads
WHERE id_category = '.$id_category.''
);
if($smcFunc['db_num_rows']($request) > 0) {
while($row = $smcFunc['db_fetch_assoc']($request)) {
$total['downloads']++;
$total['views'] += $row['views'];
}
}
$request = $smcFunc['db_query']('', '
SELECT id FROM {db_prefix}xld_categories
WHERE id_parent = '.$id_category.''
);
if($smcFunc['db_num_rows']($request) > 0) {
while($row = $smcFunc['db_fetch_assoc']($request)) {
$x[] = $row['id'];
}
}
foreach ($x as $id)
{
$y = array();
$z = 0;
$w = 0;
$request = $smcFunc['db_query']('', '
SELECT views FROM {db_prefix}xld_downloads
WHERE id_category = '.$id.''
);
if($smcFunc['db_num_rows']($request) > 0) {
while($row = $smcFunc['db_fetch_assoc']($request)) {
$z++;
$w += $row['views'];
}
}
$request = $smcFunc['db_query']('', '
SELECT id FROM {db_prefix}xld_categories
WHERE id_parent = '.$id.''
);
if($smcFunc['db_num_rows']($request) > 0) {
while($row = $smcFunc['db_fetch_assoc']($request)) {
$y[] = $row['id'];
}
}
while (count($y) > 0)
{
$id_y = $y[0];
if (!empty($id_y))
{
$request = $smcFunc['db_query']('', '
SELECT id FROM {db_prefix}xld_categories
WHERE id_parent = {int:id_parent}',
array(
'id_parent' => $id_y,
)
);
if($smcFunc['db_num_rows']($request) > 0) {
while($row = $smcFunc['db_fetch_assoc']($request)) {
$y[] = $row['id'];
}
}
$request = $smcFunc['db_query']('', '
SELECT views FROM {db_prefix}xld_downloads
WHERE id_category = {int:id_category}',
array(
'id_category' => $id_y,
)
);
if($smcFunc['db_num_rows']($request) > 0) {
while($row = $smcFunc['db_fetch_assoc']($request)) {
$z++;
$w += $row['views'];
}
}
}
unset($y[0]);
$y = array_values($y);
}
$total['downloads'] += $z;
$total['views'] += $w;
}
return $total;
}