如何只更新PHP表单中给出的元组的一个属性？

Question

I have created an update form in PHP that takes the attributes of a tuple. The following query updates a tuple of the table based on the input:

<?php
    error_reporting(E_ALL); ini_set('display_errors', 1);

    if(isset($_POST['submit'])) {

        $sql = "UPDATE member SET MFirst='".$_POST["MFirst"]."', MLast='".$_POST["MLast"]."',Street='".$_POST["Street"]."'
            ,number='".$_POST["number"]."',postalCode='".$_POST["postalCode"]."',Mbirthdate='".$_POST["Mbirthdate"]."' 
            WHERE memberID='".$_POST["memberID"]."'";

        $result = mysqli_query($conn,$sql);
    }
?>   

However, if not all attributes are submitted the update does not happen. How should I write the query in order to change an attribute of a tuple even if not all the attributes of the form are completed.

The form is the following:

<FORM METHOD="post" ACTION="update.php">
    <div class="input-group">
        <label>Member Id</label>
        <INPUT TYPE="text" name="memberID" SIZE="30">
    </div>
    <div class="input-group">
        <label>First Name</label>
        <INPUT TYPE="text" name="MFirst" SIZE="30">  
    </div> 
    <div class="input-group">
        <label>Last Name</label>
        <INPUT TYPE="text" name="MLast" SIZE="30">
    </div>   
    <div class="input-group">
        <label>Street Name</label>
        <INPUT TYPE="text" name="Street" SIZE="30">     
    </div>
    <div class="input-group">
        <label>Street Number</label>
        <INPUT TYPE="number" name="number" min=0 SIZE="30">
    </div>  
    <div class="input-group">
        <label>Postal Code</label>
        <INPUT TYPE="number" name="postalCode" min=0 SIZE="30"> 
    </div>
    <div class="input-group">
        <label>Birth Day</label>
        <INPUT TYPE="date" name="Mbirthdate" SIZE="30">
    </div>
    <button class="btn" TYPE="submit" name="submit">Submit Info </button>
    <button class="btn" TYPE="reset" name="Reset">Reset </button>
</FORM>

Answer 1

You can omit any columns that are either empty or not submitted by iterating over $_POST and adding them to the "SET" list accordingly. However, this is dangerous as you should never trust any inputs received from the internet. (See SQL Injection)

The following code does what you want whilst using mysqli Prepared Statements, which mitigates SQL Injection risk. It also verifies that $_POST keys are valid table column names.

Explanations are in comments:

<?php
if (isset($_POST['submit'])) {

// Assume $_POST is in this format:
// $_POST = [ "MFirst" => "John", "MLast" => "Doe", "Street" => "Infinity Loop", "number" => 1, "postalCode" => 95014, "Mbirthdate" => "01-01-1990", "memberId" => "abcde-1", "submit" => 1];

// remove $_POST elements not part of SET list
    $_POST = array_filter($_POST); //remove any empty elements
    unset($_POST["submit"]);
    $member_id = $_POST["memberId"];
    unset($_POST["memberId"]);

//stop now if form is empty
    if (count($_POST) == 0) exit(); 

// array of valid columns as keys, and their types as values (s = string, i = integer)
    $member_col_types = [ "MFirst"     => "s",
                          "MLast"      => "s",
                          "Street"     => "s",
                          "number"     => "i",
                          "postalCode" => "i",
                          "Mbirthdate" => "s",
                          "memberId"   => "s"
    ];

    $key_val = [];
    $types   = "";
    $columns = array_keys($member_col_types);
    $values  = [];

    foreach ($_POST as $k => $v) {
// ensure all inputs are valid table columns, otherwise exit
        if (in_array($k, $columns) == false) {
            exit("");
        }
// dynamically adding only valid $_POST
        $key_val[] = "$k=?"; // [ "MFirst=?","MLast=?",...]
// grabs values to be inserted into ?
        $values[] = $v; //["John","Doe","Infinity Loop",1,95014,"01-01-1990"]
// grabs the variable types for binding values above to types like string, integer
        $types .= $member_col_types[ $k ]; // "sssiis"
    }

    $list = implode(", ", $key_val);

// add binding and value for memberId (part of WHERE) not processed in loop above
    $values[] = $member_id; //["John","Doe","Infinity Loop",1,95014,"01-01-1990","abcde-1"]
    $types    .= "s"; // "sssiiss"

    $sql = "UPDATE member SET $list WHERE memberId=?"; 
    //UPDATE member SET MFirst=?, MLast=?, Street=?, number=?, postalCode=?, Mbirthdate=? WHERE memberId=?

// prepare the query (assuming mysqli connection $conn)
    $stmt = $conn->prepare($sql) or die($conn->error);

// bind the values to their types
    $stmt->bind_param($types, ...$values) or die($conn->error);
    $stmt->execute() or die($stmt->error);

}