Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Retrieve rows based on month/year from mysql

Posted on 2012-04-10
4
Medium Priority
?
586 Views
Last Modified: 2012-04-11
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
0
Comment
Question by:tjyoung
  • 2
4 Comments
 
LVL 6

Expert Comment

by:yawkey13
ID: 37830574
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.
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 37830675
Please read this article:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

You can get the first day of a month with something like this:
URL: ...&timeperiod=2012-01
$alphaperiod = date('c', strtotime($_GET['timeperiod']  . '-01'));

And the last day of a month with something like this:
$omegaperiod = date('c', strtotime($_GET['timeperiod'] . '-t'));

Then you can use these values in the WHERE clause like this:
WHERE CallDate BETWEEN '$alphaperiod' AND '$omegaperiod'
0
 
LVL 1

Author Closing Comment

by:tjyoung
ID: 37830741
Wish I read that couple months ago :)
Thanks.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 37831868
Thanks for the points!  The way PHP functions date() and strtotime() play together is almost magical.
0

Featured Post

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question