Count items of category

[zbrmaxx]

Hi guys,

I'm beginner in php and i try to show total items of each category.

 

 

table prefix_channels
+--------+----------+--------------------+------+--------+
| cat_id | child_of | cat_name           | type | filter |
+--------+----------+--------------------+------+--------+
|   1    |    0     | parent_category_1  |  3   |   1    |
|   2    |    1     | subcategory_a      |  3   |   1    |
|   3    |    1     | subcategory_b      |  3   |   1    |
|   4    |    1     | subcategory_c      |  3   |   1    |
|   5    |    0     | parent_category_2  |  3   |   1    |
|   6    |    0     | parent_category_3  |  3   |   1    |



table prefix_images
+-----+---------_+-------------------+-------+--------+
| id  | category | title             |  pub  | filter |
+-----+-------_--+-------------------+-------+--------+
|  1  |    2     | image_1           |   1   |    1   |
|  2  |    2     | image_2           |   1   |    1   |
|  3  |    3     | image_3           |   1   |    1   |
|  4  |    3     | image_4           |   1   |    1   |
|  5  |    0     | image_5           |   1   |    1   |
|  6  |    5     | image_6           |   1   |    1   |
|  7  |    5     | image_7           |   1   |    1   |
|  8  |    6     | image_8           |   1   |    1   |


the result should be =>
    parent_category_1 (4)
    parent_category_2 (2)
    parent_category_3 (1)




My Code show only parent categories=>
    parent_category_1 
    parent_category_2 
    parent_category_3 



<?php 

$image = $db->get_results("select ".DB_PREFIX."channels.* from ".DB_PREFIX."channels where filter = 0 and child_of=0 order by cat_name ASC ".this_limit()."");
if($gif) { 
foreach ($image as $images) {
echo '<ul><li class="lihead"><a href="'.channel_url($images->cat_id,$images->cat_name).'" "'.$images->cat_name.'""'.$images->cat_name.'"><i class="material-icons">&#xe413;</i>
'._html($images->cat_name).'</a></li>';
}
}
echo '</ul></div>';
?>
</div>

 

[ginerjm]

I don't see how you get those counts at all.  Perhaps you could tell us the relationship between your 2 tables?

PS - be sure you have php errors turned on.  I think you have an error in your complicated output line

Edited September 7, 2022 by ginerjm

[Barand]

Indirect owners of images
UNION
Direct owners of images

eg

SELECT c1.cat_name
     , count(i.id) as total
FROM prefix_channels c1
     JOIN prefix_channels c2 ON c2.child_of = c1.cat_id
     JOIN prefix_images i ON i.category = c2.cat_id
GROUP BY c1.cat_name
UNION
SELECT c1.cat_name
     , count(i.id) as total
FROM prefix_channels c1
     JOIN prefix_images i ON i.category = c1.cat_id
WHERE c1.child_of = 0
GROUP BY c1.cat_name;
+-------------------+-------+
| cat_name          | total |
+-------------------+-------+
| parent_category_1 |     4 |
| parent_category_2 |     2 |
| parent_category_3 |     1 |
+-------------------+-------+

 

[zbrmaxx]

@Barand thanks! but how can i write the echo with your code?

[Barand]

Run my query with your class then output as now.

[Barand]

You can simplify things by

• always ensuring categories have at least one subcategory, and
• always linking images to the subcategories

then you have

so that

mysql> SELECT c1.cat_name
    ->      , count(i.id) as total
    -> FROM prefix_channels c1
    ->      JOIN prefix_channels c2 ON c2.child_of = c1.cat_id
    ->      JOIN prefix_images i ON i.category = c2.cat_id
    -> GROUP BY c1.cat_name;
+-------------------+-------+
| cat_name          | total |
+-------------------+-------+
| parent_category_1 |     4 |
| parent_category_2 |     2 |
| parent_category_3 |     1 |
+-------------------+-------+

 

[zbrmaxx]

@Barand, Thank you for help man !

 

Quote

always ensuring categories have at least one subcategory, and

always linking images to the subcategories

All the time is in this way

 

I think I have a lot to learn man because nothing works. Here is your code

<?php
$db->debug();

$test = $db->get_result("SELECT c1.cat_name, count(i.id) as total FROM ".DB_PREFIX."channels c1 JOIN ".DB_PREFIX."channels c2 ON c2.child_of = c1.cat_id
   JOIN ".DB_PREFIX."images i ON i.category = c2.cat_id, GROUP BY c1.cat_name UNION SELECT c1.cat_name,  count(i.id) as total FROM ".DB_PREFIX."channels c1 JOIN ".DB_PREFIX."images i ON i.category = c1.cat_id WHERE c1.child_of = 0, GROUP BY c1.cat_name ");


$test2 = $db->get_result("SELECT c1.cat_name , count(i.id) as total FROM ".DB_PREFIX."channels c1 JOIN ".DB_PREFIX."channels c2 ON c2.child_of = c1.cat_id JOIN ".DB_PREFIX."images i ON i.category = c2.cat_id GROUP BY c1.cat_name");




?>

 

[Barand]

I'd do something like this (although my output is simplified as I have no idea what your methods are producing)...

$res = $pdo->query("SELECT c1.cat_name
                          , count(i.id) as total 
                    FROM prefix_channels c1
                          JOIN prefix_channels c2 ON c2.child_of = c1.cat_id
                          JOIN prefix_images i ON i.category = c2.cat_id 
                    GROUP BY c1.cat_name 
                    UNION 
                    SELECT c1.cat_name
                          , count(i.id) as total 
                    FROM prefix_channels c1
                          JOIN prefix_images i ON i.category = c1.cat_id 
                    WHERE c1.child_of = 0 
                    GROUP BY c1.cat_name
                    ");
$result = $res->fetchAll(PDO::FETCH_OBJ);
echo "<ul>\n";
foreach ($result as $row) {
    echo "<li>$row->cat_name ($row->total)</li>\n";
}
echo "</ul>\n";

giving

[zbrmaxx]

Fatal error: Uncaught Error:
Call to a member function query() on null in /tpl/main/sidebar.php:7 
Stack trace: #0 /lib/functions.php(452): include_once() #1 /lib/functions.plugins.php(144): 
the_side(Array) #2 /lib/functions.plugins.php(199): 
apply_filter('vibe_sidebar', Array) #3 /lib/functions.php(433): 
do_action('vibe_sidebar') #4 /tpl/main/dashboard.php(2): 
the_sidebar() #5 /com/com_dashboard.php(101): include_once('/tpl/main/dashb...') #6 /index.php(169): 
include_once('/com/com_dashbo...') #7 {main} thrown in /tpl/main/sidebar.php on line 7

Now  result some conflicts. I will try to solve this.

Thank you very much @Barand

[Barand]

$pdo in my code is a PDO object connecting to the database server.

Example from PHP manual 

/* Connect to a MySQL database using driver invocation */
$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

$pdo = new PDO($dsn, $user, $password);

 

[zbrmaxx]

yes, in my case is $db and prefix is .DB_PREFIX. and the code look like this :

<?php 

# Categories 

$res = $db->get_results("SELECT c1.cat_name
                          , count(i.id) as total 
                    FROM ".DB_PREFIX."channels c1
                          JOIN ".DB_PREFIX."channels c2 ON c2.child_of = c1.cat_id
                          JOIN ".DB_PREFIX."images i ON i.category = c2.cat_id 
                    GROUP BY c1.cat_name 
                    UNION 
                    SELECT c1.cat_name
                          , count(i.id) as total 
                    FROM ".DB_PREFIX."channels c1
                          JOIN ".DB_PREFIX."images i ON i.category = c1.cat_id 
                    WHERE c1.child_of = 0 
                    GROUP BY c1.cat_name
                    ");
$result = $res->fetchAll(db::FETCH_OBJ);
echo "<ul>\n";
foreach ($result as $row) {
    echo "<li>$row->cat_name ($row->total)</li>\n";
}
echo "</ul>\n";
?>

Call to a member function fetchAll() on array in /tpl/main/sidebar.php:97 Stack trace: #0 /lib/functions.php(452): include_once()

code function.php (452)

function the_side(){
global $db, $cachedb;
include_once(TPL.'/sidebar.php');
}

This code only shows the categories and works perfect :

<?php 

# Categories  

$image = $db->get_results("select ".DB_PREFIX."channels.* from ".DB_PREFIX."channels where child_of=0 order by cat_name ASC ".this_limit()."");
if($image) { 
foreach ($image as $images) {
echo '<ul><li class="lihead"><a href="'.channel_url($images->cat_id,$images->cat_name).'" "'.$images->cat_name.'""'.$images->cat_name.'"><i class="material-icons">&#xe413;</i>
'._html($images->cat_name).'</a></li>';
}
}
echo '</ul></div>';
?>

 

[zbrmaxx]

I think the problem come from here:

$result = $res->fetchAll(db::FETCH_OBJ);

 

[Barand]

fetchAll() is a PDO method. It won't work if you're using mysqli objects.

An alternative without it would be

$res = $db->query("SELECT c1.cat_name
                          , count(i.id) as total 
                    FROM ".DB_PREFIX."channels c1
                          JOIN ".DB_PREFIX."channels c2 ON c2.child_of = c1.cat_id
                          JOIN ".DB_PREFIX."images i ON i.category = c2.cat_id 
                    GROUP BY c1.cat_name 
                    UNION 
                    SELECT c1.cat_name
                          , count(i.id) as total 
                    FROM ".DB_PREFIX."channels c1
                          JOIN ".DB_PREFIX."images i ON i.category = c1.cat_id 
                    WHERE c1.child_of = 0 
                    GROUP BY c1.cat_name
                    ");

echo "<ul>\n";
foreach ($res as $row) {
    echo "<li>{$row['cat_name']} ({$row['total']})</li>\n";
}
echo "</ul>\n";
?>

 

[zbrmaxx]

SOLVED!

 

<h4 class="li-heading"><?php echo _lang('Flirty'); ?></h4>
<?php 

# Categories

$res = $db->get_results("SELECT c1.cat_name
                          , count(i.id) as total 
                    FROM ".DB_PREFIX."channels c1
                          JOIN ".DB_PREFIX."channels c2 ON c2.child_of = c1.cat_id
                          JOIN ".DB_PREFIX."images i ON i.category = c2.cat_id 
                    GROUP BY c1.cat_name 
                    UNION 
                    SELECT c1.cat_name
                          , count(i.id) as total 
                    FROM ".DB_PREFIX."channels c1
                          JOIN ".DB_PREFIX."images i ON i.category = c1.cat_id 
                    WHERE c1.child_of = 0 
                    GROUP BY c1.cat_name
                    ");

echo "<ul>\n";
foreach ($res as $row) {
    echo "<li>$row->cat_name ($row->total)</li>\n";
}
echo "</ul>\n";
?>

 

[zbrmaxx]

after refresh a saw your post  :d

Yes i use  mysqli objects.

 

@Barand thank a lot man!

[zbrmaxx]

@Barand one more question:

how can i extract id of curent cat_name  from code? i use it to create the link of category, and how integrate $gifs->total in this relation?

Thanks a lot man

foreach ($image as $images) {

	echo '<ul><li class="lihead"><a href="'.channel_url($images->cat_id,$images->cat_name).'" "'.$images->cat_name.'"><i class="material-icons">&#xe7a3;</i>
'._html($images->cat_name).'</a></li>';
}
echo "</ul>\n";

 

Edited September 8, 2022 by zbrmaxx

[Barand]

The same way that you are doing now. Did you read the code that you posted?

foreach ($res as $row) {
    echo "<li>$row->cat_name ($row->total)</li>\n";
              ^^^^^^^^^^^^^^  ^^^^^^^^^^^ 
                 cat_name        total
}

 

[zbrmaxx]

yes, that code  give me, only name and number of items  (channel cat_name, and total of items)

i need  the channel cat_name, cat_id and total of items. But i solved it :d

For my url 

.channel_url($images->cat_id,$images->cat_name)

cat_id is important.

I also added c1.cat_id in your query.

$gif = $db->get_results("SELECT c1.cat_name, c1.cat_id
                          , count(i.id) as total 
                    FROM ".DB_PREFIX."channels c1
                          JOIN ".DB_PREFIX."channels c2 ON c2.child_of = c1.cat_id and c1.filter = 1 
                          JOIN ".DB_PREFIX."images i ON i.category = c2.cat_id and i.pub = 1
                    GROUP BY c1.cat_id 
                    UNION 
                    SELECT c1.cat_name, c1.cat_id
                          , count(i.id) as total 
                    FROM ".DB_PREFIX."channels c1
                          JOIN ".DB_PREFIX."images i ON i.category = c1.cat_id and i.pub = 1
                    WHERE c1.child_of = 0  
                    GROUP BY c1.cat_id
                    order by cat_name ASC");

echo "<ul>\n";
foreach ($image as $image) {

	echo '<ul><li class="lihead"><a href="'.channel_url($images->cat_id,$images->cat_name).'" <i class="material-icons">&#xe7a3;</i>
'._html($images->cat_name).''._html( $images->total).'</li>';
}
echo "</ul>\n";
$db->debug();

now it's ok, it works. 
thank you again man