Rewrite sql to support joins
I have the following query which in oracle databases should work but current wordpress instalation uses a mysql database. Could you please help me out to rewrite the next query so it will work on mysql ?
$wpdb->get_results("SELECT a.ID, a.post_title, DAYOFMONTH(b.meta_value) as dom "
."FROM $wpdb->posts a, $wpdb->postmeta b "
."WHERE b.meta_value >= '{$thisyear}-{$thismonth}-01 00:00:00' "
."AND b.meta_value <= '{$thisyear}-{$thismonth}-{$last_day} 23:59:59' "
."AND a.post_type = 'post' AND a.post_status = 'publish' AND a.ID = b.postid AND b.meta_key='Event Date'"
);
Solutions
There isn't an issue with your query in MySQL from what I can tell. Both of the following statements produce the same results (although I prefer to use the
JOIN
). Here is a simplified version:
SELECT *
FROM table1 t
JOIN table2 t2 on t.id = t2.id
WHERE t.dt >= '2011-01-02 00:00:00';
SELECT *
FROM table1 t, table2 t2
WHERE t.dt >= '2011-01-02 00:00:00'
AND t.id = t2.id;
SQL Fiddle Demo
You may have a problem with the date format of the constant. I would suggest converting the date into the same format using
to_char()
. I'm thinking something along the lines of:
SELECT a.ID, a.post_title, to_char(b.meta_value, 'DD') as dom "
."FROM $wpdb->posts a, $wpdb->postmeta b "
."WHERE to_char(b.meta_value, 'YYYY-MM-DD HH:MI:SS') >= '{$thisyear}-{$thismonth}-01 00:00:00' "
."AND to_char(b.meta_value, 'YYYY-MM-DD HH:MI:SS') <= '{$thisyear}-{$thismonth}-{$last_day} 23:59:59' "
."AND a.post_type = 'post' AND a.post_status = 'publish' AND a.ID = b.postid AND b.meta_key='Event Date'
Or, more simply:
SELECT a.ID, a.post_title, to_char(b.meta_value, 'DD') as dom "
."FROM $wpdb->posts a, $wpdb->postmeta b "
."WHERE to_char(b.meta_value, 'YYYY-MM') = '{$thisyear}-{$thismonth}' "
."AND a.post_type = 'post' AND a.post_status = 'publish' AND a.ID = b.postid AND b.meta_key='Event Date'
This assumes that
meta-value
is stored as a date. If not, you have to deal with conversion of values into dates. I'm making this assumption because you are using a function
DAYOFMONTH
.