We use cookies to ensure that we give you the best experience on our website.

insert data into a MySQL database using PHP

How to insert data into a MySQL database using PHP?

In this tutorial, you will learn how to insert data into a MySQL database using PHP.

Let’s start by creating a database, I named my database “my_test_db“, you can choose any name.

Now, Inside the database create the “users” table that has four columns – user_id, name, age, and email.

Use the following SQL code to create the “users” table and its columns.

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `email` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

PHP inserts data into MySQL database

PHP has three database APIs (MySQLi, OOP, PDO), therefore it has three different methods or code syntax for inserting data into the MySQL database. Let’s see one-by-one –

You need to know before proceeding –

1. PHP MySQLi Insert data into MySQL database

<?php
$db_host = 'localhost';
$db_user = 'root';
$db_password = '';
$db_name = 'my_test_db';

# Database Connection
$db_connection = mysqli_connect($db_host, $db_user, $db_password, $db_name);

# Data
$name = "John Doe";
$age = 52;
$email = "[email protected]";

# SQL query (statement) to insert data
$sql = "INSERT INTO users (`name`, `age`, `email`) VALUES ('$name', $age, '$email')";
# Executing the SQL query
$query = mysqli_query($db_connection, $sql);

# Checks that the query executed successfully
if($query){
    echo "New data inserted successfully.";
}
else{
    echo "Failed to insert new data.";
}

2. PHP MySQLi OOP Insert data into MySQL database

<?php
$db_host = 'localhost';
$db_user = 'root';
$db_password = '';
$db_name = 'my_test_db';

# Database Connection
$db_connection = new mysqli($db_host, $db_user, $db_password, $db_name);

# Data
$name = "Mark";
$age = 25;
$email = "[email protected]";

# SQL query (statement) to insert data
$sql = "INSERT INTO users (`name`, `age`, `email`) VALUES ('$name', $age, '$email')";
# Executing the SQL query
$query = $db_connection->query($sql);

# Checks that the query executed successfully
if($query){
    echo "New data inserted successfully.";
}
else{
    echo "Failed to insert new data.";
}

3. PHP PDO Insert data into MySQL database

<?php
$db_host = 'localhost';
$db_user = 'root';
$db_password = '';
$db_name = 'my_test_db';

try {

    # Database Connection
    $dsn = "mysql:host=$db_host;dbname=$db_name;charset=utf8";
    $db_connection = new PDO($dsn, $db_user, $db_password);
    $db_connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    # Data
    $name = "Ram";
    $age = 22;
    $email = "[email protected]";

    # SQL query (statement) to insert data
    $sql = "INSERT INTO users (`name`, `age`, `email`) VALUES ('$name', $age, '$email')";
    # Executing the SQL query
    $db_connection->exec($sql);

    echo "New data inserted successfully.";

} catch(PDOException $e) {
    echo $e->getMessage();
}

In the above, you saw a simple way to insert data into MySQL database. But, you must use prepared statements to insert data in a safe and efficient manner.

Related Posts