Displaying Siblings (Database Query)

PHP
1

I have a database table (gz_life) filled with information about animals in a parent-child relationship. The scheme below shows the taxonomic chain for the lion (Panthera leo), where the field Taxon lists scientific names and Parent lists each taxon’s parents.

TAXON | PARENT
Mammalia | Vertebrata
Carnivora | Mammalia
Felidae | Carnivora
Panthera | Felidae
Panthera-leo | Panthera

I can easily display the parent (and grandparent) and know how to display the children. Here’s the code I’m using to count the sister taxa (siblings):

$stm = $pdo->prepare("select gp.Taxon, count(*) - 1 Siblings
from gz_life g
inner join gz_life gp on g.Parent = gp.Parent
where gp.Taxon = :MyURL
group by g.Parent");
 $stm->execute(array(
 'MyURL'=>$MyURL,
 ));

while ($row = $stm->fetch())
{
 $Siblings = $row['Siblings'];
}

The next step is to display a LIST of siblings. For example, if I visited the page MySite/life/panthera-leo (the lion), it would display “tiger, leopard, cheetah,” or something like that.

Can anyone show me how to do that? Thanks.

2
  1. Select lion
  2. bind CurrentParent => lion Parent
  3. bind CurrentTaxon => lion Taxon
  4. Select * from gz_life where Parent = :CurrentParent AND Taxon <> :CurrentTaxon
2 Likes
3

Awesome!

I’m not sure what you mean by “bind,” but I translated your suggestion into the following query, which works great. Thanks.

$stm = $pdo->prepare("SELECT Taxon, Parent from gz_life
 WHERE Parent = :Parent AND Taxon <> :MyURL");
 $stm->execute(array(
 'MyURL'=>$MyURL,
 'Parent'=>$Parent
 ));

while ($row = $stm->fetch())
{
 $Siblings2[] = $row['Taxon'];
}

echo join($Siblings2, ', ');
4

This is semi related. If you have a fixed number of layers in your hierarchy this may not be appropriate, but if you’re dealing with arbitrary numbers of levels then consider using the nested set model in your DB. It sometimes takes a little while to get your head around, but you can get some pretty great results. This is (in my opinion) the defacto article on the subject regarding MySQL : http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

1 Like
5

Yes, I remember seeing that article (or something similar) a few years ago when I was first learning how to work with parent-child relationships. It looks pretty impressive, and I suspect it would be more powerful/flexible than what I’m doing now - a great tool for manipulating biological data. But it’s still a little over my head. :wink: I think it would be a lot harder to set up the databases. But I may get there some day…

6

The nice thing about biological categories is that they’re well structured with species, families, genus etc, so you probably don’t need it, but it’s definitely worth considering for most categorising DBs IMO

7

I meant this part of your code:

'MyURL'=>$MyURL,
'Parent'=>$Parent

“Bind” means associating a placeholder in query with a real variable

8

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.