CODEWITHSUNDEEP
phpmysqlmultidimensional-array
Kárpáti András
Kárpáti András

Reputation: 1239

MySQL select multi dimensional?

I would like to select all the posts and their attachments from my database.

This is the structure with dummy data:

Posts table

id       | post          | userId   | 
---------|---------------|----------|
1        | "hello"       |  1       |
2        | "world"       |  1       |
3        | "ouch"        |  2       |
4        | "test"        |  1       |

Attachments table

id       | postId        | fileName |  time    | 
---------|---------------|----------|----------|
1        | 1             |"hey.jpg" |        0 |
2        | 1             |"test.png"| 53252354 |
3        | 2             |"asd.png" |        0 |
4        | 4             |"asd2.png"|        0 |

My code looks like this so far, but I doesn't really get what I'd like to.

$qry = $db->prepare('
SELECT p.id
     , p.post
     , p.userId
     , att.fileName
     , att.time
  FROM posts p
  LEFT 
  JOIN attachments att 
    ON att.postId = p.id
');
$qry->execute();
$postsArray = $qry->fetchAll(PDO::FETCH_ASSOC);

I would like to have something like this:

[{'id': 1,
'post': 'hello',
'userId': 1,
'attachments': [{'fileName': 'hey.jpg', 'time:' 0}, ... ]
}, ... ]

How could I achieve this?

Upvotes: 0

Views: 254

Answers (2)

Paul Spiegel
Paul Spiegel

Reputation: 31782

Usually I would do something like this:

$result = $db->query("select id, post, userId from posts");
$posts = [];
while ($post = $result->fetch(PDO::FETCH_OBJECT)) {
    $post->attachments = [];
    $posts[$post->id] = $post;
}

$result = $db->query("select postId, fileName, time from attachments");
while ($att = $result->fetch(PDO::FETCH_OBJECT)) {
    $posts[$att->postId]->attachments[] = $att;
    unset($att->postId); // optional
}

$posts = array_values($posts); // optional
echo json_encode($posts);

Note that the $posts array is id-indexed. I would keep it that way. But if you need exactly the same result as in your question (zero-indexed), you can add this line:

$posts = array_values($posts);

In newer MySQL versions you can also get the JSON result with a single SQL query:

select json_arrayagg(post_json) as json
from (
  select json_object(
    'id', p.id,
    'post', p.post,
    'userId', p.userId,
    'attachments', json_arrayagg(
      json_object('fileName', a.fileName, 'time', time)
    )
  ) as post_json
  from posts p
  left join attachments a on a.postId = p.id
  group by p.id
) x

Result:

[{"id": 1, "post": "hello", "userId": 1, "attachments": [{"time": 0, "fileName": "hey.jpg"}, {"time": 53252354, "fileName": "test.png"}]}, {"id": 2, "post": "world", "userId": 1, "attachments": [{"time": 0, "fileName": "asd.png"}]}, {"id": 3, "post": "ouch", "userId": 2, "attachments": [{"time": null, "fileName": null}]}, {"id": 4, "post": "test", "userId": 1, "attachments": [{"time": 0, "fileName": "asd2.png"}]}]

db-fiddle demo

Upvotes: 3

Nick
Nick

Reputation: 147156

Your query will give you the right data for the results you want, you can post-process in PHP to get the format you want:

foreach ($postArray as $post) {
    $fixed_part = array('id' => $post['id'], 'post' => $post['post'], 'userId' => $post['userId']);
    $key = serialize($fixed_part);
    if (!isset($out[$key])) $out[$key] = $fixed_part;
    $out[$key]['attachments'][] = array('fileName' => $post['fileName'], 'time' => $post['time']);
}
$out = array_values($out);
echo json_encode($out, JSON_PRETTY_PRINT);

Output is too long to post but can be seen in this demo. Query results can be seen on dbfiddle.

Upvotes: 3

Related Questions