Parse multiple conditional expressions in an SQL string

Question

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?

Tim Biegeleisen · Accepted Answer · 2021-07-29 08:01:54Z

1

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

answered Jul 29, 2021 at 8:01

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Martin · Accepted Answer · 2021-07-29 08:04:01Z

You can use explode to do this if you like. An example would be:

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

$output = array();
foreach (explode(" AND ", $source) as $value) {
  foreach (explode(" OR ", $value) as $value2) {
    $output[] = $value2;
  }
}

var_dump($output);

This will output:

array(4)
{
    [0]=> string(32) "start_time >= '2021-07-29 12:00'" 
    [1]=> string(30) "end_time <= '2021-07-30 12:00'" 
    [2]=> string(18) "customer_id = '12'" 
    [3]=> string(23) "wipe_status = 'SUCCESS'" 
}

This will work for AND or OR as requested.

Important note: explode is case-sensitive. If a non-uppercase AND or OR is used then this will not work. The better choice if there is no guaranteed case is to use the regular expression method preg_split which has already been shown in an answer.

Collectives™ on Stack Overflow

Parse multiple conditional expressions in an SQL string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related