php mysql total profit help

[ianhaney]

Hi

I need bit of help, I want to display the total amount earned in a month for the past three months

I am sort of there but got stuck

I want the page to look like the following

Profit in April: £amount
Profit in May: £amount
Profit in June: £amount

the mysql columns I need adding together are the following, I know how to do this part

deposit_paid
rembl_paid_amount

the bit I am stuck on is the following

I got a mysql column called date_deposit_paid and date_rembl_paid and am unsure how to get the data added from them two date fields so for example if a deposit is paid in May and the remaining balance is paid in June, I need the amounts to show under the correct month, below is the coding I have

<?php 
$mysqli = new mysqli('localhost','password', 'db'); 

$sql = "SELECT MONTHNAME(date_deposit_paid) as month, MONTHNAME(date_rembl_paid) as month, SUM(deposit_paid + rembl_paid_amount) as total FROM projects  
WHERE date_deposit_paid AND date_rembl_paid BETWEEN LAST_DAY(CURDATE()) + INTERVAL 1 DAY - INTERVAL 3 MONTH AND LAST_DAY(CURDATE()) 
GROUP BY YEAR(date_deposit_paid), MONTH(date_deposit_paid), YEAR(date_rembl_paid), MONTH(date_rembl_paid) 
ORDER BY MONTH(date_deposit_paid), MONTH(date_rembl_paid) ASC"; 
$result = $mysqli->query($sql); 

if ($result->num_rows > 0) { 
    while($row = $result->fetch_assoc()) { 
    echo "<tr> 
    <td>".'Profit Made in '.$row["month"]."</td> 
    <td>" . '£' .$row["total"]."</td> 
    </tr>"; 
    } 
} else { 
    echo "0 results"; 
} 
$mysqli->close(); 
?>

Hope it makes sense

Thank you in advance

[ginerjm]

Loop thru the results storing the values into an array that holds the values for each month.

$ar['mth1']['val1'] += (amt);
$ar['mth1']['val2'] += (amt);

 

(rough code and un-tested)

 

Then output your $ar values as you want them. You'll have to check for things like if either date falls in your desired months before saving its value.

 

PS - couldn't you simplify your query by just looking for a start date and end date instead of all the functions?

[Barand]

You probably want something along these lines

SELECT 
    dep.month 
  , deptotal
  , baltotal
  , deptotal + baltotal as total
FROM (
    SELECT 
        EXTRACT(YEAR_MONTH FROM date_deposit_paid) as ym
        MONTHNAME(date_deposit_paid) as month
      , SUM(deposit_paid) as deptotal
    FROM projects
    GROUP BY ym
    ) dep
JOIN (
    SELECT 
        EXTRACT(YEAR_MONTH FROM date_rembl_paid) as ym 
      , SUM(rembl_paid_amount) as baltotal 
    FROM projects  
    GROUP BY ym
    ) bal USING (ym)