PHP/MySQL Dynamically create prepare query

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)

extract($_POST['myform'])

extract()

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.

2 Answers
2

Run Foreach on fields and concat each field with incremented number, and concat these

$i=1;$par='';
Foreach($fields as $k=>$v)
{
$par.="$k=:param$i";
$i++;
}


Concat this into query string. Then loop through fields again calling bind param

$i=1;$par='';
Foreach($fields as $k=>$v)
{
$stn->bindParam (":param$i", $fields[v]) ;
$i++;
}


Run array_values on update_fields, then build your query dynamicaly accessing keys obtained from array_values to get field values.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.