ianhaney Posted December 24, 2015 Share Posted December 24, 2015 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; Quote Link to comment Share on other sites More sharing options...
ianhaney Posted December 24, 2015 Author Share Posted December 24, 2015 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 Quote Link to comment Share on other sites More sharing options...
ianhaney Posted December 24, 2015 Author Share Posted December 24, 2015 (edited) 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 December 24, 2015 by ianhaney Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.