My webpage isn't showing database results

My summary page is not working as I think it should. It correctly prints $monthlyTotal and $yearlyTotal. However, it is not printing $totalSilvers nor $totalGolds. The queries run fine when I use SQL Workbench. Can you see something I’m overlooking?

Thanks!


$monthlyRevenue = "SELECT sum(amount) as total FROM payments WHERE YEAR(payDate) = YEAR(CURDATE()) AND MONTH(payDate) = MONTH(CURDATE()) and uID in (select uID from users where subscription = 'Silver' or subscription = 'Gold')";
$result = mysql_query($monthlyRevenue) or die(mysql_error());
$row = mysql_fetch_assoc($result);
$monthlyTotal = $row['total'];


$yearlyRevenue = "SELECT sum(amount) as total FROM payments WHERE YEAR(payDate) = YEAR(CURDATE())";
$result = mysql_query($yearlyRevenue) or die(mysql_error());
$row = mysql_fetch_assoc($result);
$yearlyTotal = $row['total'];


$countSilvers = "SELECT count(distinct uID) from payments where type = 'Silver' and uID in (select uID from users where subscription = 'Silver');";
$result = mysql_query($countSilvers) or die(mysql_error()); 
$row = mysql_fetch_assoc($result);
$totalSilvers = $row['total'];


$countGolds = "SELECT count(distinct uID) from payments where type = 'Gold' and uID in (select uID from users where subscription = 'Gold');";
$result = mysql_query($countGolds) or die(mysql_error());
$row = mysql_fetch_assoc($result);
$totalGolds = $row['total'];


$topMessage = "<p>There are $totalSilvers Silvers and $totalGolds Golds.  Revenue for the current month is $monthlyTotal and $yearlyTotal for the year.</p>";

It’s because the variable $row[‘total’] does not exist on the last 2 queries.

I don’t know if it’s what you want to achieve but you can use


SELECT count(distinct uID) as total

Well i’d immediately say use a single query to do this instead of 4, but joms’ post is the answer.

Thanks, I got it working again. But how would I make a single query out of these four? They are based on separate criteria.

Actually, you’re right, you’ll need two queries.
(I’m going to butcher this, and r937 will probably come along and do it much cleaner ;P)


SELECT sum(CASE WHEN type='Silver' THEN 1 ELSE 0 END) as silver, sum(CASE WHEN type='Gold' THEN 1 ELSE 0 END) as gold
FROM payments 
GROUP BY uID 
ORDER BY payDate DESC

(Why are you saving type in the payments table, anyway? Isn’t that a property of User, rather than Payment?)


SELECT SELECT sum(amount) as total, sum(CASE WHEN (type = 'Silver' OR type='Gold') AND MONTH(payDate) = MONTH(CURDATE()) THEN amount ELSE 0 END) as totalmonth
FROM payments 
WHERE YEAR(payDate) = YEAR(CURDATE())
ORDER BY payDate DESC

those 4 queries cannot be combined (except in a UNION query, which would be only marginally better than simply running 4 queries one after the other)

i’ve taken the liberty of re-formatting and optimizing them –


SELECT SUM(payments.amount) AS total_monthlyRevenue
  FROM users
INNER
  JOIN payments 
    ON payments.uID = users.uID
   AND payments.payDate >= CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY
   AND payments.payDate  < CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY
                                        + INTERVAL 1 MONTH 
 WHERE users.subscription IN ( 'Silver' , 'Gold' )
SELECT SUM(payments.amount) AS total_yearlyRevenue
  FROM payments 
 WHERE payments.payDate >= CURRENT_DATE - INTERVAL DAYOFYEAR(CURRENT_DATE)-1 DAY
   AND payments.payDate  < CURRENT_DATE - INTERVAL DAYOFYEAR(CURRENT_DATE)-1 DAY
                                        + INTERVAL 1 YEAR 
SELECT COUNT(DISTINCT uID) AS countSilvers
  FROM users
INNER
  JOIN payments 
    ON payments.uID = users.uID
   AND payments.type = 'Silver' 
 WHERE users.subscription = 'Silver'
SELECT COUNT(DISTINCT uID) AS countGolds
  FROM users
INNER
  JOIN payments 
    ON payments.uID = users.uID
   AND payments.type = 'Gold' 
 WHERE users.subscription = 'Gold'