display totals from previous month

[ianhaney]

Hi

 

I am trying to output the job_cost and profit from the previous month but am getting no output, I have the following coding

<table class="dashboard">
<tr>
<th colspan="5">Total Jobs Cost and Profit Made(Month)</th>
</tr>
<tr>
<th>Month</th>
<th>Total Job Costs</th>
<th>Total Profit Made</th>
</tr>

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

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

I know it is the SELECT query where I have messed up on somewhere but is about it, I am not sure what it should be, sorry

 

Thank you in advance

 

Ian

[Barand]

You need two separate SUM()s

SELECT SUM(job_cost) as job_cost, SUM(profit) as profit .....

[ianhaney]

Hi Barand

 

Ok, I now have the following but still not getting any output

SELECT SUM(job_cost) as job_cost, SUM(profit) as profit FROM repairs WHERE exrdate >= exrdate(exrdate, INTERVAL 1 MONTH

[Barand]

exrdate is not a function.

SELECT SUM(job_cost) as job_cost, SUM(profit) as profit FROM repairs WHERE exrdate >= CURDATE() - INTERVAL 1 MONTH

[ianhaney]

Ahh ok cool got it

 

it is outputting amounts showing for December as well and not just for November, sorry, I want it to output the total cost and total profit for just the current month, sorry

[Barand]

... WHERE MONTH(exrdate) = MONTH(CURDATE()) AND YEAR(exrdate) = YEAR(CURDATE())

[ianhaney]

Is this right as not getting nothing outputted?

SELECT SUM(job_cost) as job_cost, SUM(profit) as profit FROM repairs WHERE MONTH(exrdate) = MONTH(CURDATE()) AND YEAR(exrdate) = YEAR(CURDATE)())

[ianhaney]

Sorry my fault, I had a extra (

 

I now have the following and is outputting data now that looks right

SELECT SUM(job_cost) as job_cost, SUM(profit) as profit FROM repairs WHERE MONTH(exrdate) = MONTH(CURDATE()) AND YEAR(exrdate) = YEAR(CURDATE())

[ianhaney]

Sorry last one, is it possible to display the current Month word in the table

 

I have the following code

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

If possible to have the word November displayed where the first td row is?

[Barand]

Yes, SQL has a MONTHNAME() function

 

http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html

[ianhaney]

Sorry I don't get that bit, I looked at the link you gave and see the following

 

mysql> SELECT MONTHNAME('2008-02-03');
-> 'February'

 

do I directly add that into the SELECT query?

 

I don't get how to add it into the php code?

[ianhaney]

I got the following now but is not outputting the month name

SELECT SUM(job_cost) as job_cost, SUM(profit) as profit FROM repairs WHERE MONTHNAME(exrdate) = MONTHNAME(CURDATE()) AND YEAR(exrdate) = YEAR(CURDATE())

do I need to add MONTHNAME after as profit

Edited November 30, 2015 by ianhaney

[Barand]

mysql> SELECT MONTHNAME('2008-02-03');

-> 'February'

 

do I directly add that into the SELECT query?

Of course you don't (unless you want "February" output every time). That is what is known as "an example". Strangely, the writers of the MySQL manual did not know that your date field would be "exrdate", so you need to substitute that yourself.

SELECT MONTHNAME(exrdate) as month, ....

then you would output it with " $row['month'] "

[ianhaney]

Thank you so much, is working perfect now

 

Just to confirm, will it reset on the 1st of each month so tomorrow 1/12/2015 should reset to display the amount for December

 

Is that right?

[Barand]

That's the theory. Whatever the current month is, you will get the total for that month.

[ianhaney]

Ok great perfect, thank you so much for your help, appreciate it