In this tutorial, you will learn how you can insert data into MySQL Database Table using the PHP Programming Language.
And here you will see an example of inserting data, where we will insert users’ first_name, last_name, and email, into MySQL DB.
Steps to insert data into MySQL database using PHP
1. Create a Database in MySQL DB.
First, we need to create a database where we will store the dummy users’ data. But, if you don’t have the localhost on your PC, you can use the XAMPP software to make your own local server.
1.1. Open the XAMPP application and start Apache and MySQL Server.
1.2. Go to the phpMyAdmin and create a new Database.
After starting the Apache and MySQL server, Open your browser and type the following URL to open the phpMyAdmin Dashboard.
http://localhost/phpmyadmin/Now on the left side of the phpMyAdmin Dashboard, click on the New to create a new database.
1.3. Enter the database name and choose the collation.
After that, enter the database name and choose the collation, and then click on the Create button.
The database name is totally up to you, but here I named the database my_test_db and the collation is utf8mb4_unicode_ci. If you wish, you can enter the same I entered.
1.4. Create the users table into the database.
Use the following SQL code to create the users table and structure of the table.
CREATE TABLE `users` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(255) NOT NULL,
`last_name` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;Go to the my_test_db » SQL tab » paste the SQL code and click the Go button.
2. PHP MySQLi procedural way to insert data.
mysqli_query($db_conn, $sql);<?php
// Database connection
$db_conn = mysqli_connect("localhost","root","","my_test_db");
// Check database connection
if (mysqli_connect_errno()){
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
// Put data into the variables and Escaping special characters.
$first_name = mysqli_real_escape_string($db_conn, 'John');
$last_name = mysqli_real_escape_string($db_conn,'Doe');
$email = mysqli_real_escape_string($db_conn, '[email protected]');
// Insert query
$sql = "INSERT INTO `users` (first_name, last_name, email) VALUES ('$first_name', '$last_name', '$email')";
// Inserting data into users table
$insert_data = mysqli_query($db_conn, $sql);
// Check if data inserted
if($insert_data){
echo "Data inserted successfully.";
}
// else data not inserted
else{
echo "Something error occurred";
}
?>3. MySQLi OOP way to insert data.
$db_conn->query($sql);<?php
// Database connection
$db_conn = new mysqli("localhost", "root", "", "my_test_db");
// Check database connection
if ($db_conn === false){
echo "Failed to connect to MySQL: " . $db_conn->connect_error;
}
// Put data into the variables
$first_name = 'John';
$last_name = 'Doe';
$email = '[email protected]';
// Insert query
$sql = "INSERT INTO `users` (first_name, last_name, email) VALUES ('$first_name', '$last_name', '$email')";
// Insert data into users table
$insert_data = $db_conn->query($sql);
// Check if data inserted
if($insert_data === true){
echo "Data inserted successfully.";
}
// else data not inserted
else{
echo "Something error occurred";
}
?>4. PDO (PHP Data Objects) way to insert data.
$db_conn->exec($sql);<?php
$servername = "localhost";
$username = "root";
$password = "";
$database_name = "my_test_db";
// CREATE DATABASE CONNECTION USING PDO METHOD
try {
$db_conn = new PDO("mysql:host=$servername;dbname=$database_name", $username, $password);
// Set the PDO error mode to exception
$db_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e)
{
echo "Failed to connect to MySQL: " . $e->getMessage();
}
// INSERT DATA INTO THE DATABASE
try {
// PUT DATA INTO THE VARIABLES
$first_name = 'John';
$last_name = 'Doe';
$email = '[email protected]';
$sql = "INSERT INTO `users` (first_name, last_name, email) VALUES ('$first_name', '$last_name', '$email')";
$db_conn->exec($sql);
echo "Data inserted successfully";
}
catch(PDOException $e)
{
echo "data not inserted";
}
?>So, these are the methods you can insert data into MySQL DB, but if you want to avoid SQL injection, you have to implement the PHP prepared statements.
