CODEWITHSUNDEEP
phpmysqlsqlcountunion
David Morin
David Morin

Reputation: 47

Adding totals from sql statements that use union

I have a query that uses union to show totals from each request category. I'm trying to find a way to add the totals of all 6 categories and show them on the user page. I am using ajax to reload the totals as they come in or are completed. What I am getting for a response shows each category together in succession instead of a total.

$sql = "SELECT 'SKU Requests: ' AS skus, COUNT(*), '/admin/sku2', '/skuRequest/myRequests.php' AS sLink FROM sku_request WHERE completed = 0
        UNION
        SELECT 'Sku Update:' AS updates, COUNT(*), '/admin/sku/skuUpdate.php', '#' AS sLink FROM sku_update WHERE completed = 0
        UNION
        SELECT 'RMA Requests: ' AS rma, COUNT(*),'/admin/rma', '/rma/yourRequests.php' AS sLink FROM rma_submissions WHERE rma_number = 'Pending'
        UNION
        SELECT 'Termination Requests: ' AS idT, COUNT(*),'/admin/idTerminations','/idTermination/yourIdTermRequests.php' AS sLink FROM idTermination WHERE completed = 0
        UNION
        SELECT 'ID Requests: ' AS ids, COUNT(*), '/admin/idRequest2', '/idRequest/yourIdRequests.php' AS sLink FROM id_request WHERE ssoid IS NULL
        UNION
        SELECT 'TDA: ' AS tda, COUNT(*), '/admin/tda', '/tda' AS sLink FROM tda_request WHERE completed = 0


";


  $stmt = $conn->prepare($sql);

  $stmt->bindColumn(1,$ID);
  $stmt->bindColumn(2,$count);
  $stmt->bindColumn(3,$link);
  $stmt->bindColumn(4,$link2);
  $stmt->execute();
  $numRows = $stmt->rowCount();

  if (isset($numRows)) {

}

while ($stmt->fetch()) {

  $reqTotals+= $count;
  echo $reqTotals;

}

the result from the echo is 000000 when empty. If a request comes in for let's say ID termination it shows 000110 when it should just show 1. A request for TDA shows 000001.

How can I get these to add up? I have tried using the += but that doesn't seem to work. What's odd is if I put the result in a session it works fine.

This works and shows correct numbers. But I don't like using this in a session.

while ($stmt->fetch()) {

  if($count > 0){
    $class = "badge badge-pill badge-danger";
  }else{$class = "badge badge-pill badge-success";}

if($_SESSION['role'] == 'Admin'){
  echo '<div class="btn-group floatRight" role="group" style="font-size: 14px;"><a href="'.$link.'" class="nav-link" style="margin-left: -20px;"> '.$ID.' <span class="'.$class.'" style="margin-left: 4px; margin-right: -6px;"> ' . $count . '</span></a></div>';
  if ($count == 0) {
    $_SESSION['re_totals']+= 0;
  }
  $_SESSION['re_totals']+= $count;
}else{
  echo '<div class="btn-group floatRight" role="group"><a href="'.$link2.'" class="nav-link" style="margin-left: -20px;"> '.$ID.' <span class="'.$class.'" style="margin-left: 4px;"> ' . $count . '</span></a></div>';
  if ($count == 0) {
    $_SESSION['re_totals']+= 0;
  }
  $_SESSION['re_totals']+= $count;
}


}

Current result:
     Request Totals: 000000
Desired Results:
     Request Totals: 0 or 1, 2, 3 etc.

Upvotes: 0

Views: 27

Answers (1)

GMB
GMB

Reputation: 222432

What I am getting for a response shows each category together in succession instead of a total.

Why not simply do the computation in the database?

SELECT 
    (SELECT COUNT(*) AS cnt FROM sku_request WHERE completed = 0)
    + (SELECT COUNT(*) FROM sku_update WHERE completed = 0)
    + (SELECT COUNT(*) FROM rma_submissions WHERE rma_number = 'Pending')
    + (SELECT COUNT(*) FROM idTermination WHERE completed = 0)
    + (SELECT COUNT(*) FROM id_request WHERE ssoid IS NULL)
    + (SELECT COUNT(*) FROM tda_request WHERE completed = 0)
    AS res

Upvotes: 1

Related Questions