asked on

Retrieve rows based on month/year from mysql

Hello,

I'm trying to construct a query to retrieve all the call costs incurred during a particular month passed from a post (post will look like: mydomain.com/script.php&timeperiod=2012-01 for instance) .

The timestamp for each call that has come in during the month is stored in the DB like: 2012-01-17 10:36:21

Not sure how to construct the 'WHERE' part to just look at the month/year and not the day or time?

Here is what I have currently.

$timeperiod = $_REQUEST['timeperiod];

$result = mysql_query("SELECT SUM(CallCost) FROM callers WHERE account_id = '$account_id' AND CallDate = '$timeperiod'");
            while ($row = mysql_fetch_assoc($result)) {
            $totalcost = $row['SUM(CallCost)'];
            $totalcost = sprintf("%01.2f", $totalcost);
      }

So with the above, I'd be trying to retrieve all the call costs that occurred in the month of January 2012 only and totalling them up.

Thanks as always,
TJ

yawkey13

Your query will be:
SELECT SUM(CallCost) FROM callers WHERE account_id = '$account_id' AND MONTH(CallDate) = 'right($timeperiod,2)' AND YEAR(CallDate) = 'left($timeperiod,4)'

Here are the right and left functions:
function right($value, $count){
return substr($value, ($count*-1));
}

function left($string, $count){
return substr($string, 0, $count);
}

The query is solid, but check my PHP. It is not my strong point.

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tjyoung

ASKER

Wish I read that couple months ago :)
Thanks.

Ray Paseur

Thanks for the points! The way PHP functions date() and strtotime() play together is almost magical.