Hello Developer, In this tutorial, You will learn some easy methods to insert data into the database using PHP & MYSQL. Event You will learn to store new records into the MySQL database using MySQLi procedure, MySQLi Object-oriented, PDO & prepared statement with a new concept & an example.
Steps to Insert Data into MySQL Database with PHP
In this step, you will learn to insert form data into the MySQL database dynamically with an advanced coding concept that will help you to improve your coding skills for writing smart & standard code in PHP.
Before getting started, make sure that the local server (xamp/wamp) must be installed in your system and start it if it is not being started already.
Learn Also –
PHP Contact Form With Sending Email
PHP Image Gallery With MySQL Database
You can test yourself to insert data with the following folder structure –
codingstatus/
|__database.php
|__form.php
|__developers.php
|
1. Create MySQL Table
First of all, you have to create a database with the name of codingstatus. After that, create a database with the name of developers in PHPMyAdmin to store form data.
developers table should have 10 columns like id, fullName, gender, email, mobile, city, state, created_at & updated_at. you can get details of columns from the following query.
CREATE TABLE `developers` ( `id` int(10) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, `fullName` varchar(50) DEFAULT NULL, `gender` varchar(10) DEFAULT NULL, `email` varchar(50) DEFAULT NULL, `mobile` varchar(20) DEFAULT NULL, `address` varchar(100) DEFAULT NULL, `city` varchar(50) DEFAULT NULL, `state` varchar(50) DEFAULT NULL, `created_at` timestamp(5) DEFAULT NULL, `updated_at` datetime(5) DEFAULT NULL, )
2. Connect PHP to MySQL Database
You can use the following database connection query to connect PHP to the MySQL database
- $hostName – It contains host name
- $userName – It contains database username
- $password – It contains database password
- $databaseName – It contains database name.
File Name – database.php
<?php
$hostName = "localhost";
$userName = "root";
$password = "";
$databaseName = "codingstatus";
$conn = new mysqli($hostName, $userName, $password, $databaseName);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>3. Create an HTML Form
I have created this form using Bootstrap 4. SO, You have to include its CDN –
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
Also, include the developers.php file that contains the source code with a custom function to insert data and its output stores with a variable $result. So, We have to print it on this page.
<?php
include("developers.php");
?>Just Copy only the part of HTML form and paste it within the <body></body> element. But before it, You should know about its input fields –
- Input Field Name should be same as a column name of a table. for example – if the input field name is fullName then column name also be fullName.
- No need to create an input field for the id column. It will be created automatically while new record will be created.
- Also, you need not create input fields for the created_at & updated_at columns. Because created_at type is a timestamp and it will automatically store the current time while a new record will be created.
- No need to create a column for submitting input field. It will be used to execute the PHP code to insert data
File Name – form.php
<?php
include("developers.php");
?>
<!DOCTYPE html>
<html lang="en">
<head>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-sm-4">
<h3 class="text-primary"> HTML Form to Insert Data</h3>
<p><?php echo !empty($result)? $result:''; ?></p>
<!--=================HTML Form=======================-->
<form method="post" >
<div class="form-group">
<input type="text" class="form-control" placeholder="Full Name" name="fullName">
</div>
<div class="form-group">
<div class="form-check-inline">
<input type="radio" class="form-check-input" name="gender" value="male">Male
</div>
<div class="form-check-inline">
<input type="radio" class="form-check-input" name="gender" value="female">Female
</div>
</div>
<div class="form-group">
<input type="email" class="form-control" placeholder="Email Address" name="email">
</div>
<div class="form-group">
<input type="text" class="form-control" placeholder="Mobile Number" name="mobile">
</div>
<div class="form-group">
<textarea class="form-control" name="address" placeholder="Address"></textarea>
</div>
<div class="form-group">
<input type="text" class="form-control" placeholder="City" name="city">
</div>
<div class="form-group">
<input type="text" class="form-control" placeholder="State" name="state">
</div>
<button type="submit" name="save" class="btn btn-primary">Save</button>
</form>
<!--======================== HTML Form============================ -->
</div>
<div class="col-sm-8">
</div>
</div>
</div>
</body>
</html>
4. Insert Data using PHP
First of all, you have to include a database.php file to stabilize the connection between PHP & MySQL.
include("database.php");If you declare extract($_POST) then you can get the value of an input filed by making its name a variable with $ sign. In this way, you will not have to declare $_POST with input filed name again and again.
For Example –
| without declaring extract($_POST) | with declaring extract($_POST); |
| $_POST[‘fullName’] | $fullName |
| $_POST[‘gender’] | $gender |
| $_POST[’email’] | |
| $_POST[‘mobile’] | $mobile |
| $_POST[‘address’] | $address |
| $_POST[‘city’] | $city |
| $_POST[‘state’] | $state |
insert_data() – This function accepts three parameters like $db, $tableName & $inputData and It contains some line of code to insert input data into the MySQL Database. So, It will execute when the form is submitted and returns an message.
validate() – This function is created to protect from the iligal data that may be inserted by the Web attackers to distroy the database. So, I have pass all the input field value to it for converting input value into a legal data.
File Name – developers.php
<?php
include("database.php");
extract($_POST);
if(isset($save)){
$inputData = [
'fullName' => validate($fullName) ?? "",
'gender' => validate($gender) ?? "",
'email' => validate($email) ?? "",
'mobile' => validate($mobile) ?? "",
'address' => validate($address) ?? "",
'city' => validate($city) ?? "",
'state' => validate($state)?? ""
];
$tableName= "developers";
$db = $conn;
$result= insert_data($db, $tableName, $inputData);
}
function insert_data($db, $tableName, $inputData){
$data = implode(" ",$inputData);
if(empty($db)){
$msg= "Database connection error";
}elseif(empty($tableName)){
$msg= "Table Name is empty";
}elseif(trim( $data ) == ""){
$msg= "Empty Data not allowed to insert";
}else{
$query = "INSERT INTO ".$tableName." (";
$query .= implode(",", array_keys($inputData)) . ') VALUES (';
$query .= "'" . implode("','", array_values($inputData)) . "')";
$execute= $db->query($query);
if($execute=== true){
$msg= "Data was inserted successfully";
}else{
$msg= mysqli_error($db);
}
}
return $msg;
}
function validate($value) {
$value = trim($value);
$value = stripslashes($value);
$value = htmlspecialchars($value);
return $value;
}
?>5. Test Yourself to insert data
After Implementing previous steps, You can test it yourself to open the following URL in your web browser
https://localhost/codingstatus/form.php
More Methods to Insert Form Data into MySQL Database with PHP
Now, You should know more methods to insert form data into the MySQL Database from the next step. After learning those methods, you can use one of them in your project.
From the next step, I have shared only the source code without a custom function. So that you can directly paste it into your project where you need to implement it.
Insert Data using MySQLi Procedure
Connect Database with MySQLi Procedure
$hostName = "localhost";
$userName = "root";
$password = "";
$databaseName = "codingstatus";
$conn = mysqli_connect($hostName, $userName, $password, $databaseName);
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}Insert data with MySQLi Procedure
if(isset($_POST['save'])){
$fullName = $_POST['fullName'];
$gender = $_POST['gender'];
$email = $_POST['email'];
$mobile = $_POST['mobile'];
$address = $_POST['address'];
$city = $_POST['city'];
$state = $_POST['state'];
$query = "INSERT INTO developers (fullName, gender, email, mobile, address, city, state)
VALUES ('$fullName','$gender','$email', '$mobile', '$address', '$city', '$state')";
$execute = mysqli_query($conn, $query);
if($execute=== true){
$msg= "Data was inserted successfully";
}else{
$msg= mysqli_error($conn);
}
echo $msg;
}Insert Data Using MySQLi Object Oriented
Connect database with MySQLi Object Oriented
$hostName = "localhost";
$userName = "root";
$password = "";
$databaseName = "codingstatus";
$conn = new mysqli($hostName, $userName, $password, $databaseName);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}Insert data with MySQLi Object Oriented
if(isset($_POST['save'])){
$fullName = $_POST['fullName'];
$gender = $_POST['gender'];
$email = $_POST['email'];
$mobile = $_POST['mobile'];
$address = $_POST['address'];
$city = $_POST['city'];
$state = $_POST['state'];
$query = "INSERT INTO developers (fullName, gender, email, mobile, address, city, state)
VALUES ('$fullName','$gender','$email', '$mobile', '$address', '$city', '$state')";
$execute = mysqli_query($conn, $query);
if($execute=== true){
$msg= "Data was inserted successfully";
}else{
$msg= mysqli_error($conn);
}
echo $msg;
}
Insert Data Using PDO
Connect Database with PDO
$hostName = "localhost";
$userName = "root";
$password = "";
$databaseName = "codingstatus";
$conn = new PDO("mysql:host=$hostName;databaseName=$databaseName", $username, $password);
if (!$conn) {
die("Connection failed");
}Insert Data with PDO
$hostName = "localhost";
$userName = "root";
$password = "";
$databaseName = "codingstatus";
$conn = new PDO("mysql:host=$hostName;databaseName=$databaseName", $username, $password);
if (!$conn) {
die("Connection failed");
}
if(isset($_POST['save'])){
$fullName = $_POST['fullName'];
$gender = $_POST['gender'];
$email = $_POST['email'];
$mobile = $_POST['mobile'];
$address = $_POST['address'];
$city = $_POST['city'];
$state = $_POST['state'];
try {
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$query = "INSERT INTO developers (fullName, gender, email, mobile, address, city, state)
VALUES ('$fullName','$gender','$email', '$mobile', '$address', '$city', '$state')";
// use exec() because no results are returned
$conn->exec($query);
echo "Data was inserted successfully";
} catch(PDOException $e) {
echo $e->getMessage();
}
}Insert Data Using Prepared Statement
Insert data using Prepared Statement with MySQLi –
if(isset($_POST['save'])){
$fullName = $_POST['fullName'];
$gender = $_POST['gender'];
$email = $_POST['email'];
$mobile = $_POST['mobile'];
$address = $_POST['address'];
$city = $_POST['city'];
$state = $_POST['state'];
$query = "INSERT INTO developers (fullName, gender, email, mobile, address, city, state)
VALUES ('?','?','?', '?', '?', '?', '?')";
$prepared=$conn->prepare($query);
$prepared->bind_param("sssssss", $fullName, $gender, $email, $mobile, $address, $city, $state);
$execute = $prepared->execute();
if($execute=== true){
$msg= "Data was inserted successfully";
}else{
$msg= mysqli_error($conn);
}
echo $msg;
}Insert data using Prepared Statement with PDO –
if(isset($_POST['save'])){
$fullName = $_POST['fullName'];
$gender = $_POST['gender'];
$email = $_POST['email'];
$mobile = $_POST['mobile'];
$address = $_POST['address'];
$city = $_POST['city'];
$state = $_POST['state'];
try {
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$query = "INSERT INTO developers (fullName, gender, email, mobile, address, city, state)
VALUES (:fullName,:gender, :$email, :$mobile, :$address, :$city, :$state)";
$prepared= $conn->prepare($query);
$prepared->bindParam(':fullName', $fullName);
$prepared->bindParam(':gender', $gender);
$prepared->bindParam(':email', $email);
$prepared->bindParam(':mobile', $mobile);
$prepared->bindParam(':address', $address);
$prepared->bindParam(':city', $city);
// use exec() because no results are returned
$prepared->execute();
echo "Data was inserted successfully";
} catch(PDOException $e) {
echo $e->getMessage();
}
}