Display total earnings for last week

[Ihsaan]

How would i display total income for last week in php/mySql? My table has the following columns: 

 

ID, Date, Total

[DaveyK]

What is your date? Is it a datestamp, timestamp, unix_timestamp, datetime?

 

I suggest you look into SUM() in SQL

[jazzman1]

The easiest way to make this is to use a mysql proper date format in the date column.

 

SELECT `Total` FROM tbl_name

 WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= `Date`

 OREDER BY `ID` DESC;

 

 

[DaveyK]

Im pretty sure the total the OP is referring to are total per day, so that wont return the totals for the entire week, but rather the totals per day.

[Ihsaan]

Its in the format of 2012-12-11 08:49:38

[Ihsaan]

This is what I have at the moment but nothing shows up.

 

<?php $get_lastweek = mysql_query("SELECT SUM(total) FROM invoices WHERE created >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY AND created < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY");

$show_lastweek = mysql_fetch_array("$get_lastweek"); ?>

<?php echo $show_lastweek; ?>

[Zane]

SELECT SUM(total)
FROM invoices
WHERE created BETWEEN DATEADD(wk, -1, GetDate()) AND GetDate()

[Ihsaan]

Still showing up blank

[Ihsaan]

<? $get_lastweek = mysql_query("SELECT SUM(total) FROM invoices WHERE date_created BETWEEN DATEADD(wk, -1, GetDate()) AND GetDate()");

$show_lastweek = mysql_fetch_array("$get_lastweek"); ?>



<?php echo $show_lastweek ?>

 

[Zane]

It may not be the problem, PHP is pretty forgiving, but why do you have $get_lastweek inside double quotes?  I mean, double quotes WILL parse the variable, but I've never ever seen anyone pass a resource value that way.  Try it without the quotes, just so I don't go crazy...

 

Also, the use of short tags .... ?> suggests to me that you are using an old version of PHP

[Ihsaan]

I tried it without the quotes and I get a server error 500 and when i try it with single quotes i get the same blank result

[DaveyK]

You get a 500 error because you are not using quotes? That seems awfully strange. Never heard that before. Are you sure o_O ?

[Zane]

Try this

<?php
$get_lastweek = mysql_query("SELECT SUM(total) FROM invoices WHERE date_created BETWEEN DATEADD(wk, -1, GetDate()) AND GetDate()");
$show_lastweek = mysql_fetch_array($get_lastweek); ?>


<?php echo "<pre>" . print_r($show_lastweek) . "</pre>"; ?>
 

 

 

Edited March 20, 2013 by Zane

[Ihsaan]

@DaveyK strange but true... Im running xampp maybe that has something to do with it...

 

@Zane Thanks man that makes a diffierence only thing its not echoing what i want. It just echoes "1"

[Ihsaan]

I tried running the following as suggested as a SQL query directly in mySql and I get the following error

 

FUNCTION billing_accounts.DATEADD does not exist

 

Try this

<?php
$get_lastweek = mysql_query("SELECT SUM(total) FROM invoices WHERE date_created BETWEEN DATEADD(wk, -1, GetDate()) AND GetDate()");
$show_lastweek = mysql_fetch_array($get_lastweek); ?>


<?php echo "<pre>" . print_r($show_lastweek) . "</pre>"; ?>
 

Edited February 23, 2014 by Ihsaan

[jazzman1]

If you want to use php functions inside sql string, then the syntax should be different.

 

Try,

$get_lastweek = mysql_query('SELECT SUM(total) FROM invoices WHERE date_created BETWEEN DATEADD(wk, -1,'. GetDate().') AND '.GetDate());

Edited February 23, 2014 by jazzman1

[Barand]

First, define "last week".

 

Is that

• during the previous calendar week (and is that Sun-Sat or Mon-Sun)
• or the last 7 days

[Ihsaan]

Actually the string I ran in mySql is

 

SELECT SUM(total) FROM invoices WHERE date_created BETWEEN DATEADD(wk, -1,'. GetDate().') AND '.GetDate

 

and the error is 

 

FUNCTION billing_accounts.DATEADD does not exist

[Barand]

I'd use

SELECT SUM(total) as total
FROM invoices 
WHERE date_created > CURDATE() - INTERVAL 7 DAY

but the function you were looking for is DATE_ADD() or DATE_SUB() (which is what you want in this instance)

Edited February 25, 2014 by Barand

[Ihsaan]

Thanks Barand that worked only thing is that I would like to get the total of last week's sales and not the last seven days. Any idea how to achieve this?

[Ihsaan]

$get_lastweek = mysql_query("SELECT SUM(total) as total FROM invoices WHERE date_created > CURDATE() - INTERVAL 7 DAY");

$show_lastweek = mysql_fetch_array("$get_lastweek");

 

<?php echo ($show_lastweek); ?>

 

When I insert the above into my code it returns nothing

[Barand]

Yes, answer the question when it's asked

 

 

First, define "last week".

 

Is that

• during the previous calendar week (and is that Sun-Sat or Mon-Sun)
• or the last 7 days

 

 

Then

$dt = new DateTime('last saturday');
$saturday = $dt->format('Y-m-d');
$sql = "SELECT SUM(total) as total
    FROM invoices 
    WHERE date_created BETWEEN '$saturday' - INTERVAL 6 DAY AND '$saturday' ";

[Barand]

 

$get_lastweek = mysql_query("SELECT SUM(total) as total FROM invoices WHERE date_created > CURDATE() - INTERVAL 7 DAY");

$show_lastweek = mysql_fetch_array("$get_lastweek");

 

<?php echo ($show_lastweek); ?>

 

When I insert the above into my code it returns nothing

 

 

should be

<?php echo ($show_lastweek['total']); ?>

[Ihsaan]

Still coming up blank

[Barand]

Turn error reporting on