Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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??

share|improve this question
    
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
add comment

1 Answer 1

up vote 1 down vote accepted

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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