Dynamic MySQL query with PHP

Question

Hi guys, as the headers say, I'm looking for a way to make dynamic queries on my MySQL server. At the moment, this is the code that I use to update data on the server:

$deskAttr = json_decode($_POST["desk_attributes"]); foreach($deskAttr as $key => $value) { $sql = "UPDATE desk_attributes SET iw_standard=".$value->iw_standard.", avaya_standard=".$value->avaya_standard.", avaya_withcallid=".$value->avaya_withcallid.", avaya_withtransfer=".$value->avaya_withtransfer.", dual_screen=".$value->dual_screen.", air_conditioning=".$value->air_conditioning.", iw_obdialler=".$value->iw_obdialler." WHERE id=".$value->id; $conn->query($sql); } 

As you can see, the SQL column names are the same as the deskAttr keys. I am looking for a way to make this line a loop, so I don’t need to change this line if I have to add more columns to the MySQL table.

It will look something like this:

 $deskAttr = json_decode($_POST["desk_attributes"]); foreach($deskAttr as $key => $value) { $sql = "UPDATE desk_attributes SET"; foreach($value as $k => $v) { $sql .= " $k = $value->$k ,"; } $sql .= "WHERE id=".$value->id"; } 

How do I write the code above so that it really works? Thanks.

---

EDIT

It might be useful to know that $deskAttr is an array of objects, and the column name matches the name of the object keys.

Here is what I mean in the pseudo-code:

 foreach($object in $deskAttr) { $sql = "UPDATE table SET "; foreach($key in $object) { if($key != "id") $sql .= "$key = $object->$key, "; } $sql .= "WHERE id = $object->id; $conn->query($sql); } 

Obviously, this would add an extra comma at the end of the query before the WHERE part, but I hope you get what I'm trying to achieve.

Answer 1

You can do this with a little code change using PHP implode () .

Take an empty array, connect it to the update options.

And then if is not empty () , implode() to get the string.

Updated code:

 $sql = "UPDATE desk_attributes SET "; foreach ($deskAttr as $key => $value) { $value = mysqli_real_escape_string($link, $value); // $links is database connection string. $key = mysqli_real_escape_string($link, $key); // $links is database connection string. $updtAttrs[] = $key ." = '" . $value . "'"; } $sql .= ! empty($updtAttrs) ? implode(', ', $updtAttrs) : ''; $sql .= " WHERE id=" . $value->id;