PHP MySQL: Insert Data Into a Table

Summary: in this tutorial, you will learn how to use PHP PDO to insert one or more rows into a table.

To insert data into a table, you follow these steps:

First, connect to the MySQL database by creating a new PDO object.
Second, create a prepared statement.
Third, execute the INSERT statement using the prepared statement.

Inserting one row

The following insert.php script inserts a new row into the tasks table of the todo database:

<?php
require_once 'config.php';

try {
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $sql = 'insert into tasks(title) values(:title)';
    $stmt = $conn->prepare($sql);
    $stmt->execute([':title' => 'Learn PHP MySQL']);
} catch (PDOException $e) {
    die($e);
}
Code language: HTML, XML (xml)

How it works.

First, include the config.php file that contains the database configuration:

require_once 'config.php';Code language: PHP (php)

This statement imported variables declared in the config.php into the script.

Second, establish a connection to the todo database on the MySQL server by creating a new instance of the PDO:

$conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);Code language: PHP (php)

Third, construct an INSERT statement:

$sql = 'insert into tasks(title) values(:title)';Code language: PHP (php)

The :title is a parameterized placeholder. This helps prevent SQL injection.

Fourth, prepare the SQL statement for execution:

$stmt = $conn->prepare($sql);Code language: PHP (php)

Fifth, execute the prepared SQL statement with the provided value:

$stmt->execute([':title' => 'Learn PHP MySQL']);Code language: PHP (php)

This statement inserts a new row into the tasks table with the title 'Learn PHP MySQL'.

If an exception occurs, the script will terminate with an error message:

} catch (PDOException $e) {
    die($e);
}Code language: PHP (php)

Verify the insert

First, connect to the todo database on the MySQL server:

mysql -u root -p -D todo

Second, retrieve data from the tasks table:

SELECT * FROM tasks;

Output:

+----+-----------------+-----------+
| id | title           | completed |
+----+-----------------+-----------+
|  1 | Learn PHP MySQL |         0 |
+----+-----------------+-----------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

The output indicates that the script has inserted a new row into the table successfully.

Inserting multiple rows

To insert multiple rows into a MySQL database using PDO in PHP, you can modify the script to use a prepared statement in a loop.

For example, the following insert-multiple.php script inserts two rows into the tasks table:

<?php
require_once 'config.php';

try {
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);

    $sql = 'INSERT INTO tasks(title) VALUES(:title)';
    $stmt = $conn->prepare($sql);
    
    $titles = ['Build a Web Application', 'Grow the App User Base'];
    foreach ($titles as $title) {
        $stmt->execute([':title' => $title]);
    }

} catch (PDOException $e) {
    die($e->getMessage());
}
Code language: PHP (php)

Summary

Use a prepared statement to insert one or more rows into a table.

Was this tutorial helpful?