Create Dynamic SQL Update Query in PHP and MySqli

Previously, I have described how to Create Dynamic Insert SQL script Using PHP. Let’s create a dynamic update SQL query based on data and table name.

We’ll create a method to update a row data into the MySQL table, You can use this method into the foreach method to create a dynamic update query.

Simple MySQL Update Query Example

Create build_sql_update() a php method into the PHP file or application.

if the table has 2 columns, one is the name and another column is age,Then data array should be as like below:

array('name' => 'parvez', 'age' => '26')

We will define a method as follows :

function build_sql_update($table, $data, $where)

The function takes three arguments:

  1. The first one is the table name where it will insert
  2. $data what will insert. The $data is a key value pair of array.
  3. The third one is the Where condition and so first we will separate key and value from data array. Now we will implode key and values of arrays with where condition and create a string of update sql.

Checkout other dynamic MySQL query tutorials,

Full Source Code:

/* function to build SQL UPDATE string */
function build_sql_update($table, $data, $where)
{
    $cols = array();

    foreach($data as $key=>$val) {
        $cols[] = "$key = '$val'";
    }
    $sql = "UPDATE $table SET " . implode(', ', $cols) . " WHERE $where";

    return($sql);
}

Leave a Reply

Your email address will not be published. Required fields are marked *