Placing a json array inside the first array?

Question

How would I make the $query_ppimage results a sub array from the first users query? At the moment, the output is outputting a user, then a profile image, under the user, rather than inside the users array. Therefore they aren't linked.

How would I do such a thing? Here is my code:

$query_user = "SELECT *,'user' AS type FROM users WHERE username LIKE '".$query."' OR firstname LIKE '".$query."' OR lastname LIKE '".$query."'";
$quser = $conn->query($query_user);
$rows = array();
while($user = mysqli_fetch_assoc($quser)) {
    $query_ppimage = "SELECT id, post_id, relation, userID, file_format FROM media WHERE userID = '".$user['id']."' AND relation = 'profile_picture' UNION ALL SELECT -1 id, '55529055162cf' post_id, 'profile_picture' relation, '0' userID, 'jpg' file_format ORDER BY id DESC";
    $qppimg = $conn->query($query_ppimage);
    while($ppimg = mysqli_fetch_assoc($qppimg)) {
        $rows[] = $ppimg;   
        $rows[] = $user;
    }
}

Here is how the array is returned:

[
    {
    "id": "117",
    "post_id": "1",
    "relation": "profile_picture",
    "userID": "3",
    "file_format": "jpg"
    },
    {
    "id": "3",
    "email": "casper@socialnetwk.com",
    "type": "user"
    },
]

How it should look, or something similar. I don't think I named the sub array correctly, but it needs a name ppimage

[
    {
    "id": "3",
    "email": "casper@socialnetwk.com",
    "type": "user"
        ppimage: {
        "id": "117",
        "post_id": "1",
        "relation": "profile_picture",
        "userID": "3",
        "file_format": "jpg"
        }
    },
]

1. You should stick with one kind of programming - object oriented or procedural (this is only "cosmetic" in the code). 2. You can limit this into one single query - a query in a loop is *rarely*, if ever, a good idea. — Qirel, Aug 11 '17 at 16:54
I'm having this for example: {firstname: casper}{file_format: img} when it should be {firstname: casper , ppimage{file_format:img}} So for example I need the profile picture array to be named ppimage inside the first array of user — SARUAV, Aug 11 '17 at 16:55
You're already using an API that supports **prepared statements** with bounded variable input, you should utilize parameterized queries with placeholders (prepared statements) to protect your database against [SQL-injection](http://stackoverflow.com/q/60174/)! Get started with [`mysqli::prepare()`](http://php.net/mysqli.prepare) and [`mysqli_stmt::bind_param()`](http://php.net/mysqli-stmt.bind-param). — Qirel, Aug 11 '17 at 16:56
@Qirel That would be the preferred way I was attempting, however it only showed a user result, where they had a profile picture. When I made the join, I'm terrible with joins at the moment — SARUAV, Aug 11 '17 at 16:56
@CassieJRound I didn't quite understand what you ment by that structure. Can you please edit your question with some examples? — Qirel, Aug 11 '17 at 16:59
@CassieJRound Please take into consideration what I said earlier about query inside a loop, and parameterized queries. We're talking about performance and security - neither should be ignored ;-) — Qirel, Aug 11 '17 at 17:17

score 0 · Accepted Answer · answered Aug 11 '17 at 17:08

0

You are adding to $rows twice and appending two separate elements. Try building your array element first and then adding it into the $rows array. Something like this:

$newrow = $user;
$newrow['ppimage'] = $ppimg;
$rows[] = $newrow;

answered Aug 11 '17 at 17:08

Octopus

8,075
5
46
66

You sir, are a legend. Thank you so much. Worked perfectly :) – SARUAV Aug 11 '17 at 17:09

Qirel · Answer 2 · 2017-08-11T17:14:56.003

Using JOIN, you could limit this to one single query. Having a query inside a loop is never a good idea, and you should avoid it if you can. Furthermore, you should use prepared statements in order to protect against SQL injection.

The code below uses a JOIN, so that you just get one query - and structures the array as shown in the example. This is hard-coded, as its easier to control what goes where, since we now use a JOIN, the data is all fetched at once (and not in separate variables).

$row = array();
$stmt = $conn->prepare("SELECT m.id, m.post_id, m.relation, m.file_format, u.id, u.email, 'user' as type
                        FROM media m 
                        JOIN users u ON u.id=m.userID 
                        WHERE m.relation = 'profile_picture' 
                          AND (username LIKE ? 
                           OR firstname LIKE ?
                           OR lastname LIKE ?)
                        ORDER BY m.id DESC");
$stmt->bind_param("sss", $query, $query, $query);
$stmt->execute();
$stmt->bind_result($mediaID, $postID, $relation, $file_format, $userID, $user_email, $type);
while ($stmt->fetch()) { // If you just expect one row, use LIMIT in the query, and remove the loop here
    $row[] = array('id' => $userID, 
                   'email' => $user_email, 
                   'type' => $type, 
                   'ppimage' => array('id' => $mediaID, 
                                      'post_id' => $postID,
                                      'relation' => $relation,
                                      'userID' => $userID,
                                      'file_format' => $file_format)
              );
}
$stmt->close();

Placing a json array inside the first array?

2 Answers2