如何仅使用非空用户输入字段更新MySQL数据库

Question

我有一个数据库，在那里我试图根据某些用户输入表单进行更新。我正在使用以下代码更新MySQL数据库。

$query = "UPDATE supervisorupdate SET shift1PinCount =?, shift2PinCount =?, shift3PinCount =?, shift4PinCount =? WHERE Code=?";

$stmt = mysqli_stmt_init($conn);

if(!mysqli_stmt_prepare($stmt, $query)){
   echo "updating supervisorupdate table when click save button SQL statement failed";
}else{
   mysqli_stmt_bind_param($stmt,"iiiis", $shift1PinCount, $shift2PinCount, $shift3PinCount, $shift4PinCount, $Code);
   mysqli_stmt_execute($stmt);
   $result = mysqli_stmt_get_result($stmt);
}

在这里，$shift1PinCount，$shift2PinCount，$shift3PinCount，$shift4PinCount是我从用户输入表单中得到的一些$_POST变量

我需要什么

如果用户没有在$shift1PinCount字段中输入任何内容，则查询不应该将任何内容更新到MySQL数据库的shift1PinCount列中。

我的问题

使用我的查询，它总是更新所有的MySQL列，不管是用户键入了什么还是在用户输入表单中将其保留为空。有人知道怎么做吗？

Answer 1

试图将if条件放在像这样的post数据上

<?php
     if(isset($_POST["shift1"]))
{

    $query = "
        Update product SET shift1 = ?,
    ";
    if(isset($_POST["shift2"]))
    {
        $query .= "
         shift2 = ? ,
        ";
    }
    if(isset($_POST["shift3"]))
    {
        $query .= "
         shift3 = ? ,
        ";
    }if(isset($_POST["shift4"]))
    {
        $query .= "
         shift4 = ? ,
        ";
    }
    $query .=" Where Code=?";
    $stmt = mysqli_stmt_init($conn);

    if(!mysqli_stmt_prepare($stmt, $query)){
       echo "updating supervisorupdate table when click save button SQL statement failed";
    }else{
       mysqli_stmt_bind_param($stmt,"iiiis", $_POST["shift1"], $_POST["shift1"], $_POST["shift1"], $_POST["shift1"], $Code);
       mysqli_stmt_execute($stmt);
       $result = mysqli_stmt_get_result($stmt);
    }
}
?>

Answer 2

在尝试了一些方法之后，我可以通过以下方法完成它。但我相信会有更好的方法。如果有人知道更好的方法，请发邮件

​

if(isset($_POST['shift1PinCount']) && !empty($_POST['shift1PinCount'])){
    pinCountUpdateQuery("shift1PinCount", $_POST['shift1PinCount'], $Code);
}

if(isset($_POST['shift2PinCount']) && !empty($_POST['shift2PinCount'])){
    pinCountUpdateQuery("shift2PinCount", $_POST['shift2PinCount'], $Code);
}

if(isset($_POST['shift3PinCount']) && !empty($_POST['shift3PinCount'])){
    pinCountUpdateQuery("shift3PinCount", $_POST['shift3PinCount'], $Code);
}

if(isset($_POST['shift4PinCount']) && !empty($_POST['shift4PinCount'])){
    pinCountUpdateQuery("shift4PinCount", $_POST['shift4PinCount'], $Code);
}

​

然后使用以下函数

​

function pinCountUpdateQuery($shiftPinCountDb, $shiftPinCountUserForm, $Code){
  global $conn;
    $query = "UPDATE supervisorupdate SET ".$shiftPinCountDb." =? WHERE Code=?";
        $stmt = mysqli_stmt_init($conn);
        if(!mysqli_stmt_prepare($stmt, $query)){
            echo "updating supervisorupdate table when click save button SQL statement failed";
        }else{
            mysqli_stmt_bind_param($stmt,"is", $shiftPinCountUserForm, $Code);
            mysqli_stmt_execute($stmt);
            $result = mysqli_stmt_get_result($stmt);
        }
}

​

Answer 3

伪码：

param1 = req get param1
param2 = req get param2
.
.
paramN = req get paramN

$query = "UPDATE supervisorupdate SET "
bool can_be_updated = false
# now add prepare the rest of sql for inputs
for range of params count to be updated
    if current param is valid then
        if not can_be_updated then
            can_be_updated = true
        end if
        if current param number is > 1 < param count then
            query += ", "
        end if
        query += 'current param name = ?'
    end if
end loop

# now complete sql
if can_be_updated then 
    $query += ' WHERE Code=?'
    .
    .
    # bind sql with valid param inputs like one of the following
    # option 1: if only 2 are valid
    call rel_mysql_func_to_bind_params($stmt,"iiiis", valid param 1, valid param M, $Code);
    # or
    # option N: if all are valid
    call rel_mysql_func_to_bind_params($stmt,"iiiis", $shift1PinCount, $shift2PinCount, $shift3PinCount, $shift4PinCount, $Code);

    # now execute your stmt
    relevant_mysql_func_to_execute($stmt);
else
    # what else you want, perform here
end if

Other

请参阅Java @的类似要求。

How to perform a mysql statement with dynamic count of where clauses in java.sql