SitePoint Sponsor

User Tag List

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

    Displaying Multiple Rows from Database

    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)



    PHP Code:
    $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:

    Code:
    Hiram C. Gill
    
    Republican
    
    1910-1911Hiram C. Gill
    
    1914-1918
    How can I modify my script so it displays the following?:

    Code:
    Hiram C. Gill (Republican, 1910-1911, 1914-1918)
    ...or this:

    Code:
    Hiram C. Gill (Republican), Mayor: 1910-1911, 1914-1918
    Thanks.
    Last edited by geosite; Jun 19, 2007 at 18:28.

  2. #2
    SitePoint Zealot
    Join Date
    Jan 2004
    Location
    vta,ca,usa
    Posts
    180
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    echo $NameFull;

    echo 
    $Party;

    echo 
    ''.$TermB.'-'.$TermE.''
    Actually, those statements should display
    Code:
    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?

  3. #3
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by bdl View Post
    PHP Code:
    echo $NameFull;

    echo 
    $Party;

    echo 
    ''.$TermB.'-'.$TermE.''
    Actually, those statements should display
    Code:
    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.

  4. #4
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    PHP Code:
    $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:

    Code:
    Hiram C. Gill1910-1911Hiram C. Gill1914-1918
    Can someone explain how to use CONCAT (or whatever) to change the display to this?:

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

  6. #6
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    PHP Code:
    $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.
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  7. #7
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. I have to run to work once again, but I'll check it out in the morning.

  8. #8
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, it looks like that does fix the problem. 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
  •