Sorry Jason, I should have explained more what I need to do. At the moment I have the following recordset:
<?php
$paramSession_rsAccountants = "-1";
if (isset($_SESSION['JFBMicroID'])) {
$paramSession_rsAccountants = $_SESSION['JFBMicroID'];
}
mysql_select_db($database_new_greenroom, $new_greenroom);
$query_rsAccountants = sprintf("SELECT *, COUNT(clients.AccountantID) FROM clients RIGHT OUTER JOIN accountants ON clients.AccountantID = accountants.AccountantID WHERE accountants.JFBMicroID = %s AND (clients.StagingDate BETWEEN '2015-02-01' AND '2015-02-28') GROUP BY accountants.AccountantID, clients.AccountantID", GetSQLValueString($paramSession_rsAccountants, "text"));
$rsAccountants = mysql_query($query_rsAccountants, $new_greenroom) or die(mysql_error());
$row_rsAccountants = mysql_fetch_assoc($rsAccountants);
$totalRows_rsAccountants = mysql_num_rows($rsAccountants);
?>
I then echo the total count for February using <?php echo $row_rsAccountants['COUNT(clients.AccountantID)']; ?>
However because I have 36 months' of data, I need to echo the total for each month separately from Feb 2015 - Feb 2018 and so would like to build the query into the PHP syntax rather than have 36 recordsets and query each one individually - it's just that I'm not great on PHP syntax!
So ideally I would like a basic recordset like:
<?php
$paramSession_rsAccountants = "-1";
if (isset($_SESSION['JFBMicroID'])) {
$paramSession_rsAccountants = $_SESSION['JFBMicroID'];
}
mysql_select_db($database_new_greenroom, $new_greenroom);
$query_rsAccountants = sprintf("SELECT *, COUNT(clients.AccountantID) FROM clients RIGHT OUTER JOIN accountants ON clients.AccountantID = accountants.AccountantID WHERE accountants.JFBMicroID = %s GROUP BY accountants.AccountantID, clients.AccountantID", GetSQLValueString($paramSession_rsAccountants, "text"));
$rsAccountants = mysql_query($query_rsAccountants, $new_greenroom) or die(mysql_error());
$row_rsAccountants = mysql_fetch_assoc($rsAccountants);
$totalRows_rsAccountants = mysql_num_rows($rsAccountants);
?>
and then put the date range in the individual PHP queries like:
<?php echo $row_rsAccountants['COUNT(clients.AccountantID)'] WHERE (clients.StagingDate BETWEEN '2015-02-01' AND '2015-02-28') ; ?>
<?php echo $row_rsAccountants['COUNT(clients.AccountantID)'] WHERE (clients.StagingDate BETWEEN '2015-03-01' AND '2015-03-31') ; ?>
I know that the syntax is wrong, but does that make sense?