strtotime() in SELECT query on Wordpress

Question

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

<?php

    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);

    ?>

score 1 · Accepted Answer · answered Jun 25 '16 at 12:07

1

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);

answered Jun 25 '16 at 12:07

ScaisEdge

131,976
10
91
107

I see, thanks. No more error but I the output array is empty. I wonder what's wrong with the logic – askingtoomuch Jun 25 '16 at 12:20
`AND '$wpdb->postmeta.meta_value' >= strtotime( '$thisyear-$thismonth-01 00:00:00') ` is not working. I ended up putting `$date1 = strtotime("$thisyear-$thismonth-01 00:00:00");` outside the query, then do `AND meta_value >= $date1` – askingtoomuch Jun 25 '16 at 14:31
You have posted a original question about a sintax error my answer solve this error and you change the question about the logic..this is not fair . the rule in SO is on question only by time and the if you have others then post new question .. i suggestion you of posting a new question specifically related to your problem with the logic of strtotime,.. – ScaisEdge Jun 25 '16 at 15:02
no, it's not about the logic. This syntax seems not correct `AND '$wpdb->postmeta.meta_value' >= strtotime( '$thisyear-$thismonth-01 00:00:00')` . Please try and see. – askingtoomuch Jun 25 '16 at 15:07
have you a sintax error from db ? – ScaisEdge Jun 25 '16 at 15:09
Your problem is related to the fact you are mixing date and number in php $thisyear-$thismonth (eg 2016 - 03) = 2013 .. .. so $thisyear-$thismonth-01 00:00:00 produce wrong result .. – ScaisEdge Jun 25 '16 at 15:13
but I got the correct one when I use `$date1 = strtotime("$thisyear-$thismonth-01 00:00:00");` – askingtoomuch Jun 25 '16 at 15:17
you have correct wrong .. becase so don't work .. if don't respect the correct sequence of quotes (doble quotes) you obtain a wrong sql command .. was you original problem .. – ScaisEdge Jun 25 '16 at 15:22
agree, my usage of code is wrong. but still couldn't make your query work eventhough there's no error. Any, really appreciate your time. Thanks – askingtoomuch Jun 25 '16 at 15:31

strtotime() in SELECT query on Wordpress

1 Answers1