title: PHP, sql query to CSV, format rows for totals
body: <p>I have a working script that, when executed, runs the following SQL query which is then written to a CSV. All of this works perfectly.</p>
  
  <p>However, I'd like to adjust the formatting somewhat. It currently writes results by employee and orders by last night. I'd like to have a row at the end of all agent's records that said something like "Agent's Name: Totals" and then sums up the totals of their records. </p>
  
  <p>For example, each row has their name, phone extension, and then several metrics which are either blank or have an 'x', and lastly a number representing time on the phone. So I'd like to total the x's in the appropriate fields, add the duration of time on the phone, and lastly a count of total calls (which would be a count of that employee's records).</p>
  
  <p>I don't know if this should be done in the SQL, in the CSV code block or if there's a better way to store the metrics with PHP and do this programmatically.</p>
  
  <p>I'm a rookie here, normally just relying on queries in MySQL workbench, so any help is much appreciated.</p>
  
  <p>Here's the script: </p>
  
  <pre><code>$result = mysqli_query($conn2,
  "SELECT
        firstn
      , lastn
      , extension
      , Recieved
      , RecievedKnown
      , Outbound
      , outboundKnown
      , Missed
      , MissedKnown
      , CallingNumber
      , CalledNumber
      , starttime
      , endtime
        , duration
      , HOLDTIMESECS
      , TERMINATIONREASONCODE
  
  FROM (
        SELECT
                u.firstn
              , u.lastn
              , c.extension
              , CASE WHEN LEGTYPE1 = 2 AND ANSWERED = 1 THEN 'x' ELSE '' END AS Recieved
              , CASE WHEN LEGTYPE1 = 2 AND answered = 1 AND CALLINGPARTYNO = k.phone_number THEN 'x' ELSE '' END AS RecievedKnown
              , CASE WHEN ANSWERED = 1 AND LEGTYPE1 = 1 THEN 'x' ELSE '' END  AS Outbound
              , CASE WHEN LEGTYPE1 = 1 AND FINALLYCALLEDPARTYNO = k.phone_number THEN 'x' ELSE '' END AS outboundKnown
              , CASE WHEN Answered = 0 THEN 'x' ELSE '' END AS Missed
              , CASE WHEN ANSWERED = 0 AND CALLINGPARTYNO = k.phone_number THEN 'x' ELSE '' END AS MissedKnown
              , a.CALLINGPARTYNO AS CallingNumber
              , a.FINALLYCALLEDPARTYNO AS CalledNumber
              , b.starttime AS starttime
              , b.endtime AS endtime
              , b.duration
              , a.holdtimesecs
              , a.terminationreasoncode
        FROM ambition.session a
        INNER JOIN ambition.callsummary b ON a.NOTABLECALLID = b.NOTABLECALLID
        INNER JOIN ambition.mxuser c ON a.RESPONSIBLEUSEREXTENSIONID = c.EXTENSIONID
        INNER JOIN jackson_id.users u ON c.extension = u.extension
        LEFT JOIN ambition.known_numbers k ON a.callingpartyno = k.phone_number
        WHERE date(b.ts) &gt;= curdate()
        AND LEGTYPE1 &lt;&gt; 12 -- This keeps the report from having blank spaces due to the 12 legtype.
        AND c.extension IN (7276,7314,7295,7306,7357,7200,7218,7247,7331,7255,7330,7000,7215,7240,7358,7312)
  
        ) x
      ORDER BY lastn") or die(mysqli_error( $conn2));
  
  
  
      $userDetails = array();
      while($row = $result-&gt;fetch_array(MYSQLI_NUM)){
          /* Let me take the extension as the key to store its respective counts */
          $extension = $row['extension'];
          /* Now in your while loop do all the calculations for the user */
          if(!isset($userDetails[$extension])){
              /* Since this is the first time for the extension your doin the calculations */
              $userDetails[$extension]['missedCallCounts'] = 1; /* First time count */
          }else{
              $userDetails[$extension]['missedCallCounts'] += 1; /* Sum up the count */
          }
      }
  
      foreach($userDetails as $userDetail){
  /* In the following line dump the respective userdetails to csv which will show summary */
  fputcsv($fp, array_values($userDetails));
  }
  </code></pre>
  
  <p>And a screenshot of the current CSV results in excel (omitted names/numbers, but you still see the idea):
  <a href="https://i.stack.imgur.com/kmqrl.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/kmqrl.png" alt="enter image description here"></a></p>
  
  <p>So for extension 7312, under the first two rows would be a new row with something like:</p>
  
  <pre><code>7312's Totals | 0 | 0 | 0 | 0 | 2 | 2 | - | - | - | - | 76 | 0
  </code></pre>