Displaying Multiple Rows from Database

[geosite]

Imagine a table (sm_ppl) with the following fields and just one row, as follows:

URL / Name_First / Name_Middle / Name_Last
Hiram_Gill / Hiram / (NULL) / Gill

Another table - sm_ppl_mayor - lists the dates Gill served as mayor, along with some other information, as follows:

URL / Term_Began / Term_Ended / Party
Hiram_Gill / 1910 / 1912 / Republican
Sam_Smith / 1912 /1914 / Democrat
Hiram_Gill / 1914 / 1916 / (NULL)

$res = mysql_query ("SELECT SMP.N, SMP.URL, SMP.Name_First, SMP.Name_Middle, SMP.Name_Last,
SMGov.URL URL_G, SMGov.Term_Began, SMGov.Term_Ended, SMGov.Party G_P, SMGov.Notes, SMGov.Image,
SMM.URL URL_M, SMM.Term_Began TermB, SMM.Term_Ended TermE, SMM.Party M_P, SMM.Notes M_N, SMM.Image ImageM,
SMW.URL, SMW.Class,
SMWA.URL, SMWA.Org, SMWA.Title, SMWA.Year_Began, SMWA.Year_Ended, SMWA.Year_Mid
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'
ORDER BY N") or die (mysql_error());


while ($row = mysql_fetch_array ($res))

{
 $NameFirst = $row['Name_First'];
 $NameMiddle = $row['Name_Middle'];
 $NameLast = $row['Name_Last'];
 $NameFull = ''.$NameFirst.' '.$NameMiddle.' '.Name_Last.'';

 $PartyM = $row['M_P'];

 $TermB = $row['TermB'];
 $TermE = $row['TermE'];

echo $NameFull;
echo $Party;
echo ''.$TermB.'-'.$TermE.'';
} 

The echo statements at the end of my script display the following:

Hiram C. Gill

Republican

1910-1911Hiram C. Gill

1914-1918

How can I modify my script so it displays the following?:

Hiram C. Gill (Republican, 1910-1911, 1914-1918)

...or this:

Hiram C. Gill (Republican), Mayor: 1910-1911, 1914-1918

Thanks.

[bdl]

echo $NameFull;

echo $Party;

echo ''.$TermB.'-'.$TermE.''; 

Actually, those statements should display

Hiram C. Gill Republican 1910-1911

..because you have no breaks or newlines in there, and you're missing an entire set of date ranges.

Is this a trick question?

To give a complete, appropriate answer, I'd have to make a bunch of assumptions about your table makeup and data. Do all people in the list have a full (i.e. First,Middle,Last) name? What about date ranges? Why do you have two sets of Term_Began and Term_Ended columns if you're only using one? Which set is relevant?

I'd make a suggestion to use CONCAT() and an IF statement to retrieve the name and some of the other columns seem 'iffy'. Care to elaborate?

[geosite]

echo $NameFull;

echo $Party;

echo ''.$TermB.'-'.$TermE.''; 

Actually, those statements should display

Hiram C. Gill Republican 1910-1911

..because you have no breaks or newlines in there, and you're missing an entire set of date ranges.

Sorry, I omitted the breaks from my original script; what I posted here is closer to what I'm trying to attain. But I don't understand what you mean regarding missing date ranges.

To give a complete, appropriate answer, I'd have to make a bunch of assumptions about your table makeup and data. Do all people in the list have a full (i.e. First,Middle,Last) name?

All of them have first and last names; middle names or initials are also listed for a few.

What about date ranges? Why do you have two sets of Term_Began and Term_Ended columns if you're only using one? Which set is relevant?

My query joins several tables with unique information, including a table listing former state governors (sm_ppl_gov) and another listing former mayors (sm_ppl_mayor). The two tables are nearly identical.

So if Sam Smith served a single term as Mayor, I want to display something like this:

Sam Smith (Republican), Mayor: 1910-1911

If he served two consecutive terms as Governor, I might display something like this:

Sam Smith (Republican), Governor: 1912-1918 (two terms)

If he served two split terms as Governor...

Sam Smith (Republican), Governor: 1912-1914, 1918-1920

If he served as Mayor AND Governor...

Sam Smith (Republican), Mayor: 1912-1918
Governor: 1922-1924

However, I just realized it may be redundant to list his name "Sam Smith," when that's the page title.

I'd make a suggestion to use CONCAT() and an IF statement to retrieve the name and some of the other columns seem 'iffy'. Care to elaborate?

I'll read up on CONCAT; I think I've used it before - it's just been a while.

Thanks.

[geosite]

P.S. I replaced the query in my first post with an updated version. I think they're about the same, but I just wanted to make sure.

[geosite]

On second thought, I do want to display each person's full name somewhere on the page. Below is a stripped down version of my query/script:

$res = mysql_query ("SELECT SMP.N, SMP.URL, SMP.Name_First, SMP.Name_Middle, SMP.Name_Last,
SMM.URL, SMM.Term_Began TermB, SMM.Term_Ended TermE, SMM.Party M_P, SMM.Notes M_N, SMM.Image ImageM
FROM sm_ppl SMP
LEFT JOIN sm_ppl_mayor SMM ON SMM.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'];
$NameFull = ''.$NameFirst.' '.$NameMiddle.' '.$NameLast.'';
$TermB = $row['TermB'];
$TermE = $row['TermE'];

echo $NameFull;
echo ''.$TermB.'-'.$TermE.''; 

It displays this:

Hiram C. Gill1910-1911Hiram C. Gill1914-1918

Can someone explain how to use CONCAT (or whatever) to change the display to this?:

Hiram C. Gill (Mayor): 1910-1911, 1914-1918

Thanks.

[Raju Gautam]

I am not sure from the SQL statement itself but i had same problem last time and i had done like this. I dont know this code is useful to you or not.

$sql = "SELECT SMP.N, SMP.URL, SMP.Name_First, SMP.Name_Middle, SMP.Name_Last,
        SMM.URL, SMM.Term_Began TermB, SMM.Term_Ended TermE, SMM.Party M_P, SMM.Notes M_N, SMM.Image ImageM
        FROM sm_ppl SMP
        LEFT JOIN sm_ppl_mayor SMM ON SMM.URL = SMP.URL
        WHERE SMP.URL = '$MyURL'";

$res = mysql_query($sql) or die (mysql_error());

while ($row = mysql_fetch_array ($res)){
    $NameFirst = $row['Name_First'];
    $NameMiddle = $row['Name_Middle'];
    $NameLast = $row['Name_Last'];
    
    $NameFull = $NameFirst . ' ' . $NameMiddle . ' ' . $NameLast;
    
    $TermB = $row['TermB'];
    $TermE = $row['TermE'];

    if($NameFull == $temp){
        echo ', ' . $TermB . '-' . $TermE . ''; 
    }else{
        echo "<br />" . $NameFull .' ' . $TermB . '-' . $TermE . '';
        $temp = $NameFull;
    }
} 

Nothing but a small trick.

[geosite]

Thanks. I have to run to work once again, but I'll check it out in the morning.

[geosite]

Yes, it looks like that does fix the problem. Thanks!