Jump to content

retrieve last 3 months data and display in PHP


ianhaney

Recommended Posts

Hi

 

Sorry just a quick one, I was wondering how can I retrieve data from the last 3 months to show the costs and profit and display in PHP

 

I currently have the following

<?php
$hostname="localhost";
$username="";
$password="";
$db = "";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT SUM(job_cost) as job_cost, SUM(profit) as profit, MONTHNAME(exrdate) as month FROM repairs WHERE MONTH(exrdate) = MONTH(CURDATE()) AND YEAR(exrdate) = YEAR(CURDATE())') as $row) { 
?>

<tr>
<td><?php echo $row['month']; ?></td> 
<td><?php echo '£' . $row['job_cost']; ?></td>
<td><?php echo '£' . $row['profit']; ?></td>
</tr>
<?php
}
?>

That outputs the following

 

December                  £costs                      £profit

 

What I am aiming for is something like the following if possible

 

December                 £costs for december                      £profit made in december

November                 £costs for november                      £profit made in november

October                     £costs for october                          £profit made in october

 

I found the following code, is it similar to this

select * from table where timestamp >= last_day(now()) + interval 1 day - interval 3 month;
Link to comment
Share on other sites

I got the SQL query correct using the following as tested it in the phpmyadmin

 

SELECT MONTHNAME(exrdate), SUM(job_cost) 
FROM repairs
GROUP BY YEAR(exrdate), MONTH(exrdate)
 
How do I now alter the php coding to show the following output
 
December                £job costs figure            £profit figure
 
November                £job costs figure            £profit figure
 
October                   £job costs figure            £profit figure
Link to comment
Share on other sites

Sorry I just worked out that the SQL query above would show all months and not just the previous 3 months so trying to alter it but is outputting wrong within phpmyadmin, I am trying the following

 

SELECT MONTHNAME(exrdate), SUM(job_cost) as job_cost FROM repairs WHERE  `exrdate` >= last_day(now()) + interval 1 day - interval 3 month;

 

That is outputting the following

 

October £507.93

 

I need it like below

 

December                £job costs figure            £profit figure
 
November                £job costs figure            £profit figure
 
October                   £job costs figure            £profit figure
Edited by ianhaney
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

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.

×
×
  • 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.