How to include mysql join results into a PHP multidimensional array

Question

I have two tables named "Accounts" and "Images".

Images will contain multiple results for each account.

How do I reflect all the images associated with an account using mysql and php in an single array with all images separated by comma?

I am not certain which join to use either...

Here is what I expect my end result to be:

Array
(
    [0] => Array
        (
            [id] => 1
            [firstName] => "John"
            [lastName] => "Doe"
            [images] => "image1.jpg,image2.jpg,image3.jpg"
        )

)

Here is what I currently get:

Array
(
    [0] => Array
        (
            [id] => 1
            [firstName] => "John"
            [lastName] => "Doe"
            [filename] => "image1.jpg"
        )

)

Here is my code thus far:

$sql = SELECT a.firstName,a.lastName,i.fileName FROM accounts a INNER JOIN images i ON a.id = i.accountID;

$result = mysqli_query($con,$sql);

while($row = mysqli_fetch_assoc($result)){
        $accounts[] = $row;
}

print_r($accounts);

If there is a better way for me to start than using the code above I am open to all suggestions.

This is not a good way to store that file names in string. If one of those files have `,` in it, it makes problem. — ICE, May 25 '17 at 11:39

score 4 · Accepted Answer · answered May 24 '17 at 23:28

You can accomplish the output you want using a single SQL query and the GROUP_CONCAT function.

The following will find all accounts (with and without images), and a comma separated list of image filenames.

If you only want to find accounts with images, change LEFT JOIN for INNER JOIN.

SELECT a.id, a.firstName, a.lastName, GROUP_CONCAT(i.fileName SEPARATOR ',') AS images
FROM accounts a
LEFT JOIN images i ON (a.id = i.accountID)
GROUP BY a.id

score 1 · Answer 2 · answered May 24 '17 at 23:27

Basically you're looking for a way to concatenane multiple rows from the joined table into a string. There are some solutions, but they are specific to database - you may check this SO answer for inspiration - How to create a SQL Server function to "join" multiple rows from a subquery into a single delimited field?

But probably easier way is to combine the results in PHP code. This has some performance hit due to returning duplicated data for each account, but it's not really major if you're selecting just a few columns.

The code may look like this:

$sql = 'SELECT a.id,a.firstName,a.lastName,i.fileName FROM accounts a INNER JOIN images i ON a.id = i.accountID';

$result = mysqli_query($con,$sql);

while($row = mysqli_fetch_assoc($result)){
    if (!isset($accounts[$row['id']])) {
        $accounts[$row['id']] = $row;
    } else {
        $accounts[$row['id']]['fileName'] .= ',' . $row['fileName'];
    }
}

print_r($accounts);

Note, this code creates array $accounts with keys matching accounts ids. If you really need simple sequential numbers as keys, add $accounts = array_values($accounts); after the loop.

thank you that gave me the following results. it put all images in the csv and gave me only a single account instead of all accounts that are returned from sql query. ` Array ( [0] => Array ( [id] => 1 [firstName] => "John" [lastName] => "Doe" [filename] => "image1.jpg,image2,jpg,image3,image4.jpg,image11.jpg,image20.jpg" ) )` — user1157800, May 25 '17 at 00:46
Did you add that `array_values` call? Try without it. Also note that I've modified the query and added `a.id` to the selected columns. Finally, if you need to get all accounts even including those that don't have any images, replace INNER JOIN with LEFT JOIN. — astax, May 25 '17 at 07:44

How to include mysql join results into a PHP multidimensional array

2 Answers2