Dynamic SQL Insert Query in PHP and MySQL

Hi guys, in this article we will learn about how to use Dynamic SQL Insert Query in PHP and MySQL. You will get bored of writing SQL insert query again and again as web developer.

This approach will reduce the time which wasted on writing insert query in web application development. Each time not need to write complete insert code to add data.

Writing a simple insert query is lengthy and makes chances of mistake in comma matching, column and value. You lose precious coding time in correction of insert query. Is it not so difficult?
Simple approach:

mysqli_query($conn, "INSERT INTO TABLENAME (col1,col2, ,…,col15,col16) VALUES ('".$val1."', '".$val2."', …,'".$val15."', '".$val16."'");

So here to be free from writing so typical query I have written a simple function for dynamic sql insert a query in PHp with Mysql.

As a programmer you were aware about function is a reusable code. So when require to insert data just call this function and pass two argument. I have taken a reference of MVC pattern to make it reusable dynamic insert code.

Sample dynamic insert query:

function insert_data( $table_name, $data )
{
..
//insert query
..
}

In these function two arguments is used.
$table_name is to store table name of database in which value will be inserted.
$data is the variable which will store what will be insert in db.
In this query $data is used as an array to store column name as key and values to be insert as value. So before proceed to insert query you show know how to set data in array.

There are two steps to create dynamic sql insert query for first time and after that just create $data array as per your need and pass to function insert_data().
Suppose table name is userinfo having 3 column namely name, age and city. Then $data array will be created as follows:

Set $data array to be inserted in database

$data = array(
“name” => “Vikash”,
“age” => “28”,
“city” => “Delhi”
);

Now create dynamic SQL insert query and pass above $data to insert in database.

Dynamic Insert query (Function)

<?php 
/*
*
* Reusable code for dynamic insert query
*
*/
function insert_data( $table_name, $data )
{
$key = array_keys($data);  //get key( column name)

$value = array_values($data);  //get values (values to be inserted)

$query ="INSERT INTO $table_name ( ". implode(',' , $key) .") VALUES('". implode("','" , $value) ."')";

return $query;
}
?>

Usage:

<?php 
//DB Config
$conn = mysqli_connect("Host Name","User","Pass", "DB Name");

$table_name = "userinfo";

$data = array(
"name" => "Vikash",
"age" => "28",
"city" => "Delhi"
);

$sql = insert_data( $table_name, $data );

if(mysqli_query($conn, $sql))
{
echo "record added successfully";
}
else
{
echo "Record not added !";
}
?>

Now whenever you need to insert record just create $data variable as per table column name and set values to be inserted, then pass $data to insert_data() function with $table_name and your data will be inserted successfully.

Conclusion:
Each time for insert just use this function and it will save efficient coding time.
Happy coding. 🙂 🙂