Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

PHP MySQL Query SUM of a field by date

Posted on 2014-10-15
5
Medium Priority
?
554 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Cloud Training Guides

FREE GUIDES: In-depth and hand-crafted Linux, AWS, OpenStack, DevOps, Azure, and Cloud training guides created by Linux Academy instructors and the community.

Question has a verified solution.

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

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
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 Test the speed of a WordPress Website. Site Speed is an important metric of a site’s health. Slow site speed can result in viewers leaving your site quickly and not seeing your content. This…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…

688 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