有条件地将复选框值添加到PHP MySQLi查询中的最佳方法是什么，以及它们相关的OR？

Question

I am looking for some suggestions on how I can turn this messy code into something more cleaner, and efficient.

Apologies it's not the cleanest code, but I hope you can understand it. I have 4x GET parameters, (P, O, N, C).

These GET parameters are results of checkboxes, and will have either value ON, or wont be passed in the URL string.

If the checkbox is ON, I need to append to the SQL query, along with an OR after it - if the succeeding GET parameter is also ON (to avoid an unnessicary OR at the end).

I'm guessing the answer to this would be storing the GET parameters into an array, and a while loop for each one?

$select = "SELECT * FROM cif_schedules WHERE tiploc_code = '$t'";
$select .= " AND (";
if($_GET['w'] == "on"){$select .= "cif_schedules.CIF_stp_indicator = 'P' ";}
if($_GET['o'] == "on" && ($_GET['w'] == "on")){$select .=" or ";};
if($_GET['o'] == "on"){$select .= "cif_schedules.CIF_stp_indicator = 'O' ";}
if($_GET['s'] == "on" && ($_GET['o'] == "on" || $_GET['w'] == "on")){$select .=" or ";};
if($_GET['s'] == "on"){$select .= "cif_schedules.CIF_stp_indicator = 'N' ";}
if($_GET['c'] == "on" && ($_GET['s'] == "on" || $_GET['o'] == "on" || $_GET['w'] == "on")){$select .=" or ";};
if($_GET['c'] == "on"){$select .= "cif_schedules.CIF_stp_indicator = 'C' ";}
$select .= ")";
$select .= " AND deleted >= '$maxdate' AND created <= '$maxdate'";

Im sanitising the user's input so don't worry about security etc. Thanks

Answer 1

I'd make a conversion array, which translate the input value to the one in the DB. Walk through this array and build the query.

Something like this:

// FormValue => DbValue
$conversions = ['w' => 'P', 'o' => 'O', 's' => 'N', 'c' => 'C'];
$ors = [];

foreach($conversions as $input => $value) {
    // Check if $input is in $_GET and if its value is 'on'
    if (isset($_GET[$input]) && $_GET[$input] == 'on') {
        // Push in $ors
        $ors[] = "cif_schedules.CIF_stp_indicator = '{$value}'";
    }
}

So you'll get all the OR in the array $ors that you'll just have to implode and add it to $select if needed:

$select = "SELECT * FROM cif_schedules WHERE tiploc_code = '$t'";
// Check if there are some OR
if (!empty($ors)) {
    // Add the ORs to the query
    $select.= ' AND ('.implode(' OR ', $ors).')';
}
$select .= " AND deleted >= '$maxdate' AND created <= '$maxdate'";