“建造"基于用户输入的 PHP 准备好的 SQL 语句 [英] "Build" a prepared SQL statement based on user-inputs with PHP

问题描述

这个问题有点难以表达，但基本上我有一个表格.这是一种帐户信息表格，人们可以在其中更改与其帐户关联的信息.问题是提交了整个表单，每个输入都是可选的 (将输入留空以保持不变).因此，例如使用此表格，您可以更改您的城市、地址和电话.但是，一个人可以选择只填写城市，按提交，然后更新该信息，所有其他信息保持不变.

This question is a bit hard to phrase, but basically I have a form. It's an account information form where a person can change the information associated with their account. The catch is that the entire form gets submitted with each input being optional (Leave input empty for no-change) . So for example with this form you can change your city, address, and phone. However a person can choose to only fill out city, press submit and just that info gets updated, all other info remains the same.

手头的代码可能更有意义，这是我用来完成这项任务的 PHP；有 4 行包含代码的注释，这些被留下是因为它们试图回答我自己的问题；他们可能有助于解释我想要实现的目标:

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 语句.

As you can probably tell I repeat that last function over and over again, and it seems silly to run that $stmt repeatedly when it could be accomplished with a single execute. From what I've learnt in my time with PHP and programming fundamentals, this is very WET code, where T is no longer twice but 6x too much. In an attempt to solve this issue I created an array, then foreach input that was set; add the column name to that array. Once finished implode the array using a comma separator. Finally create the SQL statement using that imploded array.

这会根据设置的任何输入输出动态构建的 SQL 语句.因此，如果 City 和 Address 包含数据，则语句将如下所示:

This outputs a dynamically built SQL statement based on whatever inputs are set. So if City and Address contain data the statement would look like this:

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

到目前为止，我已经完成了这项工作，具体取决于我填写的输入，SQL 语句的最终输出正是我想要的.

Now I have this working up to this point, depending on what inputs I've filled out the final output of the SQL statement is exactly what I want it to be.

...当涉及将该语句绑定到变量变量时，我有.当我不知道哪些是设置.

...I have is when it comes to binding that statement to variable-Variables. I don't know if its even possible to get ALL of the set var-vars into the parameters of this prepared_Query() function, when I have no idea which ones are set.

可能我的逻辑是有缺陷的，但是我已经非常接近让它发挥作用了，我不得不问是否有办法可以做到这一点，或者这样做是否有意义.这对我来说很有意义，但我以前用 PHP 遇到过一些非常讨厌的黑洞.我敢肯定，在做这样的事情时，有人比我更有常识.

Possibly my logic is flawed, but I was so close to getting this to work I have to ask if there is a way I can accomplish this, or if it even makes sense doing it this way. It makes sense to me, but I've gone down some pretty nasty blackholes with PHP before. I'm sure there is someone out there with a bit more common sense than I.. when it comes to doing something like this.

所以我的问题是，当每个变量都是可选的时，如何将变量变量绑定到准备好的 SQL 语句.有没有办法不必为每个输入编写单独的语句?或者更短/更快的方法来做到这一点?

So my question is, how can I bind variable Variables to a prepared SQL statement when each one is optional. Is there any way to not have to write a separate statement for each input? Or a shorter/faster way to do this?

这些 SO 问题相似，但不回答我的问题:

These SO question are similar but do not answer the question I have:

根据用户表单动态生成SQL语句输入

基于用户的 PHP 动态 SQL SELECT 语句选项

似乎还有其他一些类似这样的问题，但我找不到一个明确的例子来说明有人试图解决同样的问题.这就是让我觉得我的逻辑有某种缺陷的原因.

There seems to be a few other questions out there phrased like this, but I could not find a clear example of someone trying to solve this same sort of problem. This is what makes me think my logic is somehow flawed.

谁能指出我正确的方向，好吗?

Can anyone point me in the right direction, please?

注意:我想我已经粘贴了与问题相关的所有内容，如果有不清楚的地方请告诉我.

推荐答案

其他两个答案几乎都不错.

@sh4dowb 的答案非常适合构建 SELECT 查询，其中可能是难以自动化的不同条件.但是对于 UPDATE 查询，手动选择每个子句是没有用的，循环将是一个更简洁的解决方案.

The answer from @sh4dowb is good for constructing SELECT queries where could be different conditions that are hard to automatize. But for the UPDATE query it's no use to pick every clause by hand, a loop would be a much cleaner solution.

@ooa 演示的方法更好，但他们忘记创建实际的更新子句.此外，它正在使用这些卑鄙的变量变量.

The approach demonstrated by @ooa is better but they forgot to create the actual update clauses. Besides, it is using these despicable variable variables.

但正确的解决方案几乎就在那里.我们将使用我的 PDO 示例来完成相同的事情，如何为 UPDATE 查询创建准备好的语句:

But the right solution is almost there. We will use my example for PDO that accomplishes the same thing, How to create a prepared statement for UPDATE query:

// 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]);

看在上帝的份上，忘记变量变量.它们绝对没有用例，但它们可能会造成相当大的伤害.$_POST 数组是您数据的绝佳来源，不需要其他任何东西.

And for goodness sake, forget about variable variables. There is absolutely no use case for them, yet they can do a considerable harm. $_POST array is an excellent source for your data, nothing else is needed.

这篇关于“建造"基于用户输入的 PHP 准备好的 SQL 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！