PHP/MySQL Dynamically create prepare query

Question

I am trying to pass a list of input field's ID that have been modified so I can create a UPDATE query in MySQL only for the modified data.

Here is what I have so far..

In jQuery I have the following code that makes a JSON list of all the id's that have had changed input data. The data is added to a hidden input field.

updatedFields = [];
$('form :input').change(function(){
    attr_name = $(this).attr('id');
    updatedFields.push({attr_name})
    $("#update_fields").val(JSON.stringify(updatedFields))
});

Once that is POSTed, I have an array similar to

Array
(
    [myform] => Array
        (
            [update_fields] => [{"attr_name":"field1"},{"attr_name":"field2"}]
            [field1] => field1val
            [field2] => myfield2val
            [id] => 5
            .....other irrelevant/nonmodified fields.......
        )
)

Then I do extract($_POST['myform']) to make each field name be its own variable. (Yes I know using extract() for POST data is not safe, just doing it for testing purposes)

What is the best way to dynamically create an UPDATE query given that I want to modify the values of field1 and field2.

Here is what I would like to be created:

$query = $db->prepare("UPDATE `table_name` SET `field1` = ?, `field2` = ? WHERE `id` = ?");
$query->bind_param('ssi', $field1, $field2, $id)
$query->execute();

The problem with this solution is that I do not know how to dynamically create the prepare and bind_param query.

Edit: I know this is a cluster**** of a solution. It's what I could come up with that almost worked.

Answer 1

If you're using PHP5.6 or later, you could use argument unpacking to deal with the bind_param which is the hardest part of the problem. Try something like this:

$update_fields = json_decode($_POST['myform']['update_fields'], true);
$sql_fields = array();
$bindtypes = "";
$bindings = array();
foreach (array_column($update_fields, 'attr_name') as $field) {
    $sql_fields[] .= "`$field` = ?";
    $bindtypes .= "s";
    $bindings[] = $_POST['myform'][$field];
}
$sql = "UPDATE `table_name` SET " . implode(',', $sql_fields) . " WHERE `id` = ?";
$bindtypes .= "i";
array_unshift($bindings, $bindtypes);
$bindings[] = $_POST['myform']['id'];
$query = $db->prepare($sql);
$query->bind_param(...$bindings);
$query->execute();