CODEWITHSUNDEEP
phpmysql
Talon
Talon

Reputation: 4995

How to Sum a Row Within A Certain Date Range in PHP / MySQL

I have a database that has a collection of several rows with data like this:

Username: BillyBob
TotalPrice: 19.99
CreationDate: 2012-02-20 14:30:58

I need to do a SQL Statement in PHP that does something like this.

$sql = mysql_query(" Add up Total Price Where Username='BillyBob' Where CreationDate is within this month ");

How would I do something like that. I also want to do things like, Last 30 Days, Last Week, Specific Date Range etc..

Is that possible?

Upvotes: 0

Views: 1441

Answers (3)

Ingmar Boddington
Ingmar Boddington

Reputation: 3500

Just need to add handling for errors and inputs (returns total for given month), you will just need to alter the monthStart and monthEnd logic / values with whatever period you are interested in.

//Parameters for query
$month = '2012-02';
$username = 'BillyBob';

//Make date start and end
$monthStart = $month.'-01 00:00:00';
$monthEnd = $month.'-'.date('t', strtotime($monthStart)).' 23:59:59';

//Make the query
$query = sprintf("
    SELECT SUM(TotalPrice)
    WHERE Username = '%s'
    AND CreationDate >= '%s'
    AND CreationDate <= '%s'",
    mysql_real_escape_string($username),
    mysql_real_escape_string($monthStart),
    mysql_real_escape_string($monthEnd)
);

$result = mysql_query($query);

if ($result) {
    if (mysql_num_rows($result) == 1) {
        //Output / process number e.g.:
        list($total) = mysql_fetch_num($result);
        echo 'Total for '.$username.' from '.date('l, jS F Y', strtotime($monthStart)).' to '.date('l, jS F Y', strtotime($monthEnd)).' = &pound;'.number_format($total, 2);
    } else {
        //error handling
    }
} else {
    //error handling
}

Upvotes: 2

Lorenzo Marcon
Lorenzo Marcon

Reputation: 8169

I'd try something like this (untested):

$sql = mysql_query("SELECT SUM(TotalPrice) FROM myTable WHERE Username = 'BillyBob' AND CreationDate BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()");

Upvotes: 0

encodes
encodes

Reputation: 751

http://www.tizag.com/mysqlTutorial/mysqlsum.php

you can use mysql sum. see here and apply to your database structure

probably something like Select *,sum(totalPrice) from tbl where Username='BillyBob' and CreationDate <'2012-02-20 14:30:58'

Upvotes: 0

Related Questions