CODEWITHSUNDEEP
phpmysqlarrays
adiquet
adiquet

Reputation: 53

Get the difference between 2 arrays

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

Upvotes: 3

Views: 99

Answers (4)

Cornel Raiu
Cornel Raiu

Reputation: 3005

What I would do is create another array that holds the values from the first query on a key->value basis and on the next one just substract the value from the respective entry in the array.

I think that this is the easiest and quickest way of doing it by using 2 queries.

Note: I did not test this code.

Note2: I assumed that a missing sport in the first category will count as a 0

Note3: the code I wrote is only to display a way of calculating the difference. Let me know if I should update it to givee the exact same output you require there.

$results = array();

$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()){

        //output this query results here
        $results[$row->cat] = $row->qty;
      }
   }

$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()){

      //output this query results here
      $results[$row_p->cat] = ((isset($results[$row_p->cat])) ? $results[$row_p->cat] : 0 ) - $row_p->qty;
    }
}


foreach( $results as $key => $result) {
  echo "<tr>";  
  echo "<td><h6>".$key."</h6></td><td><h3 class='text-primary'>".$result."</h3></td>";
  echo "</tr>";
}

UPDATE - displaying first week and difference next to it

You can try this. I got rid of the foreach and did it all using only the query loops.

NOTE: again, this is untested code

$results = array();

$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");

$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");
if($count_previous = $result_previous->num_rows) {
    while($row_p = $result_previous->fetch_object()){

      //output this query results here
      $results[$row_p->cat] = $row_p->qty;
    }
}

if($count_account = $result_account->num_rows) {
    while($row = $result_account->fetch_object()){

        $difference = $row->qty - ((isset($results[$row->cat])) ? $results[$row->cat] : 0 );

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

Upvotes: 0

Marco somefox
Marco somefox

Reputation: 368

Store the first set of numbers in two associative arrays, then in the second loop calculate the differences

$initial = [];
$diff = [];
$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>";
      $initial[$row->cat] = $row->qty; //remember 1st results
      $diff[$row->cat] = $row->qty; //to be used
      }
   }

$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>";
      $diff[$row_p->cat] -= $row_p->qty; 

        }
}
//now print the initial qty and the difference
$cats = array_keys($diff);
for($i=0; $i<sizeof($cats); $i++){
    echo "<tr>";
    echo "<td><h6>".$cats[$i]."</h6></td>";
    $first = $initial[$cats[$i]];
    echo "<td><h3 class='text-primary'>$first</h3></td>"
    $d = $diff[$cats[$i]];
    $sign = $d < 0 ? "-" : "+";
    echo "<td><h3 class='text-primary'>$sign $d</h3></td>";
    echo "<tr>";
}

Upvotes: 0

GMB
GMB

Reputation: 222582

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).

Upvotes: 2

Tagarikdi Djakouba
Tagarikdi Djakouba

Reputation: 428

You can do this

$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()){
        $$key[$row->cat]= $row->qty;
    }
}

$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()){
        $second_array[$row_p->cat]= $row_p->qty;
    }
}

foreach ($first_array as $key => $value) {
    $difference_array[$key]=$value - $second_array[$key];
}

foreach ($difference_array as $key => $value){
    echo "<tr>";
    echo "<td><h6>".$key."</h6></td><td><h3 class='text-primary'>".$value."</h3></td>";
    echo "</tr>";
}

Upvotes: 0

Related Questions