CODEWITHSUNDEEP
phpmysql
Ahmad
Ahmad

Reputation: 249

get total childs items form table , mysql/php

I have a table with child culmon

if child culmon = 0 , so the it's Main Item , else

the item is child of the other item

Table Items :

-------------------------------
id  |  title             |  child
1   | A main item 1      |   0
2   | B main item 2      |   0
3   | K child of item_2  |   2
4   | R child of item_1  |   1
5   | Q child of item_1  |   1
--------------------------------

I want one query to get data like this if it's possible :

   ---------------------------------
    + Main item 1  (total 2 childs)
        - R
        - Q
    + Main item 2  (total 1 child)
        - K

I did a lot of searchs but I did not get it ! I can use join if there is two tables , but I did not know how can I do that if I use just one table .. umm , any help will be most appreciated ..

*EDIT .. I did this : *

my table like this :

-------------------------------
id  |  title             |  child
1   | main_item1         |   0
2   | main_item2         |   0
3   | child item         |   1
4   | another child item |   1
5   | child for main2    |   2
--------------------------------

Query :

  SELECT *
  FROM `items` AS `t1`
  INNER JOIN `items` AS `t2`

  WHERE `t1`.`child` = `t2`.`id` and t2.user_id = $user_id

and I get this :

-main_item_1
-main_item_1
-main_item2

any help ?

------------------------ SOLVED ! --------------------------------------------

sorry I can not answer my own question , so I edited my question here ..

Thank you guys for your help ..

and thanks for @Shi ( I made changes for his code :) )

I just did this simple query to get all of my main items and total of children for each main item .. like this :

  SELECT *,
     (SELECT COUNT(1)
      FROM `items`
      WHERE child = t1.id )
      AS  total_childs

  FROM `itmes` AS `t1`
  where `t1`.`child` = 0

  GROUP BY t1.id

and I get data like this :

then I did another query to get all items ( children items )

and inside template page I just write IF statement to get children of each item some thing like that (I use Smarty Template) ;

{foreach from=$items item=i} 
{$i.title} ( total children : {$i.total_childs} )

// did another foreach to get children 
{foreach from=$children item=c} 
  {if $c.child eq $i.id}
  <li> {$c.title} </li>
  {/if}
{/foreach}


{/foreach}

of course if there is a better way to do that , it's nice if someone help us with it :)

thanks again ..

Upvotes: 1

Views: 266

Answers (3)

RiaD
RiaD

Reputation: 47650

It won't work with trees but should works for 2-level if child always > id:

$lastid=-1,$names=array();
$a=mysql_query('SELECT * FROM tblname ORDER BY child ASC');
while($b=mysql_fetch_assoc($a)){
    if(!$b['child']){
         $names[$b['id']]=$b['name']; continue;
    }
    if($lastid!=$b['child'])
        print 'Childs of '.$names['child'].'<br>';
    print $b['name'].'<br>';
    $lastid=$b['child'];
}

Upvotes: 0

lovesh
lovesh

Reputation: 5411

For example if you want to get the count of children of id 1 then use this query

select count(child) from Items where child ='1'

to get the titles of children of id 1 then use this query

select title from Items where child ='1'

to get count of children of all rows grouped by child values use 

select count(id),child from Items where child in (1,2,3,4,5) group by child

the following query will show you all elements which are children

SELECT t1.id, t1.title, t1.child
FROM `Items` AS `t1`
INNER JOIN `Items` AS `t2`
WHERE `t1`.`child` = `t2`.`id`
AND t1.id
IN ( 1, 2, 3, 4, 5 )

Upvotes: 1

Shi
Shi

Reputation: 4258

You need to join the table with itself:

SELECT * FROM `table` AS `t1` INNER JOIN `table` AS `t2` ON `t1`.`child` = `t2`.`id`;

If you want to store larger and deeper nested trees, I recommend using nested sets.

Upvotes: 1

Related Questions