“建造"基于用户输入的 PHP 准备好的 SQL 语句

This might make more sense with the code in hand, here is the PHP I am using to accomplish this task; There are 4 lines with comments that contain code, these were left in since they were an attempt at answering my own question; they may help explain what I'm trying to achieve:

// SQL HELPER
function prepared_Query($con, $sql, $params, $types = ""){
    $types = $types ?: str_repeat("s", count($params));
    $stmt   = $con -> prepare($sql);
    $stmt -> bind_param($types, ...$params);
    $stmt -> execute();
    return $stmt;
}

// TRIM ALL POST VARS
function trim_Val(&$val){
    $val = trim($val);
}
array_filter($_POST, 'trim_Val');

// SANITIZE ALL INPUTS 
$filter = ['filter' => FILTER_SANITIZE_STRING, 'flags' => FILTER_FLAG_ENCODE_HIGH | FILTER_FLAG_ENCODE_LOW];
$inputs = ['prov', 'city', 'addr', 'code', 'phone', 'phone_alt'];
$keys       = array_fill_keys($inputs, $filter);
$values    = filter_input_array(INPUT_POST, $keys);
unset($filter, $inputs, $keys); // cleanup

$sqlA   = [];
foreach($values as $key=>$val){
    if($val){
        $$key   = $val;
        //$sqlA[]   = "meta_".$key." = ?";
    }
}

//$sqlA = implode(", ", $sqlA);
//$sql  = "UPDATE _MAIN_meta SET ".$sqlA." WHERE meta_user = ?;";
//$stmt = prepared_Query($con, $sql, [$$key, $uid]);

if(!empty($prov)){
    $sql    = "UPDATE _MAIN_meta SET meta_prov = ? WHERE meta_user = ?;";
    $stmt   = prepared_Query($con, $sql, [$prov, $uid]);
    $stmt   -> close();
    echo "Updated Province";
}
if(!empty($city)){
    $sql    = "UPDATE _MAIN_meta SET meta_city = ? WHERE meta_user = ?;";
    $stmt   = prepared_Query($con, $sql, [$city, $uid]);
    $stmt   -> close();
    echo "Updated City";
}
if(!empty($addr)){
    $sql    = "UPDATE _MAIN_meta SET meta_addr = ? WHERE meta_user = ?;";
    $stmt   = prepared_Query($con, $sql, [$addr, $uid]);
    $stmt   -> close();
    echo "Updated Address";
}
if(!empty($code)){
    $sql    = "UPDATE _MAIN_meta SET meta_code = ? WHERE meta_user = ?;";
    $stmt   = prepared_Query($con, $sql, [$code, $uid]);
    $stmt   -> close();
    echo "Updated Postal Code";
}
if(!empty($phone)){
    $sql    = "UPDATE _MAIN_meta SET meta_phone = ? WHERE meta_user = ?;";
    $stmt   = prepared_Query($con, $sql, [$phone, $uid]);
    $stmt   -> close();
    echo "Updated Phone";
}
if(!empty($phone_alt)){
    $sql    = "UPDATE _MAIN_meta SET meta_phone_alt = ? WHERE meta_user = ?;";
    $stmt   = prepared_Query($con, $sql, [$phone_alt, $uid]);
    $stmt   -> close();
    echo "Updated Alt. Phone";
}
$con -> close();

您可能会告诉我，我一遍又一遍地重复最后一个函数，而重复运行该 $stmt 似乎很愚蠢，因为它可以通过一次执行来完成.根据我在 PHP 和编程基础方面所学的知识，这是非常 WET 的代码，其中 T 不再是两倍，而是 6 倍.为了解决这个问题，我创建了一个数组，然后设置了 foreach 输入；将列名添加到该数组.一旦完成 implode 数组使用逗号分隔符.最后使用该内爆数组创建 SQL 语句.

$sql = "UPDATE _MAIN_meta SET meta_city = ?, meta_addr = ? WHERE meta_user = ?;";

// the list of allowed field names
$allowed = ['prov', 'city', 'addr', 'code', 'phone', 'phone_alt'];

// initialize an array with values:
$params = [];

// initialize a string with `fieldname` = ? pairs
$setStr = "";

// loop over source data array
foreach ($allowed as $key)
{
    if (!empty($_POST[$key]) && $key != "id")
    {
        $setStr .= "`$key` = ?,";
        $params[] = $_POST[$key];
    }
}
$setStr = rtrim($setStr, ",");

$params[] = $_POST['id'];
prepared_query("UPDATE _MAIN_meta SET $setStr WHERE main_user = ?", [$params]);