Jump to content

php mysql total profit help


ianhaney
 Share

Recommended Posts

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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)
Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.