PHP, create json from inner join table

[nitiphone2021]

Dear All,

As I would like to create json as below format 

{
    "data": [
        {
            "title": "After getting a COVID-19 Vaccine",
            "content": [
                {
                    "list": "You may have some side effects, which are normal signs that your body is building protection."
                },
                {
                    "list": "Following symptoms can take place, and it usually resolves within 2 to 3 days."
                },
                {
                    "list": "Common Side Effects pain, redness, swelling, warmth, nausea, muscle pain, tiredness, headache"
                }
            ]
        },
        {
            "title": "After getting a COVID-19 Vaccine",
            "content": [
                {
                    "list": "You may have some side effects, which are normal signs that your body is building protection."
                },
                {
                    "list": "Following symptoms can take place, and it usually resolves within 2 to 3 days."
                },
                {
                    "list": "Common Side Effects pain, redness, swelling, warmth, nausea, muscle pain, tiredness, headache"
                }
            ]
        }
    ]
}

The information is from  mysql select command:

SELECT (tb_helpinfo.title_eng) as title,(tb_helpdetail.content_eng) as content FROM tb_helpinfo INNER JOIN tb_helpdetail ON tb_helpinfo.id = tb_helpdetail.helpinfo_id

 

How could I make json like that from mysql information?

[gw1500se]

Use json_encode.

[nitiphone2021]

12 minutes ago, gw1500se said:

Use json_encode.

Yes, but how we create the query to be the json format like my target?

[Barand]

Depends the structure you want for your array. Using your existing query...

$res = $pdo->query("SELECT i.title_eng as title
                         , d.content_eng as content 
                    FROM tb_helpinfo i
                         INNER JOIN tb_helpdetail d ON i.id = d.helpinfo_id
                    ORDER BY title
                    ");
## METHOD 1
    $data = [];
    foreach ($res as $r)  {
        if (!isset($data[$r['title']])) {
            $data[$r['title']] = [];
        }
        $data[$r['title']][] = $r['content'];
    }
    echo '<pre> Method 1 ', print_r($data, 1), '</pre>';
    echo json_encode($data);
    
## METHOD 2
    $data = $res->fetchAll();
    echo '<pre> Method 2 ', print_r($data, 1), '</pre>';
    echo json_encode($data);