Count items of category

zbrmaxx · September 7, 2022

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 · September 7, 2022

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 · September 7, 2022

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 · September 7, 2022

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

Barand · September 7, 2022

Run my query with your class then output as now.

Barand · September 7, 2022

You can simplify things by

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

then you have

image.png.b99675166766f966ac3eb10af84d297c.png

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 · September 7, 2022

@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 · September 7, 2022

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 image.png.ea519bf19519a6c8ec29b90de9b1faf4.png

zbrmaxx · September 8, 2022

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 · September 8, 2022

$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 · September 8, 2022

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 · September 8, 2022

I think the problem come from here:

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

Barand · September 8, 2022

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 · September 8, 2022

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 · September 8, 2022

after refresh a saw your post :d

Yes i use mysqli objects.

@Barand thank a lot man!

zbrmaxx · September 8, 2022

@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 · September 8, 2022

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 · September 8, 2022

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

Sign In

Count items of category

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information