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'd start with something like this //your query $sql = "SELECT complaint.id, complaint.complaint, complaint.c_time, complaint_images.name FROM complaint left join complaint_images on complaint.id = complaint_images.c_id ORDER BY c_time DESC LIMIT 20"; //your array $output = []; $result = $db->sql_query($sql); // build the array for display while($thisdata = $result->fetch_assoc()){ if (!array_key_exists($thisdata['c_time'], $output['DAYS']) || !array_key_exists($thisdata['id'], $output['DAYS'][$thisdata['c_time']){ //don't try to make the key fancy $output['DAYS'][$thisdata['c_time']][$thisdata['id']] = [ 'ID' => $thisdata['id'], 'TIME' => date("g:i a", $thisdata['c_time']), 'DATE' => date("D M j, Y", $thisdata['c_time']), 'COMPLAINT' => nl2br($thisdata['complaint']), 'COMPLAINT_EDIT' => $thisdata['complaint'], 'IMAGES' => [] ]; } $output['DAYS][$thisdata['c_time']][$thisdata['id']]['IMAGES'][] = $thisdata['name']; } Code (php):
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):
cut it down to a single database call. If you have 50 complaints returned by your first query that is another 50 database hits just to generate your array.
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.