SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Thread: Group_concat

  1. #1
    SitePoint Enthusiast adamschroeder's Avatar
    Join Date
    Mar 2011
    Location
    World
    Posts
    80
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Group_concat

    Hi guys,
    I have a database with users on it. Every user has a number of rows according to the amount of countries he wants to visit. All the information is the same except for county colunm.
    So what I'm trying to do, and not succeeding, is to dispaly all the information of each user and the additional countries they'd like to see. But I'm having trouble grouping all the countries they'd like to see according to each user (member_id).

    Any help appreciated.
    Thank you.


    Code MySQL:
    SELECT mainweb.* 
          , GROUP_CONCAT(mainweb.country) AS country //(here is where I try to group additional countries each user woud like to see)
    	FROM mainweb
        WHERE mainweb.country = '$country_form' 
    	GROUP
        BY mainweb.member_id 
    	ORDER BY date DESC 
    	LIMIT $start, $per_page

    PHP Code:
    while ($row mysql_fetch_assoc($get))
    {     
    $lastname$row['lastname'];
    $firstname$row['firstname'];
    $country$row['country'];

    echo 
    $firstname $lastname;//and other info user has

    //here I try to explode the string of countries I grouped according to member_id. 
    $countries explode(",",$country);
    foreach(
    $countries as $c)
        echo 
    "additional countries they'd like to see: .$c";


  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    if you do this --
    Code:
    WHERE mainweb.country = '$country_form'
    then you will retrieve only rows with that one country

    so then when you do this --
    Code:
    GROUP_CONCAT(mainweb.country) AS country
    then there's only going to be that one country concatenated
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •