Ask your WordPress questions! Pay money and get answers fast! (more info)

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)

2016-02-19

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.

2016-02-20

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?

2016-02-22

Arnav Joy answers:

is it still left ?