previous 3 months data php

[ianhaney]

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

 

[maxxd]

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.

[ianhaney]

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?

[ginerjm]

Hmmm.  I don't think you need the AND in the order by but it still doesn't make sense. 

[ianhaney]

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?

[ianhaney]

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?

[Barand]

The echo would be inside the while loop

[ianhaney]

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

[ianhaney]

Just to confirm will the SQL query just return the past 3 months or do I need to add INTERVAL 3 month into it?

[Barand]

You will need a WHERE clause otherwise you will get all months in the table.

[ianhaney]

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?

[Barand]

What happened to your GROUP BY and ORDER BY?

[ianhaney]

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();
?>

[Barand]

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?
 

[ianhaney]

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

[ianhaney]

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

[Barand]

... WHERE exrdate BETWEEN LAST_DAY(CURDATE()) + INTERVAL 1 DAY - INTERVAL 3 MONTH AND LAST_DAY(CURDATE())

[ianhaney]

Thank you so much, is perfect

[Barand]

Merry Christmas!

[ianhaney]

Thank you Barand, Merry Christmas to you too and Happy New Year if not on here before New Year