SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Jan 2012
    Posts
    261
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Table Join Query displays data from one table only

    Imagine a database table - let's call it Table1 - that contains animal taxa displayed in a parent-child relationship, like this:

    Felidae (Family)
    > Panthera (Genus)
    >> Lion (Species)
    >> Tiger (Species)
    > Lynx (Genus)
    >> Lynx (Species)
    >> Bobcat (Species)

    For example, the genus Lynx is the parent of the species lynx and bobcat, the genus Panthera is the parent of the species lion and tiger and the cat family (Felidae) is the parent of Panthera and Lynx.

    I have such a database table working on my computer; I can display a variety of information about animals on a website I'm working on. However, I decided to create a series of tables that will include a new taxonomic rank - SUBFAMILIES.

    I want to display a table that looks something like this:

    Big Cats (Subfamily)
    >> Lion (Species)
    >> Tiger (Species)
    Small Cats (Subfamily)
    >> Lynx (Species)
    >> Bobcat (Species)

    Notice that I'm displaying two taxonomic ranks - subfamily and species, but NOT the intermediate rank, genus.

    So I'm trying to figure out how to do that. The obvious solution is to add the subfamilies to my database table. However, that creates some problems, so I'd like to put them in a separate dartabase table. So I simply copied Table1, deleted all the species names and added the subfamilies. I had to keep the genera in order to establish a parent-child relationship with the subfamilies. So, once again, here's a snapshot of Table1, followed by Table2...

    TABLE1
    Felidae (Family)
    > Panthera (Genus)
    >> Lion (Species)
    >> Tiger (Species)
    > Lynx (Genus)
    >> Lynx (Species)
    >> Bobcat (Species)

    TABLE2
    Felidae (Family)
    > Big Cats (Subfamily)
    >> Panthera (Genus)
    > Small Cats (Subfamily)
    >> Lynx (Genus)

    Now I want to somehow join these tables so I can display the following:

    Big Cats (Subfamily)
    >> Lion (Species)
    >> Tiger (Species)
    Small Cats (Subfamily)
    >> Lynx (Species)
    >> Bobcat (Species)

    The following code, where $MyURL = 'Felidae' (The URL is MySite/Life/Felidae) works to a point, but it only displays data from Table2.

    Code:
    $Children = mysql_query ("SELECT L.N, L.Taxon, L.Parent, L.MyFam, L.Rank, P.N, P.Taxon2, P.Parent, P.MyFam, P.Rank
     FROM gz_life AS L
     RIGHT JOIN gz_parataxa AS P on P.Taxon2 = L.Taxon
     WHERE P.MyFam = '$MyURL'
     Order By P.N, P.Rank");
    In other words, it displays subfamilies and genera. I want it to 1) continue displaying subfamilies, 2) NOT display genera and 3) display species names from Table1.

    I've tried several variations, including left join instead of right join. Also, I'm not sure if I have to display the results in one row...

    Code:
    echo <<<EOD
       <tr>
         <td>$Taxon</td>
       </tr>
    EOD;
    ...or two, with one row to display the subfamilies from Table2 and a second row to display the species names from Table1:

    Code:
    echo <<<EOD
       <tr>
         <td>$Taxon</td>
       </tr>
       <tr>
         <td>$Species</td>
       </tr>
    EOD;
    Sorry for the long, convoluted explanation, and thanks for any tips.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Could you please post the test data in the two tables with the correct table and column names? Right now I don't see much connection between the "table" data (which isn't the table data but how you want to display the data) and the query.

  3. #3
    SitePoint Addict
    Join Date
    Jan 2012
    Posts
    261
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The two tables are nearly identical in structure, with the following column names - N Taxon Parent MyFam MyOrd MyCla Rank

    However, I renamed column Taxon to Taxon2 in Table2 (actually gz_parataxa). Each table has the same data except that Table1 (gz_life) lists species but not subfamilies, while gz_parataxa lists subfamilies but not species.

    N | Taxon | Parent | MyFam | MyOrd | MyCla | Rank
    1 | Felidae | Carnivora | Felidae | Carnivora | mam| 45
    2 | Panthera | Felidae | Felidae | Carnivora | mam| 55
    3 | Panthera_leo | Panthera | Felidae | Carnivora | mam | 65

    * * * * *

    N | Taxon2 | Parent | MyFam | MyOrd | MyCla | Rank
    1 | Felidae | Carnivora | Felidae | Carnivora | mam | 45
    2 | Felinae | Felidae | Felidae | Carnivora | mam | 47
    3 | Panthera | Felinae | Felidae | Carnivora | mam | 55

    I use Table1 - gz_life to display pages, where a page's URL equals a value in the column Taxon. For example, MySite/Life/Felidae is a dynamic page produced by the value Felidae. I created the second table - gz_parataxa - so I could display subfamily names.

    The column "Parent" is self-explanatory. The columns MyFam, MyOrd and MyCla display each taxon's family (e.g. Felidae), order (e.g. Carnivora) and class (e.g. mam = mammals). It makes it easier for me to select all values belonging to a particular animal family, order or class.

    Thanks!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •