2

this is the query:

$sql = "select pn.id, pn.purchased_number, pn.plan, pn.ring_to_group, 
        count(rtg.id) as totalGroups, 
        concat_ws(',', group_concat(rtg.group_name), group_concat(rtg.id)) as groupList
           from tbl_phone_numbers as pn
           left join tbl_rings_to_groups as rtg on rtg.user_id=pn.user_id
           where pn.user_id = ? and pn.status = ?";
$query = $this->db->query($sql, array($this->userId, 'Y'));

that returns the result of groupList like default,demo,1,2 but i want to get result something like :

default:1,demo:2

in MYSQL any suggestion??

  • try group_concat(concat(rtg.group_name, ':', rtg.id)) as groupList – ypercubeᵀᴹ May 1 '13 at 9:13
  • @ypercube, thanks i get the expected result with group_concat(concat_ws(':', rtg.group_name, rtg.id)) as groupList, also tried yours and got the same result. Thanks again to responding. – jogesh_pi May 1 '13 at 9:23
  • @ypercube i want to close this question, so please reply in answer section so that this question also helpful to others. – jogesh_pi May 1 '13 at 9:25
2

You can reverse the order of the two functions, so the concatenation of group_name and id is done first - using either CONCAT() or CONCAT_WS() - and then the group concatenation:

GROUP_CONCAT( CONCAT_WS(':', rtg.group_name, rtg.id) 
              ORDER BY rtg.id
              SEPARATOR ','                      -- the comma ',' is the default
            ) AS groupList

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.