How to merge MySQL query result with JSON format

Question

Hi i am trying to merge output of MySQL result in JSON format but i confused how i can do this so guys i need your helps please tell me how i can do this work thank you.

SQL:

$result = $db->sql_query("SELECT a.*,i.member_id,i.members_seo_name
    FROM ".TBL_IPB_USER." i 
    LEFT JOIN ".TBL_IPB_LA." a 
    ON a.member_id=i.member_id 
    WHERE i.".$column." = '".$val."' AND a.game = '".$game."'");

    while( $dbarray = $db->sql_fetchrow($result) ){
        $arr[] = $dbarray;
    }

    return ($arr);

The normal result and output with JSON format for my query is:

{
    "status": 200,
    "result": [
        {
            "member_id": "1",
            "member_name": "maxdom",
            "ip_address": "177.68.246.162",
            "session_onlineplay": "1",
            "sid": "IR63374a32d1424b9288c5f2a5ce161d",
            "xuid": "0110000100000001",
            "serialnumber": "9923806a06b7f700a6ef607099cb71c6",
            "game": "PlusMW3",
            "members_seo_name": "maxdom"
        },
        {
            "member_id": "1",
            "member_name": "maxdom",
            "ip_address": "81.254.186.210",
            "session_onlineplay": "1",
            "sid": "IR3cd62da2f143e7b5c8f652d32ed314",
            "xuid": "0110000100000001",
            "serialnumber": "978e2b2668ec26e77c40c760f89c7b31",
            "game": "PlusMW3",
            "members_seo_name": "maxdom"
        }
    ],
    "handle": "checkUSER"
}

But i want to merge output and result like this one:

{
    "status": 200,
    "result": [
        {
            "member_id": "1",
            "member_name": "maxdom",
            "ip_address": [
                "177.68.246.162", 
                "81.254.186.210"
            ],
            "session_onlineplay": "1",
            "sid": [
                "IR63374a32d1424b9288c5f2a5ce161d",
                "IR3cd62da2f143e7b5c8f652d32ed314"
            ],
            "xuid": "0110000100000001",
            "serialnumber": [
                "9923806a06b7f700a6ef607099cb71c6",
                "978e2b2668ec26e77c40c760f89c7b31"
            ],
            "game": "PlusMW3",
            "members_seo_name": "maxdom"
        }
    ],
    "handle": "checkUSER"
}

Answer 1

you better use php for your parser, prevent high load for database, this is sample code

$result = $db->sql_query("SELECT a.*,i.member_id,i.members_seo_name
    FROM ".TBL_IPB_USER." i 
    LEFT JOIN ".TBL_IPB_LA." a 
    ON a.member_id=i.member_id 
    WHERE i.".$column." = '".$val."' AND a.game = '".$game."'");

	$arr = array();
    while( $dbarray = $db->sql_fetchrow($result) ){
		$item = $dbarray;
		$item['ip_address'] = array($item['ip_address']);
		$item['sid'] = array($item['sid']);
		$item['serialnumber'] = array($item['serialnumber']);
		
		$index = $dbarray['member_id'];
		
		if(isset($arr[$index]))
		{
			$arr[$index]['ip_address'] = array_merge($arr[$index]['ip_address'], $item['ip_address'];
			$arr[$index]['sid'] = array_merge($arr[$index]['sid'], $item['sid'];
			$arr[$index]['serialnumber'] = array_merge($arr[$index]['serialnumber'], $item['serialnumber']);
		} else {
			$arr[$index] = $item;
		}
    }

    return array_values($arr);