使用PHP创建具有特定格式的JSON

Question

I have a table in my server database with the following columns:

|---category--|---name---|---description---|

So, lets say for example that we have the following data inside the table:

|---CategoryA--|---name1---|---description1---|
|---categoryB--|---name2---|---description2---|
|---categoryA--|---name3---|---description3---|
|---categoryA--|---name4---|---description4---|

I would like to create a .php file, and when i call it from my Android app i would like to get a JSON as response. The json file would like to have the following format:

{
"CategoryA":[
{"name":"name1","description":"description1"},
{"name":"name3","description":"description3"},
{"name":"name4","description":"description4"}
],
"KatigotiaB":[
{"name":"name2","description":"description2"}
]
}

I have created a .php file that returns me the data in JSON format, but not in the specific format i want. Here is my .php file:

<?php
    header('content-type: text/html; charset=utf-8');
    try {
        $pdo = new PDO('mysql:host=****;dbname=****', '****', '****', array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch(PDOException $e) {
        echo 'ERROR: ' . $e->getMessage();
    }

    $stmt = $pdo->prepare("SELECT * FROM `db`.`table`;");
    $stmt->execute();

    $results = array();

    while ($obj = $stmt->fetch(PDO::FETCH_ASSOC)) {
        array_push($results, $obj);
    }

    function replace_unicode_escape_sequence($match) {
        return mb_convert_encoding(pack('H*', $match[1]), 'UTF-8', 'UTF-16BE');
    }

    $str = preg_replace_callback('/\\\\u([0-9a-f]{4})/i', 'replace_unicode_escape_sequence', json_encode($results));


    echo $str;
?>

and the result is:

[{"category":"CategoryA","name":"name1","description":"description1"},
{"category":"CategoryB","name":"name2","description":"description2"},
{"category":"CategoryA","name":"name3","description":"description3"},
{"category":"CategoryA","name":"name4","description":"description4"}]

As i'm an Android developer and my php knowledge is limited, how could i recreate my .php file in order to get the correct JSON format?

UPDATE: that works

 foreach ($nameDescriptionPairs as $nameDescriptionPair) {
            $result[$row['category']][] = array(
                'name'          => $nameDescriptionPair['name'],
                'description'   => $nameDescriptionPair['description']
            );

        }

Answer 1

Solution #1 (simple):

<?php
$host = 'localhost';
$database = '******';
$user = '******';
$password = '******';

$result = [];
try {
    $pdo = new PDO(
        "mysql:host=$host; dbname=$database;",
        $user,
        $password,
        [PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"]
    );

    $stmt = $pdo->prepare("SELECT category, name, description FROM `YOUR_TABLE_HERE`");
    $stmt->execute();

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $result[$row['category']][] = [
            'name'          => $row['name'],
            'description'   => $row['description'],
        ];
    }
} catch (Exception $e) {
    echo 'ERROR: ' . $e->getMessage();
}

header('content-type: application/json; charset=utf-8');
echo json_encode($result);

Solution #2 (geeky):

One note to my code: I used hex conversion to prevent problems with quotes. Thus, even if any column has any number of ", the code will work fine.

<?php

function hexToStr($hex) {
    $string = '';
    for ($charIter = 0; $charIter < strlen($hex) - 1; $charIter += 2) {
        $string .= chr(hexdec($hex[$charIter] . $hex[$charIter + 1]));
    }
    return $string;
}

//----------------------

$host = 'localhost';
$database = '******';
$user = '******';
$password = '******';

$result = [];    
try {
    $pdo = new PDO(
        "mysql:host=$host; dbname=$database;",
        $user,
        $password,
        [PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"]
    );

        $query = <<<QUERY
    SELECT category, CONCAT('[', GROUP_CONCAT( CONCAT( '{"name":"', hex( name ) , '", "description":"', hex( description ) , '"}' ) ), ']') raw_json
    FROM `YOUR_TABLE_HERE`
    GROUP BY category
QUERY;

    $stmt = $pdo->prepare($query);
    $stmt->execute();

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $nameDescriptionPairs = json_decode($row['raw_json'], true);
        foreach ($nameDescriptionPairs as $nameDescriptionPair) {
            $result[$row['category']][] = [
                'name'          => hexToStr($nameDescriptionPair['name']),
                'description'   => hexToStr($nameDescriptionPair['description'])
            ];
        }
    }
} catch (Exception $e) {
    echo 'ERROR: ' . $e->getMessage();
}

header('content-type: application/json; charset=utf-8');
echo json_encode($result);