CODEWITHSUNDEEP
phpmysqlsql
geds13
geds13

Reputation: 191

Displaying data from a specific date php

I would like to display data from a specific date, for example, total income for a day, or total income for a week and so on.

I have no idea how to do it, I have only created a query that would display a total, but i also need it to display specific dates. as i've said above.

the data type in my database table is TIMESTAMP, with a format like 2014-08-28 04:08:23.

Here is my query.

$qry = "SELECT sum(order_detail.quantity*order_detail.price) as chenes, orders.date 
        FROM order_detail 
        LEFT JOIN orders 
        ON order_detail.orderid=orders.serial";

I know i would use WHERE clause but after that, i have no idea what to do next. Please help me Thank you.

Upvotes: 0

Views: 604

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You can express the query as:

SELECT sum(od.quantity*od.price) as chenes, o.date 
FROM order_detail od JOIN
     orders o
ON od.orderid = o.serial
WHERE date(o.date) = YOURDATEHERE

However, this format makes it difficult for MySQL to use an index on the date column. A better representation is:

SELECT sum(od.quantity*od.price) as chenes, o.date 
FROM order_detail od JOIN
     orders o
ON od.orderid = o.serial
WHERE o.date >= YOURDATEHERE and o.date < YOURDATEHERE + interval 1 day;

This would allow MySQL to take advantage of an index on date.

Note that I changed the left join to a join. You would have a bad database design if order_detail records did not have valid links to the order table. In addition, you want to filter on the second table, which would turn the outer join into an inner join anyway.

Upvotes: 1

Related Questions