CODEWITHSUNDEEP
phpmysqlpopulate
ietax
ietax

Reputation: 89

MYSQL and PHP calculate total price of orders table every day and every month, populate new summary table

I have orders table on my DB (id_order, price, desc, delivery) I need to calculate total price for each day

I use this

$date = date("Y-m-d"); 

$query55 = "SELECT id_order, price FROM orders WHERE delivery LIKE '%$date%'";
$result55 = mysql_query($query55) or die(mysql_error());

while ($row55 = mysql_fetch_array($result55))
{
$currency = $row55['id_currency'];
$id = $row55['id_order'];

    if ($currency == "1")
{
    echo '
        <tr>
        <td style="width: 120px;">' . $id_order . '</td>
        <td style="width: 120px;">EUR</td>
        <td style="width: 170px;">' . $row55['price'] . '</td>
        </tr>

';
        $somma_paid += $row55['price'];

}

and it works

At the end i have total day on variable $somma_paid.

I'd like to do it for each days of the year, in automatic. Becouse I need montly total. I need 30/31 rows with each 30/31 totals day

every day I must execute this script. Can I modify it for ask montly total every time for every month. I think that i did not explain it very well...

Another: I can do it with single day, can I do it for all year days before today?

I made another table with all year date and this field (date, price) I think to populate this new table for new ask..

Upvotes: 2

Views: 3635

Answers (2)

RossW
RossW

Reputation: 149

@luceos Would you not use a sum function?

EG: SELECT SUM(price) as total FROM orders WHERE 1 GROUP BY YEAR(delivery), MONTH(delivery), DAY(delivery) ORDER BY delivery

Upvotes: 0

Luceos
Luceos

Reputation: 6730

You'll probably want to group by a day of the year. Best way to do so is something like this:

SELECT id_order, price FROM orders WHERE 1 GROUP BY YEAR(`delivery`),
MONTH(`delivery`), DAY(`delivery`) ORDER BY `delivery`

So if you would like a sum per day:

SELECT SUM(price) FROM orders WHERE 1 GROUP BY YEAR(`delivery`),
MONTH(`delivery`), DAY(`delivery`) ORDER BY `delivery`

Or if you want to format the results with a date, you can use date_format with %j for the day of the year (count from 001 to 366) or by formatted date date_format with `%d-%m-%Y for a format like 31-12-2013 (dd-mm-yyyy)

SELECT SUM(price), DATE_FORMAT(`delivery`,'%j') as `dayoftheyear`, DATE_FORMAT(`delivery`,'%d-%m-%Y') as `date` FROM `orders` WHERE 1 GROUP BY YEAR(`delivery`), MONTH(`delivery`), DAY(`delivery`) ORDER BY `delivery`

You can now loop through your results with $row55['dayoftheyear'] and $row55['date'] for the day of the year and the formatted day.

Or define your own format based on the w3schools documentation (easier to read for some): http://www.w3schools.com/sql/func_date_format.asp or mysql docs: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

Upvotes: 1

Related Questions