[SOLVED] Parse multiple conditional expressions in an SQL string

Parse multiple conditional expressions in an SQL string

I have a table containing user access filters. These filters contain a certain MySQL range that the user can search the data within, for instance:

start_time >= '2021-07-29 12:00' AND end_time <= '2021-07-30 12:00' AND customer_id = '12' AND wipe_status = 'SUCCESS'

All of this is stored within one string. I am looking to seperate the string so that I will get the following in this case:

$end_time = end_time <= '2021-07-30 12:00'
$customer_id = customer_id = '12'
$wipe_status = wipe_status = 'SUCCESS'

Trouble is the length of these can be variable so I am not sure how to use substring to seperate these. At times the filters will include and "OR" as well so I can't simply seperate by AND. Do you have any ideas as to how I would go about doing this?

Solution

I prefer creating a dictionary whose keys correspond to the columns in the WHERE clause. We can do a regex split on AND or OR, and then iterate the terms to populate the dictionary.

$input = "start_time >= '2021-07-29 12:00' AND end_time <= '2021-07-30 12:00' AND customer_id = '12' AND wipe_status = 'SUCCESS'";
$parts = preg_split("/\s+(?:AND|OR)\s+/", $input);
$vals = array();
foreach ($parts as $part) {
    $vals[explode(" ", $part)[0]] = $part;
}
print_r($vals);

This prints:

Array
(
    [start_time] => start_time >= '2021-07-29 12:00'
    [end_time] => end_time <= '2021-07-30 12:00'
    [customer_id] => customer_id = '12'
    [wipe_status] => wipe_status = 'SUCCESS'
)