Jump to content

previous 3 months data php


ianhaney

Recommended Posts

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

 

Link to comment
Share on other sites

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

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

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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