SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multi-Join Database Query Questions

    My script is working well enough to suggest that my database tables are OK, so I think my questions pertain mainly to displaying the data with PHP.

    Let's start with four tables that have information about people who have served as Governor and/or Mayor and/or corporate executives:

    1. sm_ppl - This table lists every individual included in any of these tables, with four fields, as follows:

    URL | Name_First | Name_Middle | Name_Last
    Doug_Brown | Doug | Wayne | Brown
    All the other tables have a field named URL, through which they can be joined to sm_ppl, followed by fields with additional information.

    2. sm_ppl_gov - This table has information about state governors. If Doug Brown was a former governor, the information might look like this:

    URL |Term_Began | Term_Ended | Party | Notes
    Doug_Brown | 1972 | 1976 | Republican | Brown lowered taxes.
    3. sm_ppl_mayor - This table has information about local mayors and is almost identical to sm_ppl_gov...

    URL |Term_Began | Term_Ended | Party | Notes
    Doug_Brown | 1964 | 1968 | Republican | Brown raised taxes.
    4. sm_ppl_wrrs_assoc - This table has information on corporate executives...

    URL |Org | Title | Year_Began | Year_Ended | Year_Mid
    Doug_Brown | Costco | CEO | (NULL) | (NULL) | 1972
    In the above example, I don't know what year his term began or ended, but I do know that he was CEO in 1972. (This is a fictitious example, by the way.)

    The other table in the script below - sm_ppl_wrrs - is somewhat similar.

    * * * * *

    So, what I'm trying to do is pull all this information together to present a short biography on a dynamic page. If a visitor types MySite/People/Doug_Brown/ ($MyURL = Doug_Brown) into their browser, they should get information from all the tables that feature his name.

    Below is my query, followed by some echo variables I created.

    PHP Code:
    $res mysql_query ("SELECT *
    FROM sm_ppl SMP
    LEFT JOIN sm_ppl_gov SMGov ON SMGov.URL = SMP.URL
    LEFT JOIN sm_ppl_mayor SMM ON SMM.URL = SMP.URL
    LEFT JOIN sm_ppl_wrrs SMW ON SMW.URL = SMP.URL
    LEFT JOIN sm_ppl_wrrs_assoc SMWA ON SMWA.URL = SMP.URL
    WHERE SMP.URL = '
    $MyURL'") or die (mysql_error());

    while (
    $row mysql_fetch_array ($res))
    {
    $NameFirst $row['Name_First'];
    $NameMiddle $row['Name_Middle'];
    $NameLast $row['Name_Last'];
    $Name2 ''.$NameFirst.' '.$NameLast.'';
    $Name3 ''.$NameFirst.' '.$NameMiddle.' '.$NameLast.'';
    $TermBegan $row['Term_Began'];
    $TermEnded $row['Term_Ended'];
    $Party $row['Party'];
    $Notes $row['Notes'];
    $URL $row['URL'];
    $Class $row['Class'];

    /*
    // The following PHP switch worked fine when my query only cited tables sm_ppl and sm_ppl_gov...

    switch($MyURL)
    {
     case 'Elisha_Ferry':
     echo '<p class="pfirst">'.$Name3.' ('.$Party.') is a former Governor of Washington '.$Notes.' (1872-1880 & 1889-1893).';
     break;
     case 'John_Rogers':
     echo '<p class="pfirst">'.$Name3.' (Democrat, Populist) is a former Governor of Washington '.$Notes.' ('.$TermBegan.'-'.$TermEnded.').';
     break;
     case 'Arthur_Langlie':
     echo '<p class="pfirst">'.$Name3.' ('.$Party.') is a former Governor of Washington '.$Notes.' (1941-1945 & 1949-1957).';
     break;
     default:
     echo '<p class="pfirst">'.$Name3.' ('.$Party.') is a former Governor of Washington '.$Notes.' ('.$TermBegan.'-'.$TermEnded.').';
     break;
    }
    */

    // Now that my query cites five tables, none of the echo statements below display anything except $Name3 and $Class (from table sm_ppl_wrrs)...

    echo $Name3;
    echo 
    '<br />';
    echo 
    $TermBegan;
    echo 
    '<br />';
    echo 
    $TermEnded;
    echo 
    '<br />';
    echo 
    $Party;
    echo 
    '<br />';
    echo 
    $Notes;
    echo 
    '<br />';
    echo 
    $URL;
    echo 
    '<br />';
    echo 
    $Class;

    So this is my first question:

    1. Do you see any obvious problem with my query that's preventing the other data from displaying?

    Here are some other things I'm trying to figure out:

    2. Do I need to modify my query so that I can distinguish between fields with identical names, like sm_ppl_gov.Term_Began and sm_ppl_mayor.Term_Began? If so, how do I do that?

    3. Suppose a person is featured in two tables: Governors and Corporate Executives. How can I insert the words "Governor" and "CEO," or whatever title they had, in the appropriate places, so my display looks like this...

    GOVERNOR
    1986-1990 (Republican)
    Costco
    CEO: 1992-1993
    Oyster Software, Inc.
    Chairman of the Board: 1994-1996
    I've done similar things before, but my brain isn't clicking right now. Thanks!

  2. #2
    SitePoint Evangelist
    Join Date
    May 2006
    Location
    Austin
    Posts
    401
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I've always learned to join a little differently, and reference the original table in the select on every join. I haven't ever had any problems with it.

    If there are duplicate column names across different tables, it will cause problems if you try to access them independantly. Im not sure if it is possible, you will get an ambiguous error if you try to use a specific one. Alternately you can specifically select one and use the AS command to distinguish it.

    Select SMGov.Term_Began AS Term_Began2, * From ...

    PHP Code:
    $res mysql_query ("SELECT *
    FROM sm_ppl SMP
    LEFT JOIN sm_ppl_gov SMGov ON  SMP.URL = SMGov.URL
    LEFT JOIN sm_ppl_mayor SMM ON SMP.URL = SMM.URL
    LEFT JOIN sm_ppl_wrrs SMW ON SMP.URL = SMW.URL
    LEFT JOIN sm_ppl_wrrs_assoc SMWA ON SMP.URL = SMWA .URL
    WHERE SMP.URL = '
    $MyURL'") or die (mysql_error()); 
    Anyway , hope this helps a bit.
    Merchant Equipment Store - Merchant Services, POS, Equipment, and supplies.
    Merchant Account Blog | Ecommerce Blog

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    would be a whole lot easier to assign column aliases if you stopped using the dreaded, evil "select star"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, aliases; thanks for jogging my memory. I think I can figure out the rest of it now.


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
  •