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

@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";

after refresh a saw your post :d Yes i use mysqli objects. @Barand thank a lot man!

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"; ?>

I think the problem come from here: $result = $res->fetchAll(db::FETCH_OBJ);

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>'; ?>

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, Thank you for help man ! 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 thanks! but how can i write the echo with your code?

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>