Return Yesterdays Sales

Hi,

I have a table with all my sales from yesterday. In the field i have the date of the order stored in this format: 1286193719

I want to run a statement on the database to find all the sales from yesterday.

I thought this would work:

"SELECT fTotalPrice FROM te5_tbShop_Orders WHERE tmOrderDate = CURRENT_DATE() - INTERVAL 1 DAY"

PHP:

or

"SELECT fTotalPrice FROM te5_tbShop_Orders WHERE date(tmOrderDate) = CURRENT_DATE() - INTERVAL 1 DAY"

PHP:

But it returns 0 even though there are 20 sales yesterday.

Please can someone advise?

Cheers,
Adam

 

Looks like your date is in Unix Epoch time format. You will need to specific a time range to look for data from yesterday. Here's a sample you can try

$yesterday = strtotime(strftime('%Y-%m-%d', time() - 86400));
'SELECT fTotalPrice FROM te5_tbShop_Orders WHERE tmOrderDate >= ' . $yesterday . ' AND tmOrderDate <= ' . ($yesterday + 86399);

Code (markup):

Hope this helps.

 

Hi, you can try and the following :

$query = "SELECT fTotalPrice FROM te5_tbShop_Orders 
WHERE DATE(tmOrderDate) = DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY)";

PHP:

Regards

 

Following shall do the trick:

$day_start_time = mktime(0, 0, 0) - 86400;
$day_end_time = mktime(0, 0, 0) - 1;
$query = "SELECT fTotalPrice FROM te5_tbShop_Orders WHERE tmOrderDate BETWEEN $day_start_time AND $day_end_time"

PHP:

 

Thank you for all your answers i got it working with a reply so big thanks.

I have another issue which i am unsure about, now i have this working i need another query to run which goes through the previous month and gives me a report for each day. Any idea how i would do this?