mysql query join group_concat

debugcn Published at Dev

user3117183

I have the following query,

 SELECT 
     p.id AS parent, GROUP_CONCAT( c.id ) AS children
 FROM 
     rev_r_clients AS p
 JOIN 
     rev_r_clients AS c ON c.parent_client_id = p.id
 GROUP BY 
     p.id

It get the id as parent and the children of the parent. After that I send the mapping id to array using the following function,

$parents = Array();

while ($row = $res->fetch_assoc()) {
    $parents[$row['parent']] = explode(',',$row['children']);
}

I need it to get a username from another table (rev_users) as well, how could this be done?

user2923779

If you don't want to achieve this with two queries (or alternatively one MySQL transaction using a subquery) then you could concat like this:

SELECT 
    p.id AS parent_id, p_u.name, GROUP_CONCAT( CONCAT(c.id, '-', c_u.name) ) AS children
FROM 
    rev_r_clients AS p
JOIN 
    rev_r_clients AS c ON c.parent_client_id = p.id
JOIN 
    rev_users c_u ON c_u.id= c.id
JOIN 
    rev_users p_u ON p.id = p_u.id
GROUP BY 
    p.id

Be careful doing this though. There are limits on how big a GROUP_CONCAT can get. See the documentation http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

And your code would look like

$parents = Array();
while($row = $res->fetch_assoc()) {
  $children = explode(',',$row['children']);
  foreach($children as $child){
    $child_info = explode('-', $child);
    if(!isset($parents[$row['parent_id']])){
      $parents[$row['parent']] = array(
        'id' => $row['parent'],
        'name' => $row['name'],
        'children' => array()
      )
    }
    $parents[$row['parent']]['children'][] = array(
      'id' => $child_info[0],
      'name' => $child_info[1]
    );
  }
}

Lastly be careful to use a separator in your CONCAT function that you know will not be in the name.

Collected from the Internet

Please contact [email protected] to delete if infringement.