sql is not picking up duplicate dates

I have a wordpress site where the dates are storing in this way 24/01/2014 under meta_value. If there are 2 dates with the same value then my sql is picking up only one date.

My query is :

SELECT wp_posts.* ,wp_postmeta.* 
       FROM wp_posts,wp_postmeta 
       WHERE wp_posts.ID = wp_postmeta.post_id 
             AND wp_posts.post_status = 'publish' 
             AND wp_postmeta.meta_key='advert_date' 
             AND STR_TO_DATE(wp_postmeta.meta_value, '%d/%m/%Y') BETWEEN '2014-01-01' AND '2014-01-31' 
      GROUP BY wp_postmeta.meta_value 
      ORDER BY wp_postmeta.meta_value asc

There are total 7 records and the dates are

1 -- 03/01/2014

2 -- 08/01/2014

3 -- 10/01/2014

4 -- 16/01/2014

5 -- 24/01/2014

6 -- 24/01/2014

7 -- 31/01/2014

Whereas it is showing only 6 records and ignoring record id 6.

Is it ignoring the duplicate records?

Solutions

You need to remove group by wp_postmeta.meta_value from your SQL.

group by statement in mysql combines entries that are duplicates. It's very handy. Since you have grouped by the date, it will 'aggregate' all dates in common. However only the first one is shown that was grouped by. This is typically useful when using an aggregate function like SUM or AVERAGE. Say you want to know all the money made on a date. Group by the date and SUM up the money. YOu will get an entry for each date and how much money.

Tags: Mysql / Wordpress / Date

Similar questions

How to only keep posts with dates in between other dates?
I have some posts with some custom fields where I have a date in a string format, so I loop my posts and I convert each of them as a Date, then I do some bit to have them in order by date: So I am attaching the date together with the ids and that gives us: OK now what we do is GET some $start and $end dates from a form using GET And that gives us: ...
No disabled dates in datepicker using php array of dates
I'm trying to take dates from a date field in a custom post type in wordpress and then transfer the dates into an array. Then that array is converted to a js object. That js object is used in a datepicker with beforeShowDay, but it is not working. I get the datepicker but no dates are unavaible. If I do: in the php part i get: Array ( [0] => 202...
Wordpress Search not picking up text provided by a Plugin
I have a site plugin that fills in text on pages remotely. Wordpress does not search the pages created by the plugin. I tried Google custom search engine code, no good. How can I search this text? Also how can I show search snippets not entire posts listed out? Site is at www.SiteNamePro.com. The domains listed are filled in by the plugin. I want u...
mamp localhost not picking up wp-login
looking for some help moving my wordpress site to a new Mac. I've exported my database through phpmyadmin on the original device and imported it on the phpmyadmin on my new device. I've also copied the Wordpress folder over to htdocs in my mamp folder. When I'm running mamp I can connect to my wordpress site through localhost:8888 on my new compute...
WP Ajax Action Not Picking Up Query String Parameter
Not sure why this isn't working... my action is being passed and the right function is firing from my ajax, but the callback isn't receiving my query parameter. My ajax (using bloodhound.js running from the administrative screens, so no need to localize): If you're not familiar with Bloodhound, the query it renders looks like this: admin-ajax.php?q...
Why is wp_list_authors not picking up Authors from CPT's?
Why is wp_list_authors not picking up Authors from CPT's when I have the supports option enabled? From what I have read elsewhere it looks like I have everything covered but the authors of CPT's refuse to show. I am trying to get the default post and CPT authors to show in one unified list. Could someone show me what I am doing wrong? FUNCTIONS.PHP...

Also ask

We use cookies to deliver the best possible experience on our website. By continuing to use this site, accepting or closing this box, you consent to our use of cookies. To learn more, visit our privacy policy.