#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Location
    Paris, France
    Posts
    15
    Rep Power
    0

    Generating a comma-separated list SQL question


    I'd really like to simplify the code for what I'm trying to do, so I'll briefly explain what I'd like to do:

    I have created a database for baseball all-star teams since 1990. What I want to do is generate a table that shows which players were all-stars for a specified team for each year.

    Example: if I were choosing the Pittsburgh Pirates (PIT)
    I would want the table to look as follows
    Year All-Star
    1990 Neal Heaton, Bobby Bonilla, Barry Bonds
    1991 Bobby Bonilla, John Smiley
    1992 Andy Van Slyke, Barry Bonds
    (...)
    where each row has a cell with the year and a cell with a list separated by commas

    The two problems I'm running into are (1) separating the players names by commas without a final comma (I get a repeated comma at the end of the final name in the list) and (2) I can't figure out how to make this code simplified into one, unified query instead of creating a query for each year (WHERE Year = 1990 and a whole new one with WHERE Year = 1991 and so on..)

    The following is what I have as it stands now, but it's obviously not outputting what I want:

    PHP Code:
    $team $_GET["Team"];
    if ( isset(
    $team)  ) 
    {
        
    $where " WHERE Team = '" mysql_real_escape_string($team) . "' ";
    } else
        
    $where "";

    $sql "SELECT Year, Pos, POS_ID, CONCAT(Fname,' ',Lname) AS Player "
            
    " FROM allstar_hist "
            
    $where "
            
    " GROUP BY Year, Pos, Player "
            
    " ORDER BY Year "
         
    $result mysql_query($sql) or die(mysql_error());


    echo 
    "<table border='0' cellpadding='2' cellspacing='0' width='700' align='center'>";
    echo 
    "<tr class='header'> <td width='50'>Year</td> <td width='650'>All-Star</td></tr>";
    echo 
    "<tr class='odd'> <td width='50'>1980</td> <td width='650'>";

    while(
    $row mysql_fetch_assoc$result ))  
    {
        
    $player $row['Player'];
        echo 
    "$player, ";


    echo 
    "</td></tr></table>"
    Have I bit off more than I can chew?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,209
    Rep Power
    4279
    Originally Posted by bayken37
    Example: if I were choosing the Pittsburgh Pirates (PIT)
    I would want the table to look as follows
    Year All-Star
    1990 Neal Heaton, Bobby Bonilla, Barry Bonds
    1991 Bobby Bonilla, John Smiley
    1992 Andy Van Slyke, Barry Bonds
    where are Pos and POS_ID here? they're in your query but not this example

    the good news is you can do the comma business all in the query

    but first, i would like to see you expand the example to show how you want Pos and POS_ID formatted
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Location
    Paris, France
    Posts
    15
    Rep Power
    0
    Oh good catch, originally I thought about sorting it by position or at least having the position display, but decided the names would suffice, so I actually don't need/plan to use the Pos or POS_ID

    those will be used primarily for other queries, but not this one.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,209
    Rep Power
    4279
    well, in that case, simplicity abounds...
    Code:
    SELECT Year
         , GROUP_CONCAT(CONCAT(Fname,' ',Lname)) AS Players
      FROM allstar_hist
     WHERE team = 'PIT'
    GROUP
        BY Year ASC
    test that in mysql before you stuff it into your php code, and let me know how you like it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Location
    Paris, France
    Posts
    15
    Rep Power
    0
    Perfect and now from here, I'd like it to look like
    1988 Player1, Player2, Player3
    1989 Player2, Player 4, ...

    instead of
    1988 Player 1
    1988 Player 2
    1988 Player 3
    1989 Player 2
    1989 Player 4
    ...
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,209
    Rep Power
    4279
    you didn't really run my query, did you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Location
    Paris, France
    Posts
    15
    Rep Power
    0
    heh THANKS!
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,451
    Rep Power
    1751
    If you want the names comma separatated, just add the SEPARATOR ', ' option to the GROUP_CONCAT:

    Code:
    GROUP_CONCAT(CONCAT(Fname,' ',Lname) SEPARATOR ',  ') AS Players
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,209
    Rep Power
    4279
    Originally Posted by SimonJM
    If you want the names comma separatated...
    If you want the names comma separatated with extra spacing...

    FTFY

    GROUP_CONCAT uses a comma separator by default
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,451
    Rep Power
    1751
    Originally Posted by r937
    If you want the names comma separatated with extra spacing...

    FTFY

    GROUP_CONCAT uses a comma separator by default
    Yes, meant 'comma space' ... I thought the default was just a comma (with no trailing space)!
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc

IMN logo majestic logo threadwatch logo seochat tools logo