SELECT rows between two datetimes when the range is dynamic

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="error"><p class="wpdberror"><strong>WordPress 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 '04:00:00 AND 2021-06-02 03:59:59 ORDER BY dateAndTime DESC' at line 2]<br /><code>SELECT * 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>[]

Contents hide

Answers:

Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating