Search the Community

Another member here, @psycho, was kind enough to help get this script on track for me but I'm trying to modify it for other values. Right now, it groups by user and adds a totals row for them under their records but I'm wanting to add 3 rows for Mean, Mode and Median. I'm not exactly sure how to follow the same format and add 3 rows for each user but I feel like the structure would be the same. My idea is that I would set variables to the formulas and then add them but how would the rows cascade? // Check connectionif ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } echo "Connected successfully"; //Create and run query $sql = " SELECT CONCAT(u.first_name, ' ', u.last_name) as Name, t.ext_id as ID, t.total_talk_time_minutes as TalkTime, t.total_outbound as Outbound, t.total_inbound as Inbound, t.dealers_contacted as Dealers, t.date_of_report as Date, DAYNAME(t.date_of_report) as Day FROM ambition.ambition_totals t INNER JOIN ambition.ambition_users u ON t.extension = u.extension WHERE date_of_report between curdate() - interval 5 day and curdate() ORDER BY ID"; $result = mysqli_query($conn,$sql); //Start the spreadsheet $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); $sheet ->getColumnDimension('A') -> setAutoSize(true); $sheet ->getColumnDimension('B') -> setAutoSize(true); $sheet ->getColumnDimension('C') -> setAutoSize(true); $sheet ->getColumnDimension('D') -> setAutoSize(true); $sheet ->getColumnDimension('E') -> setAutoSize(true); $sheet ->getColumnDimension('F') -> setAutoSize(true); $sheet ->getColumnDimension('G') -> setAutoSize(true); //Create header row $sheet->setCellValue('A1', 'Name'); $sheet->setCellValue('B1', 'User ID'); $sheet->setCellValue('C1', 'Talk Time Per Call'); $sheet->setCellValue('D1', 'Outbound Calls'); $sheet->setCellValue('E1', 'Inbound Calls'); $sheet->setCellValue('F1', 'Dealer Contacts'); $sheet->setCellValue('G1', 'Date'); $sheet->setCellValue('H1', 'Day'); //Preprocess the data into a multi-dimensional array // with the id as the parent index $resultData = array(); while($row = mysqli_fetch_assoc($result)) { $resultData[$row['ID']][] = $row; } //Set starting row number $rowNo = 1; //Iterate over the results for each unique id foreach($resultData as $idRecords) { //Set subtotal variables to 0 $subtotalTalktime = 0; $subtotalOutbound = 0; $subtotalInbound = 0; $subtotalDealers = 0; //Iterate over the records for this ID foreach($idRecords as $record) { //Increment row number $rowNo++; //Add record row to spreadsheet $sheet->setCellValue("A{$rowNo}", $record['Name']); $sheet->setCellValue("B{$rowNo}", $record['ID']); $sheet->setCellValue("C{$rowNo}", $record['TalkTime']); $sheet->setCellValue("D{$rowNo}", $record['Outbound']); $sheet->setCellValue("E{$rowNo}", $record['Inbound']); $sheet->setCellValue("F{$rowNo}", $record['Dealers']); $sheet->setCellValue("G{$rowNo}", $record['Date']); $sheet->setCellValue("H{$rowNo}", $record['Day']); //Update the subtotals $subtotalTalktime += $record['TalkTime']; $subtotalOutbound += $record['Outbound']; $subtotalInbound += $record['Inbound']; $subtotalDealers += $record['Dealers']; } //Increment row number $rowNo++; //Add subtotal row to spreadsheet $sheet->setCellValue("C{$rowNo}", $subtotalTalktime); $sheet->setCellValue("D{$rowNo}", $subtotalOutbound); $sheet->setCellValue("E{$rowNo}", $subtotalInbound); $sheet->setCellValue("F{$rowNo}", $subtotalDealers); } var_dump($resultData); $worksheet1 = $spreadsheet->createSheet(); $worksheet1->setTitle('Department Total'); //Add in the query for department totals //Add results to spreadsheet mysqli_close($conn); $writer = new xlsx($spreadsheet); $writer->save('Coaching Report - Test.xlsx'); ?>

I'm trying to create a spreadsheet within a php script that selects data from a mysql query. The query works and my below script works in the sense that it inserts my entire result set into my spreadsheet like so: https://i.stack.imgur.com/Euynu.png The only thing I can't figure out is how to modify the array loop so that I can sum totals of the 4 columns (C,D,E and F) by user. Essentially, it needs to realize that it's at the last record for user ID 48 so I can insert a row of totals, and move to the next user. I feel it should be fairly straightforward but I just can't seem to make sense of it right now. The only other factor is that when this query runs, every user could have up to 5 records so I can use the USER ID to associate a user but the records would hinge on the ID as well as the Date, if that makes sense. How can I get this to group by user and total their numbers before writing the info for the next user? $sql = " select concat(u.first_name, ' ', u.last_name) as Name, t.ext_id as ID, t.total_talk_time_minutes as TalkTime, t.total_outbound as Outbound, t.total_inbound as Inbound, t.dealers_contacted as Dealers, t.date_of_report as Date From ambition.ambition_totals t INNER JOIN ambition.ambition_users u ON t.extension = u.extension where date_of_report between curdate() - interval 5 day and curdate()" or die(mysqli_error( $conn)); $result=mysqli_query($conn,$sql); $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); $sheet->setCellValue('A1', 'Name'); $sheet->setCellValue('B1', 'User ID'); $sheet->setCellValue('C1', 'Talk Time Per Call'); $sheet->setCellValue('D1', 'Outbound Calls'); $sheet->setCellValue('E1', 'Inbound Calls'); $sheet->setCellValue('F1', 'Dealer Contacts'); $sheet->setCellValue('G1', 'Date'); $rowNumber = 2; while($row = mysqli_fetch_assoc($result)) { $col = 'A'; foreach($row as $cell){ $sheet -> setCellValue($col.$rowNumber, $cell); $col++; } $rowNumber++; }