“构建"基于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语句.因此，如果城市"和地址"包含数据，则该语句将如下所示:

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.

...我要说的是将该语句绑定到变量变量.我不知道当我不知道要设置哪些变量时，是否甚至有可能将设置的var-vars的 ALL 放入此prepared_Query()函数的参数中.

...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?

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

推荐答案

其他两个答案都差不多.

Both other answers are almost good.

@ 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屋！