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', 'jane.doe@example.com')";
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', 'jane.smith@example.com')";
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 = "rachel.green@example.com";
$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', 'mike.ross@example.com')";
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' => 'joey.tribbiani@example.com']);
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', 'alice@example.com'),
('Bob', 'bob@example.com'),
('Charlie', 'charlie@example.com')";

$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.

Similar Reads

PHP strict_types: How the Strict Mode Works

If you want to write good PHP code, strict mode should be on your radar. Strict mode—activated by the command declare(strict_types=1); at…

PHP $_SESSION: Secure Your Web Applications in PHP

It’s very important to remember user data for each session when building web applications. This enables a high level of…

PHP error_log Function: How It Works with Examples

Sometimes things go wrong when PHP runs the code. So, you need to know the reasons for this error to…

PHP Type Juggling: How it Works Behind the Scenes

PHP type juggling refers to the dynamic system where the type of a variable is determined by its context in…

Understanding PHP Constants: A Simple Guide with Examples

In regard to the use of constants in PHP, clear understanding of their definition and use could be critical in…

PHP MySQL WHERE: How to Filter Data in MySQL

Filtering data is a big part of getting the right information to show up. That is where the WHERE clause…

PHP Assign Function to Variable – Examples and Explanation

PHP variable function is a variable that is used to assign a function name with parentheses (). However, the PHP interpreter…

Understanding the PHP Operator Precedence

The PHP operator precedence refers to when doing a calculation for three or more numbers, they are calculating its values…

PHP Array: Accessing and Managing Elements

PHP arrays are lists or maps that contain multiple values grouped by array keys. They may include integers, strings, booleans,…

PHP Global Variables & Superglobals: A Complete Guide

Variables in PHP are general elements used for data storage and other manipulations. Global variables belong to a particular category…

Previous Article

What Is PHP Used For? Meaning of PHP Programming Language

Next Article

How to Insert Multiple Rows in MySQL with PHP?

Write a Comment

Leave a Comment

Your email address will not be published. Required fields are marked *


Subscribe to Get Updates

Get the latest updates on Coding, Database, and Algorithms straight to your inbox.
No spam. Unsubscribe anytime.