Get data from one to many relation as array

Question

I have 2 tables with one-to-many relationship. I would like to get the data from the table with many as an array.

The 2 tables look like this:

    Users
_____________
| id | name |
|____|______|
| 1  | John |
|____|______|

    Posts
________________________
| id | title | user_id |
|____|_______|_________|
| 1  | post1 |    1    |
|____|_______|_________|
| 2  | post2 |    1    |
|____|_______|_________|

I can get the posts for each user using:

$sql = "SELECT u.id, u.name, p.title, p.id as post_id 
FROM users u 
JOIN posts p ON u.id = p.user_id 
WHERE u.id = :id";

This would return the following data:

_________________________________
| id |   name  | title | post_id |
|____|_________|_______|_________|
| 1  |   John  | post1 |    1    |
|____|_________|_______|_________|
| 1  |   John  | post2 |    2    |
|____|_________|_______|_________|  

The id and name are duplicated with each row and I don't need this data, So I want to get the posts data as array on 1 row like:

________________________
| id |   name  | posts |
|____|_________|_______|
| 1  |   John  | array |
|____|_________|_______|  

Result would be something like:

Array ( 
    [0] => Array (
        [id] => 1
        [name] => John 
        [posts] =>  Array (
            [0] => Array (
                [title] => post1
                [post_id] => 1
            )
            [1]=> Array (
                [title] => post2
                [post_id] => 2
            )     
        )
     ) 
) 

Is that possible with SQL or I need to handle the data with PHP to look like this result?

Answer 1

Forming such Php array could be tricky. But json objects are quite similar.

create table Users (
 id int primary key auto_increment, 
 name varchar(30)
 );

create table Posts (
 id int primary key auto_increment, 
 title varchar(30), 
 user_id int
 );

insert into Users (name) values ('John');
insert into Posts (title, user_id) values 
('Post 1', 1), ('Post 2', 1);

SELECT u.id, u.name
, concat('[', group_concat(JSON_OBJECT('title', p.title, 'post_id', p.id) order by p.id separator ','), ']') as posts
FROM Users u
JOIN Posts p ON u.id = p.user_id
GROUP BY u.id, u.name

id | name | posts                                                                
-: | :--- | :--------------------------------------------------------------------
 1 | John | [{"title": "Post 1", "post_id": 1},{"title": "Post 2", "post_id": 2}]

db<>fiddle here