Get the difference between 2 arrays

Question

I have 2 search queries - one will display the contents for the last 7 days. The other will display the contents from 2 weeks prior. Both work just fine. However I want to take the results from the first query and get the difference from the second query. Then display the first query with the difference.

$result_account = $db->query("
SELECT nid
     , COUNT(cat) AS qty
     , dte
     , descript
     , cat
     , name
     , user 
  FROM client_note AS cn 
  JOIN client_note_tag_items AS cnti 
    ON cnti.note_id = cn.nid 
  JOIN client_note_tags AS cnt 
    ON cnt.tag_id = cnti.tag_id 
 WHERE dte >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) 
   AND name NOT LIKE 'Resolution%' 
 GROUP 
    BY cat 
 ORDER 
    BY qty DESC 
 LIMIT 5
");
       if($count_account = $result_account->num_rows) {
               while($row = $result_account->fetch_object()){

          echo "<tr>";
          echo "<td><h6>".$row->cat."</h6></td><td><h3 class='text-primary'>".$row->qty."</h3></td>";
          echo "</tr>";
          }
       }

$result_previous = $db->query("SELECT nid, COUNT(cat) AS qty, dte, descript, cat, name, user FROM client_note AS cn JOIN client_note_tag_items AS cnti ON cnti.note_id = cn.nid JOIN client_note_tags AS cnt ON cnt.tag_id = cnti.tag_id WHERE (dte BETWEEN DATE_SUB(CURDATE(), INTERVAL 21 DAY) AND DATE_SUB(CURDATE(), INTERVAL 14 DAY)) AND name NOT LIKE 'Resolution%' GROUP BY cat ORDER BY qty DESC LIMIT 5");
    if($count_previous = $result_previous->num_rows) {
            while($row_p = $result_previous->fetch_object()){

          echo "<tr>";  
          echo "<td><h6>".$row_p->cat."</h6></td><td><h3 class='text-primary'>".$row_p->qty."</h3></td>";
          echo "</tr>";


            }
    }

First query will result in :

Category   - Qty
Baseball   - 45
Football   - 33
Soccer     - 21
Hockey     - 7
Basketball - 3

The second query will result in :

Category   - Qty
Basketball - 38
Soccer     - 28
Hockey     - 16
Football   - 12
Baseball   - 12

Now I want to display it like this

Category   - Qty Difference
Baseball   - 45  +33
Football   - 33  +21
Soccer     - 21  -7
Hockey     - 7   -9
Basketball - 3   -35

Answer 1

Comparing the same data over a different period of time could be also achieved in a single SQL query, using conditional aggregation :

SELECT 
    cat, 
    SUM(IF(dte >= d.start1, 1, 0)) AS qty, 
    SUM(IF(dte >= d.start1, 1, 0)) - SUM(IF(dte < d.end2, 1, 0)) AS Difference, 
FROM 
    (SELECT DATE_SUB(CURDATE(), INTERVAL 7 DAY) start1, DATE_SUB(CURDATE(), INTERVAL 14 DAY) end2) as d
    CROSS JOIN client_note AS cn 
    JOIN client_note_tag_items AS cnti ON cnti.note_id = cn.nid 
    JOIN client_note_tags AS cnt ON cnt.tag_id = cnti.tag_id 
WHERE
    dte >= DATE_SUB(CURDATE(), INTERVAL 21 DAY) 
    AND name NOT LIKE 'Resolution%' 
GROUP BY cat 
ORDER BY qty DESC 
LIMIT 5

Notes :

• The first subquery is just a shortcut to avoid typing the same DATE_SUB... expressions over and over
• I removed columns that are not used in the output
• You would need to properly alias the columns in the query ; as it is, it is hard to tell which column belongs to which table
• It is recommended (and mandatory in non-ancient MySQL versions) to put all non-aggregated columns in the GROUP BY clause
• No sample data provided => not possible to test the query

PS : As commented by cornel.raiu, this approach only makes sense if you do not need to output the results separatly before combining them (else, you would end up running 3 SQL queries, which might not be optimal).