通过php从MySql创建二维JSON数组

Question

I have three different tables:

holidays:
id | date | title | description

cat_event:
id | eventId (fk: holidays.id) | catId (fk: categories.id)

categories:
id | catName

One event can contain one or more categories.

I'd like to create the following JSON:

{
"Events":
    [
        {
            "id": "12",
            "date": "2014.03.21",
            "title": "National Tiger Day",
            "description": "Some description text",
            "categories":
                [
                    { "id": "1", "catName": "Animal" },
                    { "id": "2", "catName": "Global" }
                ]
        },
        {
            "id": "13",
            "date": "2014.03.22",
            "title": "World Chocolate Day",
            "description": "Some description text",
            "categories":
                [
                    { "id": "3", "catName": "Food" },
                    { "id": "2", "catName": "Global" }
                ]
        }
    ]
}

I made a query:

SELECT holidays.*, categories.* FROM holidays JOIN event_cat ON event_cat.eventId = holidays.id JOIN categories ON categories.id = event_cat.catId WHERE holidays.id = 1

But I really don't have a clue how I can create the mentioned json array.

Answer 1

I have not enough time to check but one of solutions is:

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
// checking connection
if (mysqli_connect_errno()) {
    exit();
}
if ($result = $mysqli->query("
SELECT holidays.*, categories.id as cat_id,categories.catName as catName FROM holidays
    JOIN event_cat ON event_cat.eventId = holidays.id
    JOIN categories ON categories.id = event_cat.catId
    WHERE holidays.id = 1")) {
    while ($row = $result->fetch_row()) {
        if(isset($respond['Events'][$row['id']]))
        {
            $response['Events'][$row['id']]['categories'][] = array('id'=>$row['cat_id'],'catName'=>$row['catName']);
        }else
        {
            $response['Events'][$row['id']] = array(
                'id'=>$row['id'],
                'date'=>$row['date'],
                'title'=>$row['title'],
                'description'=>$row['description'],
                'categories'=>array(array(
                    'id'=>$row['cat_id'],
                    'catName'=>$row['catName']))

            );
        }
    }
    $result->close();
}
$mysqli->close();
echo json_encode($response);