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

x
?
Solved

PHP MySQL Query SUM of a field by date

Posted on 2014-10-15
5
Medium Priority
?
560 Views
Last Modified: 2014-10-15
I have a wordpress Website.
I want to be able to query the SUM of a meta_value field by DATE.  HOWEVER, I want to always query the same way, no matter what the date is. I always want it to be the prior month.
No matter what today is, I want to Query all of the previous month and none of the current month.

Does that make sense?
Something like the following?
$pdf_d = $pdo->prepare("SELECT SUM(meta_value) AS sum
					FROM wp_woocommerce_order_itemmeta
					WHERE meta_key = '_line_total'
					AND meta_key = '_subscription_start_date > DATE_SUB( NOW(), INTERVAL 1 MONTH )'");

Open in new window

0
Comment
Question by:rgranlund
  • 3
5 Comments
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 40382743
Without having actual data to test with, it is a bit tricky but this seemed to work. I have altered it to work with your field names and tablenames and hopefully it survived the process

SELECT
     YEAR( _subscription_start_date ),
     MONTH( _subscription_start_date ),
     SUM(meta_value) as summaryCount

FROM
     wp_woocommerce_order_itemmeta

WHERE
     _subscription_start_date < DATE_FORMAT( NOW(), '%Y-%m-00' ) AND
     meta_key = '_line_total'

GROUP BY
     YEAR( _subscription_start_date ) DESC,
     MONTH( _subscription_start_date ) DESC

ORDER BY
     YEAR( _subscription_start_date ) DESC,
     MONTH( _subscription_start_date ) DESC

LIMIT 1

Open in new window

0
 
LVL 7

Author Comment

by:rgranlund
ID: 40382756
@beverly, I needed to change my question a little.  Maybe this makes more sense:
$first_day = date("Y-m-d", mktime(0, 0, 0, date("m")-1, 1, date("Y")));
$last_day = date("Y-m-d", mktime(0, 0, 0, date("m"), 0, date("Y")));

$pdf_d = $pdo->prepare("SELECT SUM(meta_value) AS sum
					FROM wp_woocommerce_order_itemmeta
					WHERE meta_key = '_line_total'
					AND meta_key = '_subscription_start_date' >= :first_day
					AND meta_key = '_subscription_start_date' <= :end_day");

Open in new window

0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 40382768
OK, I will have a look, but I notice that you have $first_day and $last_day but your query has first_day and end_day. Is that a typo?
0
 
LVL 34

Accepted Solution

by:
Beverley Portlock earned 2000 total points
ID: 40382793
OK... what about

$first_day = date("Y-m-d", mktime(0, 0, 0, date("m")-1, 1, date("Y")));
$last_day = date("Y-m-d", mktime(0, 0, 0, date("m"), 0, date("Y")));

$pdf_d = $pdo->prepare("SELECT SUM(meta_value) AS sum
                         FROM wp_woocommerce_order_itemmeta
                         WHERE meta_key = '_line_total'
                         AND '_subscription_start_date' >= ':first_day'
                         AND '_subscription_start_date' <= ':last_day' ");
                                          

Open in new window


I have removed the meta_key from the 2nd and 3rd subclauses because it makes no sense to have it there with a different value than the first subclause. That just guarantees and empty result as well as being syntactically wrong.

I have also enclosed the :first_day and :last_day variables in single quotes. I presume that the colons are part of PDO's syntax? I would have encoded it with $ signs as follows

$first_day = date("Y-m-d", mktime(0, 0, 0, date("m")-1, 1, date("Y")));
$last_day = date("Y-m-d", mktime(0, 0, 0, date("m"), 0, date("Y")));

$pdf_d = $pdo->prepare("SELECT SUM(meta_value) AS sum
                         FROM wp_woocommerce_order_itemmeta
                         WHERE meta_key = '_line_total'
                         AND '_subscription_start_date' >= '$first_day'
                         AND '_subscription_start_date' <= '$last_day' ");
                                          

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40383320
without the benefit of data, or actual values in those date variables, I would just caution you about using >= with <=

That is the same as using between i.e.

                    select * from x where dt >= '2014-01-01' and dt <= '2014-12-31'

is exactly the same as using:

                    select * from x where dt between '2014-01-01' and  '2014-12-31'


But the most reliable method of filtering for a date range is to use >= with < like this:

                    select * from x where dt >= '2014-01-01' and dt < '2015-01-01'

see: "Beware of Between"
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The purpose of this video is to demonstrate how to integrate Mailchimp with WordPress, by placing a Mailchimp signup form on a WordPress Page or Post. This will be demonstrated using a Windows 8 PC. Mailchimp will be used. Log into your Mailchi…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

971 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