ianhaney Posted December 24, 2015 Share Posted December 24, 2015 Hi I see my post has been moved to the mysql forum which is cool, is just a update as need help with the php coding so thought here is the best place I have got the 3 months data SQL query sorted as tested the following in phpmyadmin, I just need help with the php coding and how it would go Below is the SQL query I have and that works 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 get 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 Thank you in advance Ian Quote Link to comment Share on other sites More sharing options...
maxxd Posted December 24, 2015 Share Posted December 24, 2015 The query will return the job costs figures grouped by month, as you need. Add an ORDER BY clause using YEAR(exrdate) AND MONTH(exrdate), then figure out how to calculate the profit margins, and print the data using a loop. Quote Link to comment Share on other sites More sharing options...
ianhaney Posted December 24, 2015 Author Share Posted December 24, 2015 I have the following now SELECT MONTHNAME(exrdate), SUM(job_cost) FROM repairs GROUP BY YEAR(exrdate), MONTH(exrdate) ORDER BY YEAR(exrdate) AND MONTH(exrdate) ASC That outputs it as November December October I tried DESC and put it in descending order so thought ASC would do it with December first then November then October I''ll also get stuck on the php coding but am guessing I will need a while loop before the echo of the data? Quote Link to comment Share on other sites More sharing options...
ginerjm Posted December 24, 2015 Share Posted December 24, 2015 Hmmm. I don't think you need the AND in the order by but it still doesn't make sense. Quote Link to comment Share on other sites More sharing options...
ianhaney Posted December 24, 2015 Author Share Posted December 24, 2015 I have the following SELECT MONTHNAME(exrdate), SUM(job_cost) FROM repairs ORDER BY MONTH(exrdate) ASC but is outputting October £507.93 it is not showing the past 3 months? do I need the group back in? Quote Link to comment Share on other sites More sharing options...
ianhaney Posted December 24, 2015 Author Share Posted December 24, 2015 Ah sorry got the group back in now and is outputting perfect SELECT MONTHNAME(exrdate), SUM(job_cost) FROM repairs GROUP BY YEAR(exrdate), MONTH(exrdate) ORDER BY MONTH(exrdate) DESC It is outputting in phpmyadmin the following December £amount November £amount October £amount Just need to work out the php coding, I will be honest and say I'll get stuck on this bit but taking a guess, I would need the while loop before the echo part That right? Quote Link to comment Share on other sites More sharing options...
Barand Posted December 24, 2015 Share Posted December 24, 2015 The echo would be inside the while loop Quote Link to comment Share on other sites More sharing options...
ianhaney Posted December 24, 2015 Author Share Posted December 24, 2015 Sorry think I sussed it using the following code <?php $servername = "localhost"; $username = ""; $password = ""; $dbname = ""; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $sql = "SELECT MONTHNAME(exrdate) as month, SUM(job_cost), SUM(profit) FROM repairs GROUP BY YEAR(exrdate), MONTH(exrdate) ORDER BY MONTH(exrdate) DESC"; $result = $conn->query($sql); if ($result->num_rows > 0) { echo "<table class='dashboard'> <tr> <th colspan='5'>Total Repairs Cost and Profit Made(Month)</th> </tr> <tr> <th>Month</th> <th>Total Repairs Costs</th> <th>Total Profit Made</th> </tr>"; // output data of each row while($row = $result->fetch_assoc()) { echo "<tr> <td>".$row["month"]."</td> <td>" . '£' .$row["SUM(job_cost)"]."</td> <td>" . '£' .$row["SUM(profit)"]."</td> </tr>"; } echo "</table>"; } else { echo "0 results"; } $conn->close(); ?> I know is not PDO but it works Quote Link to comment Share on other sites More sharing options...
ianhaney Posted December 24, 2015 Author Share Posted December 24, 2015 Just to confirm will the SQL query just return the past 3 months or do I need to add INTERVAL 3 month into it? Quote Link to comment Share on other sites More sharing options...
Barand Posted December 24, 2015 Share Posted December 24, 2015 You will need a WHERE clause otherwise you will get all months in the table. Quote Link to comment Share on other sites More sharing options...
ianhaney Posted December 24, 2015 Author Share Posted December 24, 2015 I thought that too so now got the following in my query SELECT MONTHNAME(exrdate), job_cost as job_cost FROM repairs WHERE exrdate BETWEEN DATE_SUB(now(), INTERVAL 3 MONTH) AND now() October 0.00 November 65.00 November 40.50 November 30.00 December 115.00 December 25.00 December 89.95 December 67.49 December 30.00 December 29.99 December 15.00 and it works but it does it displays the costs individually so added SUM into it and made it output like the following October 507.93 The query I got to output the above is below SELECT MONTHNAME(exrdate), SUM(job_cost) as job_cost FROM repairs WHERE exrdate BETWEEN DATE_SUB(now(), INTERVAL 3 MONTH) AND now() I thought that was how the SUM was added into a SQL query? Quote Link to comment Share on other sites More sharing options...
Barand Posted December 24, 2015 Share Posted December 24, 2015 What happened to your GROUP BY and ORDER BY? Quote Link to comment Share on other sites More sharing options...
ianhaney Posted December 24, 2015 Author Share Posted December 24, 2015 Think I have it now, if ok to quickly check it over to make sure is all ok, it does seem to be on the php page <?php $servername = "localhost"; $username = ""; $password = ""; $dbname = ""; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $sql = "SELECT MONTHNAME(exrdate) as month, SUM(job_cost) as job_cost, SUM(profit) as profit FROM repairs WHERE exrdate BETWEEN DATE_SUB(now(), INTERVAL 3 MONTH) AND now() GROUP BY YEAR(exrdate), MONTH(exrdate) ORDER BY MONTH(exrdate) DESC"; $result = $conn->query($sql); if ($result->num_rows > 0) { echo "<table class='dashboard'> <tr> <th colspan='5'>Total Repairs Cost and Profit Made(Month)</th> </tr> <tr> <th>Month</th> <th>Total Repairs Costs</th> <th>Total Profit Made</th> </tr>"; // output data of each row while($row = $result->fetch_assoc()) { echo "<tr> <td>".$row["month"]."</td> <td>" . '£' .$row["job_cost"]."</td> <td>" . '£' .$row["profit"]."</td> </tr>"; } echo "</table>"; } else { echo "0 results"; } $conn->close(); ?> Quote Link to comment Share on other sites More sharing options...
Barand Posted December 24, 2015 Share Posted December 24, 2015 That query will get you those records with dates between "2015-09-24 16:51:43" and "2015-12-24 16:51:43". Is that what you want? Quote Link to comment Share on other sites More sharing options...
ianhaney Posted December 24, 2015 Author Share Posted December 24, 2015 Ahh ok no I don't want that, want it from 1st October 2015 to 31st December 2015 so is a full 3 months of data Quote Link to comment Share on other sites More sharing options...
ianhaney Posted December 24, 2015 Author Share Posted December 24, 2015 I just realised its cause got now() in the SQL query what would the query be to make it a full 3 months so starts at the 1st of the month and not the 24th Quote Link to comment Share on other sites More sharing options...
Barand Posted December 24, 2015 Share Posted December 24, 2015 ... WHERE exrdate BETWEEN LAST_DAY(CURDATE()) + INTERVAL 1 DAY - INTERVAL 3 MONTH AND LAST_DAY(CURDATE()) Quote Link to comment Share on other sites More sharing options...
ianhaney Posted December 24, 2015 Author Share Posted December 24, 2015 Thank you so much, is perfect Quote Link to comment Share on other sites More sharing options...
Barand Posted December 24, 2015 Share Posted December 24, 2015 Merry Christmas! Quote Link to comment Share on other sites More sharing options...
ianhaney Posted December 24, 2015 Author Share Posted December 24, 2015 Thank you Barand, Merry Christmas to you too and Happy New Year if not on here before New Year 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.