How to Insert Data into MySQL with PHP

pplications. Such As as From registering new users to collecting form submissions and storing product details.

Things like adding a user profile information during sign-up or saving feedback from a contact form—these operations rely heavily on the integration between PHP and MySQL.

In this guide, you will learn many ways to insert data into MySQL using PHP, covering everything from basic SQL syntax to secure examples like prepared statements.

Basic Syntax of the INSERT Data INTO SQL Statement

The INSERT INTO SQL command is used to add new rows of data to a table in a database.

This assumes that you will provide values for all columns in the correct order. Here is the syntax:

INSERT INTO table_name VALUES (value1, value2, value3, ...);

To use another pattern for this statement is to specify columns name with values:

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

These two statements are only to show you how to insert data into SQL, but how can you achieve that in PHP? Actually, we have two ways to do that! MySQLi and PDO. In the following section you will learn each one in details.

Insert Data into MySQL Using PHP

PHP provides two ways to allow you insert data into SQL database which are PDO and MySQLi.

Inserting Data Using mysqli

The mysqli extension to interact with MySQL databases. It provides us two ways to insert data. They are procedural and object-oriented.

Here is the procedural method:

$conn = mysqli_connect("localhost", "username", "password", "database");

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql = "INSERT INTO users (name, email) VALUES ('Jane Doe', '[email protected]')";
if (mysqli_query($conn, $sql)) {
    echo "New record created successfully.";
} else {
    echo "Error: " . mysqli_error($conn);
}

mysqli_close($conn);

And here the Object-oriented method:

$conn = new mysqli("localhost", "username", "password", "database");

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO users (name, email) VALUES ('Jane Smith', '[email protected]')";
if ($conn->query($sql) === TRUE) {
    echo "Record added successfully.";
} else {
    echo "Error: " . $conn->error;
}

$conn->close();

Both of them handle errors using the respective error functions. Here is another example using prepared statements for secure data insertion.

$conn = mysqli_connect("localhost", "username", "password", "database");
$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);

$name = "Rachel Green";
$email = "[email protected]";
$stmt->execute();

echo "Record inserted successfully.";
$stmt->close();
$conn->close();

Anyway, let’s move on to the section below to see how to insert data into SQL using PDO in PHP.

Inserting Data to MySQL Using PDO

You need first to set up a connection between SQL and PHP. Here is the code of connection using PHP:

try {
    $pdo = new PDO("mysql:host=localhost;dbname=database", "username", "password");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

To execute an insert query use the below one:

$sql = "INSERT INTO users (name, email) VALUES ('Mike Ross', '[email protected]')";
try {
    $pdo->exec($sql);
    echo "New record created successfully.";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

Here is another example using prepared statements:

$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$stmt->execute(['name' => 'Joey Tribbiani', 'email' => '[email protected]']);
echo "Record inserted successfully.";

In the following section, you will learn how to insert multiple rows into the database in a single execution.

Insert Multiple Rows of Data into MySQL in PHP

You can insert multiple rows with a single query to improve performance. Just use this example:

$sql = "INSERT INTO users (name, email) VALUES 
('Alice', '[email protected]'),
('Bob', '[email protected]'),
('Charlie', '[email protected]')";

$conn->query($sql);

Let’s summarize it.

Wrapping Up

The process of Insert data to a MySQL database with PHP is one of the basic tasks in web development.

It involves using the INSERT INTO SQL statement to add new rows of data into a database table, a task commonly required for operations like user registrations or storing form submissions. To achieve this, PHP provides two primary extensions: MySQLi and PDO.

MySQLi offers both procedural and object-oriented approaches, while PDO provides a database-agnostic solution. Both methods support prepared statements, which are important for securing data inputs against SQL injection. By establishing a database connection in PHP and executing INSERT INTO queries using these extensions, you can securely manage data insertion in your projects.