Not sure if php loop or mysql join call to build an array

Hi all I'm trying to build an array I've got it as I wanted until I decided to add ability to ad images/files.
I'm trying to build on an existing array for multiple files & i'm having a brain fart. please help.

here's how i'm building the array now:

// Pull all the post to display
$sql = "SELECT *
        FROM complaint
        ORDER BY c_time DESC
        LIMIT 20";


$result = $db->sql_query($sql);

// build the array for display
while($thisdata = $result->fetch_assoc())
{
    $row['DAYS'][date("D M j, Y", $thisdata['c_time'])][] = array(
                                'ID'                => $thisdata['id'],
                                'TIME'                => date("g:i a", $thisdata['c_time']),
                                'COMPLAINT'         => nl2br($thisdata['complaint']),
                                'COMPLAINT_EDIT'     => $thisdata['complaint']);
}

PHP:

I'm trying to figure out how to make it show more than one file per complaint. currently it (the array) looks like something like this:

Array
(
    [0] => Array
        (
            [id] => 245
            [c_time] => 1602451200
            [complaint] => rest 2
        )

    [1] => Array
        (
            [id] => 244
            [c_time] => 1602450600
            [complaint] => new test
        )
}

Code (markup):

I want it to have something like this:

Array
(
    [0] => Array
        (
            [id] => 245
            [c_time] => 1602451200
            [complaint] => rest 2
            [files] => (AN ARRAY OF FILES HERE IF THERE ARE ANY)
        )

    [1] => Array
        (
            [id] => 244
            [c_time] => 1602450600
            [complaint] => new test
        )
}

Code (markup):

I'm not sure how to do this. Would this be a call to the db with a JOIN or two different sql calls and then loop with php & somehow add them to the array that way?
FYI: my tables are setup like this:
complaints : id - c_time - complaint
and
complaint_images : id - c_id - name

TIA for any help.

 

I was trying out yur code but I had to fix a few things a missing ] in the if line after $output['DAYS'][$thisdata['c_time']
then, the misssing ' in $output['DAYS]
After that I get an errorsaying it doesn't need the ; in line 24 "];" but if I remove it I get a new error, but it looks right so I'm not sure what to do with that.

I got my code to work, but I know it's sloppy to have a DB call in a loop. But this is working for now.

// Pull all the post to display
$sql = "SELECT *
        FROM complaint
        ORDER BY c_time DESC
        LIMIT 20";


$result = $db->sql_query($sql);
$files = array();

// build the array for display
while($thisdata = $result->fetch_assoc())
{
    $sql2 = "SELECT *
            FROM complaint_images
            WHERE c_id = '$thisdata[id]'";
    $result2 = $db->sql_query($sql2);

    while($thisdata2 = $result2->fetch_assoc())
    {
        $files[] = $thisdata2['name'];
    }


    $row['DAYS'][date("D M j, Y", $thisdata['c_time'])][] = array(
                                'ID'                => $thisdata['id'],
                                'TIME'                => date("g:i a", $thisdata['c_time']),
                                'COMPLAINT'         => nl2br($thisdata['complaint']),
                                'COMPLAINT_EDIT'     => $thisdata['complaint'],
                                'FILES'                => $files);
    $files = array();
}

PHP:

Don't laugh, I know it's sloppy. any advice on a better way?

P.S. A partial example of the array i'm getting now:

Array
(
    [DAYS] => Array
        (
            [Sun Oct 11, 2020] => Array
                (
                    [0] => Array
                        (
                            [ID] => 245
                            [TIME] => 4:20 pm
                            [COMPLAINT] => rest 2
                            [COMPLAINT_EDIT] => rest 2
                            [FILES] => Array
                                (
                                    [0] => file1.png
                                    [1] => file2.png
                                    [2] => file3.png
                                )

                        )

                    [1] => Array
                        (
                            [ID] => 244
                            [TIME] => 4:10 pm
                            [COMPLAINT] => new test
                            [COMPLAINT_EDIT] => new test
                            [FILES] => Array
                                (
                                    [0] => test-1.png
                                    [1] => test-2.png
                                )

                        )

                )

Code (markup):

 

You can try something like this:

<?php
$sql=" select c.id, c.complaint, c.c_time,
group_concat( i.name ) as name
from complaints as c
left join complaint_images as i
on c.id = i.c_id
order by c.c_time desc limit 20 ";

$result= $mysqli->query($sql);
$s=array();
while($row=$result->fetch_array(MYSQLI_ASSOC)){
$s[]=$row;
}
?>


Your array will be like this:
array(
[0]=>Array(
'id'=> 123,
'complaint' => text of complaint,
'c_time' => time of complaint
'name' => file1.jpg, file2.jpg, file3.jpg
),
[1]=>Array(
'id'=> 456,
'complaint' => text of complaint,
'c_time' => time of complaint
'name' =>
)
);


You will have comma separated list of image names, instead of in an array.
Do a "explode" on it to turn it into an array when processing.

 

Thanks for all the help. I've got it down to 2 sql calls now. I'll "prolly" stick with this:

// get all the file attachments into an array

$file_array = array();
$sql2 = "SELECT c_id, name
            FROM complaint_images";

$result2 = $db->sql_query($sql2);
while($thisdata2 = $result2->fetch_assoc())
{
    $file_array['c_id'][$thisdata2['c_id']][] = $thisdata2['name'];
}

// Pull all the post to display
$sql = "SELECT *
        FROM complaint
        ORDER BY c_time DESC
        LIMIT 20";


$result = $db->sql_query($sql);

// build the array for display
while($thisdata = $result->fetch_assoc())
{

    $files = @$file_array['c_id'][$thisdata['id']];

    $row['DAYS'][date("D M j, Y", $thisdata['c_time'])][] = array(
                                'ID'                => $thisdata['id'],
                                'TIME'                => date("g:i a", $thisdata['c_time']),
                                'COMPLAINT'         => nl2br($thisdata['complaint']),
                                'COMPLAINT_EDIT'     => $thisdata['complaint'],
                                'FILES'                => $files);
}

PHP:

 

Why build an array wasting memory on making copies of everything for no good reason, instead of just sending the data -- or the result handler -- to the flipping template? This looks like you're violating separation of concerns and making everything work ten times harder than need be.