Multi-Join Database Query Questions

**geosite** · Jun 19, 2007 05:32

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:

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:

3. sm_ppl_mayor - This table has information about local mayors and is almost identical to sm_ppl_gov...

4. sm_ppl_wrrs_assoc - This table has information on corporate executives...

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!

**jestep** · Jun 19, 2007 07:46

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.