PHP Insert Data Into MySQL Database In Multiple Ways

Hi, this tutorial about how to Insert Data Into MySQL Database using PHP.

In this PHP insert data into MySQL database tutorial, we will be inserting data into MySQL database in multiple ways.

In the PHP there are so many ways to insert data into MySQL Database. Below, a list of ways to insert data into MySQL DB using PHP.

List of PHP Insert Data methods

• PHP MySQLi procedural method
• PHP MySQLi OOP(Object Oriented Programming) method
• PDO(PHP Data Objects) method

---

But, before we start to insert data into the MySQL database using above all of the ways –

We will first create our database, where we will store our data.

---

MySQL Database and table creation

• Database Name: As you wish
• Table Name: users

After creating the Database use the below SQL code to create users table and the structure of the users table.

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`);

ALTER TABLE `users`
  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT;

If you are using the phpMyAdmin, then select the newly created Database and then go to the SQL tab above.

After that, paste the above SQL code in the Run SQL query box and then click on the go button.

---

Before start, if you want to know how to make database connection with MySQL database using PHP then check out this.

In this DB connection tutorial, I have discussed how to Make a DB Connection in different ways with MySQL DB.

---

1 – PHP MySQLi procedural method

This is the common method to insert data.

<?php
// Database connection
$database_connection= mysqli_connect("localhost","root","","database_name");

// 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($database_connection, 'John');
$last_name = mysqli_real_escape_string($database_connection,'Doe');
$email = mysqli_real_escape_string($database_connection, '[email protected]');


// Insert query (Insert data into users table)
$insert_query = mysqli_query($database_connection, "INSERT INTO `users` (first_name, last_name, email) VALUES ('$first_name', '$last_name', '$email')");

// Check if data inserted
if($insert_query){
    echo "Data inserted successfully.";
}
// else data not inserted
else{
    echo "Something error occurred";
}
?>

---

2 – PHP MySQLi OOP(Object Oriented Programming) method

<?php
// Database connection
$database_connection = new mysqli("localhost", "root", "", "database_name");

// Check database connection
if ($database_connection === false){
  echo "Failed to connect to MySQL: " . $database_connection->connect_error;
}

// Put data into the variables
$first_name = 'John';
$last_name = 'Doe';
$email = '[email protected]';

// Insert query (Insert data into users table)
$insert_query = $database_connection->query("INSERT INTO `users` (first_name, last_name, email) VALUES ('$first_name', '$last_name', '$email')");

// Check if data inserted
if($insert_query === true){
    echo "Data inserted successfully.";
}
// else data not inserted
else{
    echo "Something error occurred";
}
?>

---

3 – PDO(PHP Data Objects) method

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database_name = "database_name";

// CREATE DATABASE CONNECTION USING PDO METHOD
try {
    $database_connection = new PDO("mysql:host=$servername;dbname=$database_name", $username, $password);
    // Set the PDO error mode to exception
    $database_connection->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')";
    $database_connection->exec($sql);
    echo "Data inserted successfully";
    
    }
catch(PDOException $e)
    {
    echo "data not inserted";
    }
?>

---

This tutorial is completed.

But if you are interested in how to avoid SQL injection attacks to secure you PHP code or application then check out –

How to use the PHP prepared statements to avoid SQL injection

---

how to make an application where we perform CRUD (Create Read Update Delete) operation with MySQL Database using PHP.