askingtoomuch askingtoomuch - 1 year ago 144
MySQL Question

strtotime() in SELECT query on Wordpress

Using get_calendar() function in Wordpress as a reference, I would like to show on the calendar the event dates (meta_value = 'event_date') of my custom post type (events).

This is how I get all event dates in a month. I couldn't make the strtotime() work. Am I doing it right?

Parse error: syntax error, unexpected '$thisyear' (T_VARIABLE) in
/.../wp-content/plugins/exec-php/includes/runtime.php(42) : eval()'d
code on line 18


global $wpdb, $posts, $postmeta;

// To simplify codes
$thisyear = 2016;
$thismonth = 06;
$last_day = 30;
$cpt = 'events';

// Get days with events
$dayswithevents = $wpdb->get_results("SELECT *
FROM $wpdb->posts, $wpdb->postmeta
WHERE $wpdb->post.ID = $wpdb->postmeta.post_id
AND $wpdb->post_type = '$cpt'
AND $wpdb->post_status = 'publish'
AND $wpdb->postmeta.meta_key = '_event_date';
AND $wpdb->postmeta.meta_value >= 'strtotime("$thisyear-$thismonth-01 00:00:00")'
AND $wpdb->postmeta.meta_value <= 'strtotime("$thisyear-$thismonth-{$last_day} 23:59:59")'", ARRAY_N);


Answer Source

You are using quote in the wrong way

  $dayswithevents = $wpdb->get_results("SELECT *
    FROM '$wpdb->posts', '$wpdb->postmeta' 
    WHERE '$wpdb->post.ID' = '$wpdb->postmeta.post_id'
    AND  '$wpdb->post_type' = '$cpt' 
    AND  '$wpdb->post_status' = `publish`
    AND  '$wpdb->postmeta.meta_key' = `_eventpro_event_date`; 
    AND  '$wpdb->postmeta.meta_value' >= strtotime( '$thisyear-$thismonth-01 00:00:00') 
    AND   '$wpdb->postmeta.meta_value'  <= strtotime('$thisyear-$thismonth-{$last_day} 23:59:59')", ARRAY_N);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download