SELECT rows between two datetimes when the range is dynamic

Question

SELECT rows between two datetimes when the range is dynamic

Antti

2021年6月2日 09:26

I'm trying to select rows that fall into a certain date range from my database table. The range is determined dynamically by the user.

In my database I have a column called dateAndTime. It has dates stored into it in this format:

2021-06-01 19:37:00

I'm trying to select the rows with this:

function getEventsByDateRange(){

    $eventType = ($_POST[event]);
    $dateRangeFrom = ($_POST[dateRangeFrom]);
    $dateRangeTo = ($_POST[dateRangeTo]);

    $formattedDateRangeFrom = date(Y-m-d H:i:s, strtotime($dateRangeFrom));
    $formattedDateRangeTo = date(Y-m-d H:i:s, strtotime($dateRangeTo));

    global $wpdb;

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

    $jsonEncoded = json_encode($eventQueryResults, JSON_UNESCAPED_UNICODE);

    echo $jsonEncoded;

    exit();

}

But I keep getting this error:

div id=errorp class=wpdberrorstrongWordPress database error:/strong [You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near #039;04:00:00 AND 2021-06-02 03:59:59 ORDER BY dateAndTime DESC#039; at line 2]br /codeSELECT * FROM dailyevents 
    WHERE dateAndTime BETWEEN 2021-06-01 04:00:00 AND 2021-06-02 03:59:59 ORDER BY dateAndTime DESC/code/p/div[]

Topic select date wpdb database Wordpress

Category Web

Sally CJ · Accepted Answer · 2021年6月2日 09:26

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.

SELECT rows between two datetimes when the range is dynamic

About