Re: Hierarchical Query Question (PHP)

On 10/29/2015 7:18 PM, David Blomstrom wrote:
> Can anyone tell me how to write the query described @
> http://stackoverflow.com/questions/33402831/count-descendants-in-hierarchical-query
> ?
>
> The answer's very thorough, but I don't know how to string two queries
> and a function together like that. This doesn't work:
>
> $sql = "select * from gz_life_mammals;";
>
> create function tax_rank(id integer) returns text as $$
>      select case id
>               when 1 then 'Classes'
>               when 2 then 'Orders'
>               when 3 then 'Families'
>               when 4 then 'Genera'
>               when 5 then 'Species'
>             end;
> $$ language sql;
>
> $sql = "with recursive hier(taxon,parent_id) as (
>    select m.taxon, null::integer
>    from gz_life_mammals m
>    where taxon='Mammalia' --<< substitute me
>    union all
>    select m.taxon, m.parent_id
>    from hier, gz_life_mammals m
>    where m.parent=hier.taxon
> )
> select tax_rank(parent_id),
>         count(*) num_of_desc
> from   hier
> where  parent_id is not null
> group by parent_id
> order by parent_id;";
>
> Thanks.
>

The function is created once (like with your create tables).  Don't use
it in PHP.

Your PHP should only be like:

 > $sql = "with recursive hier(taxon,parent_id) as (
 >    select m.taxon, null::integer
 >    from gz_life_mammals m
 >    where taxon='Mammalia' --<< substitute me
 >    union all
 >    select m.taxon, m.parent_id
 >    from hier, gz_life_mammals m
 >    where m.parent=hier.taxon
 > )
 > select tax_rank(parent_id),
 >         count(*) num_of_desc
 > from   hier
 > where  parent_id is not null
 > group by parent_id
 > order by parent_id;";

$result = pg_query($dbh, $sql);

while ($row = pg_fetch_array($result)) {
    etc
    etc


-Andy