J'ai 2 requêtes de recherche - l'une affichera le contenu des 7 derniers jours. L'autre affichera le contenu de 2 semaines avant. Les deux fonctionnent très bien. Cependant, je veux prendre les résultats de la première requête et obtenir la différence de la deuxième requête. Affichez ensuite la première requête avec la différence.

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


            }
    }

La première requête entraînera:

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

La deuxième requête entraînera:

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

Maintenant je veux l'afficher comme ça

Category   - Qty Difference
Baseball   - 45  +33
Football   - 33  +21
Soccer     - 21  -7
Hockey     - 7   -9
Basketball - 3   -35
3
adiquet 26 janv. 2019 à 01:07

3 réponses

Meilleure réponse

Ce que je ferais, c'est créer un autre tableau qui contient les valeurs de la première requête sur une base clé-> valeur et sur la suivante, il suffit de soustraire la valeur de l'entrée respective dans le tableau.

Je pense que c'est le moyen le plus simple et le plus rapide de le faire en utilisant 2 requêtes.

Remarque: je n'ai pas testé ce code.

Note 2: j'ai supposé qu'un sport manquant dans la première catégorie compterait comme 0

Note 3: le code que j'ai écrit sert uniquement à afficher un moyen de calculer la différence. Faites-moi savoir si je dois le mettre à jour pour donner exactement le même résultat que vous avez besoin.

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

MISE À JOUR - affichage de la première semaine et de la différence à côté

Vous pouvez essayer ceci. Je me suis débarrassé de foreach et j'ai tout fait en utilisant uniquement les boucles de requête.

REMARQUE: encore une fois, il s'agit d'un code non testé

$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>";
    }
}
0
Cornel Raiu 25 janv. 2019 à 23:19

Stockez le premier ensemble de nombres dans deux tableaux associatifs, puis dans la deuxième boucle calculez les différences

$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>";
}
0
Marco somefox 25 janv. 2019 à 23:03

Tu peux le faire

$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>";
}
0
Tagarikdi Djakouba 25 janv. 2019 à 22:31