Create dynamic SQL insert query in php and mysql
How to create dynamic insert query in php mysql
In this article, you will be learning about how to create a dynamic insert query in php mysqli.
Dynamic Insert Query in PHP MySQLi
function insertQuery($table, $data)
{
$key = implode(',', array_keys($data));
$value = "'" . implode( "','", array_values($data)) ."'";
$sqlQuery = "INSERT INTO $table ($key) VALUES ($value)";
}
So guys now, lets pass the "table columns names" and its "data" where we will create a Array variable named $data and lets give key and value.
So, this "key" will take it as mysql table columns and "value" will take as data to insert in that columns. lets see an example of it, given below:
$data = [
'fullname' => 'Funda of Web IT',
'email' => 'fundaofwebit@gmail.com',
'phone' => '888999XXXXX',
'course' => 'BCA',
];
insertQuery('students', $data);
Now, Lets take an Live example of students to insert the records into database by dynamic mysql insert query.
Step 1: Create a table named as students with below fields:
CREATE TABLE IF NOT EXISTS `students` (
`id` int(11) NOT NULL,
`fullname` varchar(191) NOT NULL,
`email` varchar(191) NOT NULL,
`phone` varchar(20) NOT NULL,
`course` varchar(20) NOT NULL,
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
Step 2: Example for creating dynamic insert query with data in php and mysql as given below in code.php:
Example with MySQL Procedural.
<?php
$conn = mysqli_connect('localhost','username','password','database_name');
function insertQuery($table, $data, $conn)
{
$key = implode(',', array_keys($data));
$value = "'" . implode( "','", array_values($data)) ."'";
$sqlQuery = "INSERT INTO $table ($key) VALUES ($value)";
$result = mysqli_query($conn, $sqlQuery);
if($result){
return true;
}else{
return false;
}
}
if(isset($_POST['save_student_btn']))
{
$data = [
'fullname' => $_POST['fullname'],
'email' => $_POST['email'],
'phone' => $_POST['phone'],
'course' => $_POST['course'],
];
$studentData = insertQuery('students', $data, $conn);
if($studentData){
echo "Inserted";
}else{
echo "something went wrong";
}
}
?>
Step 3: Add the Student Form as given below in file student-add.php:
<div class="container">
<div class="row">
<div class="col-md-6 mt-5">
<form action="code.php" method="POST">
<div class="mb-3">
<label for="">Full Name</label>
<input type="text" name="fullname" required class="form-control" />
</div>
<div class="mb-3">
<label for="">Email ID</label>
<input type="text" name="email" required class="form-control" />
</div>
<div class="mb-3">
<label for="">Course</label>
<input type="text" name="course" required class="form-control" />
</div>
<div class="mb-3">
<label for="">Phone No</label>
<input type="text" name="phone" required class="form-control" />
</div>
<div class="mb-3">
<button type="submit" name="save_student_btn" class="btn btn-primary">Save Student</button>
</div>
</form>
</div>
</div>
</div>
Thank you.