SELECT rows between two datetimes when the range is dynamic [closed]

That error itself is not specific to WordPress, which means even if you used phpMyAdmin or the MySQL command line interface to execute the same query with the dates not enclosed in quotes, you would still get the same error.

Therefore for string literals like the 2021-06-01 04:00:00 (a DATETIME literal) in the error, you need to enclose the date-time value in single (preferred) or double quotes, e.g. '2021-06-01 04:00:00'.

So your query should look like so (* wrapped for brevity):

$eventQueryResults = $wpdb->get_results( "
    SELECT * FROM dailyevents
    WHERE dateAndTime BETWEEN '$formattedDateRangeFrom' AND '$formattedDateRangeTo'
    ORDER BY dateAndTime DESC
", ARRAY_A );

And that should work, but now here’s something specific to WordPress: It is generally recommended to use wpdb::prepare() to prepare a SQL query for safe execution, particularly query having dynamic values that are direct input from users or an unknown source.

So with $wpdb->prepare(), the above code could be rewritten as:

// I know the variables store a date() value, but this is just to demonstrate
// wpdb::prepare() usage.

$query = $wpdb->prepare( "
    SELECT * FROM dailyevents
    WHERE dateAndTime BETWEEN %s AND %s
    ORDER BY dateAndTime DESC
", $formattedDateRangeFrom, $formattedDateRangeTo );

$eventQueryResults = $wpdb->get_results( $query, ARRAY_A );

And I thought you might find this helpful: https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql 🙂

Also in response to your comment: “I didn’t know you can put variable names inside quotes and still have them refer to the variable.“, you certainly can, just like when echoing something like echo "value of blah: '$blah'"; — just make sure the variables are accessible, i.e. defined and in scope.

Related Posts: