Help with custom SQL query WordPress
I am trying to get the results of a custom query.
I want to group by date all the posts which have a post_date lower or equal to a specified value and a post_modified date higher or equal to a specified value (or not set).
Here is a sample sql fiddle:
http://sqlfiddle.com/#!9/ee0447/1
The results I would like returned are the query run against each date in the set instead of just 1 time against the full range.
ex:
date | count of entries matching parameters
2016-02-12 | 3
2016-02-13 | 3
2016-02-14 | 5
2016-02-15 | 5
2016-02-16 | 5
2016-02-17 | 4
2016-02-18 | 5
Where
2016-02-12 | 3 => All the entries with a post day on or before 2016-02-12 and a post_modified date on or after 2012-02-12
2016-02-13 | 3 => All the entries with a post day on or before 2016-02-13 and a post_modified date on or after 2012-02-13
2016-02-14 | 5 => All the entries with a post day on or before 2016-02-14 and a post_modified date on or after 2012-02-14
etc
Answers (3)
Rempty answers:
use this
SELECT COUNT(*) as c, post_date
FROM wp_posts
WHERE
post_date <= '2016-02-14'
AND
( post_modified >= '2016-02-18'
OR post_modified = '0000-00-00 00:00:00' ) GROUP by post_date
Chuck Mac comments:
Thanks for the response, however this is not what I am looking for. I do not want them output by post date, but rather by the dates being queried against.
Rempty comments:
I think you can't do via SQL,
you need to use php
Just SUM the count, example
With my query you get
2016-02-12 | 3
2016-02-13 | 3
2016-02-14 | 5
But must be
2016-02-12 = 3 posts
2016-02-13 = posts from 2016-02-12 + 2016-02-13= 3+3
2016-02-14 = posts from 2016-02-12 + 2016-02-13 + 2016-02-14 = 3+3+5
Chuck Mac comments:
With your query you get:
c post_date
3 February, 10 2016 00:00:00
1 February, 14 2016 00:00:00
Your query groups by post_date though, which is not what I am looking for. It should be grouped by the dates queried against.
Also, adding up the dates like that in php is not correct either. Each query should be individual, not the sum of the previous day.
Reigel Gallarde answers:
did you want something like this?
SELECT post_date, COUNT( DISTINCT ID ), DAY(post_date) as period
FROM wp_posts
WHERE
post_date <= '2016-02-18'
AND
( post_modified >= '2016-02-18'
OR post_modified = '0000-00-00 00:00:00' )
GROUP by period
Chuck Mac comments:
Your query returns:
post_date COUNT( DISTINCT ID ) period
February, 10 2016 00:00:00 3 10
February, 14 2016 00:00:00 1 14
February, 18 2016 00:00:00 1 18
I don't want it grouped by post_date. The query should return the results for each date queried against.
Reigel Gallarde comments:
your question is not clear... your illustration is not clear... can you perhaps tell us what you'll do with the results? by then maybe we will know what you're up to and then we can suggest the solution you want...
in the first line, why is there a date here? why is there a number 3 here? I see this as a date with 3 posts.
2016-02-12 | 3
2016-02-13 | 3
2016-02-14 | 5
2016-02-15 | 5
2016-02-16 | 5
2016-02-17 | 4
2016-02-18 | 5
is your goal to display this things as is?
