Home / Tutorials / PHP / Insert database records

PHP & MySQL - Insert data into a database Tutorial

In this tutorial we learn how to add one or more records to a database table and its columns with PHP and an SQL query.


For this tutorial, please start the MySQL module in your XAMPP control panel.

How to add data to a table

To add data to our database table, we use the INSERT INTO query.

We specify the table name, followed by the columns we want to insert data into between parentheses. Then, we specify the values to go into those columns, also in parentheses.

Syntax: 
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...)
Example: insert data sql query
$sql = "INSERT INTO Users (name, email)
        VALUES ('John Doe', 'john@mail.com')";

In this case we only insert data into the name and email columns.

Our userID column was set as an auto incrementing primary key, so we don’t need to specify a value for it. It will automatically add the number for us.

Any string value like a name or email address should be wrapped in quotes. Because the query itself is wrapped in double quotes, we can use single quotes to wrap the individual values.

Now that we’ve created the SQL query, let’s use PHP to execute it.

Example: 
<?php

$serverName = "localhost";
$userName = "root";
$password = "";
$dbName = "trainingDB";

// create connection
$conn = mysqli_connect($serverName, $userName, $password, $dbName);

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

// sql to add data
$sql = "INSERT INTO Users (name, email)
        VALUES ('John Doe', 'john@mail.com')";

// add data and check result
if (mysqli_query($conn, $sql)) {
    echo "New user added successfully";
} else {
    echo "Could not add user. Error: " . mysqli_error($conn);
}

// close connection
mysqli_close($conn);

?>

When we run the example above, it will add a new row to the Users table.

How to insert multiple records into a table

In rarer cases we may need to add multiple records to a table at once. For this, PHP provides us with the mysqli_multi_query() function.

The syntax for MySQL and MariaDB is different when adding multiple records at once.

Insert multiple records in MariaDB

To add multiple records into a table in MariaDB we simply add more sets of values in between parentheses.

Syntax: 
$result = "INSERT INTO tableName (column1, column2, ...)
           VALUES (value1, value2, ...),
           (value1, value2, ...),
           (value1, value2, ...)";

Each set of values must be separated with a comma.

Example: 
$sql = "INSERT INTO Users (name, email) VALUES
        ('James Bond', 'bond@mail.com'),
        ('John Wick', 'wick@mail.com'),
        ('Ethan Hunt', 'hunt@mail.com')";

Now that we know how the query is structured, let’s add multiple rows with PHP.

Example: 
<?php

$serverName = "localhost";
$userName = "root";
$password = "";
$dbName = "trainingDB";

// create connection
$conn = mysqli_connect($serverName, $userName, $password, $dbName);

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

// sql to add data
$sql = "INSERT INTO Users (name, email) VALUES
        ('James Bond', 'bond@mail.com'),
        ('John Wick', 'wick@mail.com'),
        ('Ethan Hunt', 'hunt@mail.com')";

// add data and check result
if (mysqli_query($conn, $sql)) {
    echo "New users added successfully";
} else {
    echo "Could not add users. Error: " . mysqli_error($conn);
}

// close connection
mysqli_close($conn);

?>

When we run the example above, it adds three more users to our table.

Insert multiple records in MySQL

If you’re using XAMPP, the following code does not apply and you may skip this section of the lesson. XAMPP uses MariaDB instead of MySQL as its database solution.

In MySQL each entry is a full INSERT INTO query, separated by a semicolon.

Syntax: 
$result = "INSERT INTO Users (name, email)
            VALUES ('James Bond', 'bond@mail.com');";
$result .= "INSERT INTO Users (name, email)
            VALUES ('John Wick', 'wick@mail.com');";
$result .= "INSERT INTO Users (name, email)
            VALUES ('Ethan Hunt', 'hunt@mail.com')";

To make the syntax easier to read, we break it up into separate queries and append them to each other with the .= operator.

Syntax: 
$sql = "INSERT INTO Users (name, email)
        VALUES ('James Bond', 'bond@mail.com');";
$sql .= "INSERT INTO Users (name, email)
        VALUES ('John Wick', 'wick@mail.com');";
$sql .= "INSERT INTO Users (name, email)
        VALUES ('Ethan Hunt', 'hunt@mail.com')";

Because the semicolon is part of the SQL query, and not the PHP statement, we add it inside the query’s quotes.

Example: 
<?php

$serverName = "localhost";
$userName = "root";
$password = "";
$dbName = "trainingDB";

// create connection
$conn = mysqli_connect($serverName, $userName, $password, $dbName);

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

// sql to add data
$sql = "INSERT INTO Users (name, email)
        VALUES ('James Bond', 'bond@mail.com');";
$sql .= "INSERT INTO Users (name, email)
        VALUES ('John Wick', 'wick@mail.com');";
$sql .= "INSERT INTO Users (name, email)
        VALUES ('Ethan Hunt', 'hunt@mail.com')";

// add data and check result
if (mysqli_query($conn, $sql)) {
    echo "New users added successfully";
} else {
    echo "Could not add users. Error: " . mysqli_error($conn);
}

// close connection
mysqli_close($conn);

?>

When we run the example above, it adds three more users to our table.

Summary: Points to remember

  • We add data into a table with the INSERT INTO query.
  • We insert multiple queries into a MariaDB table by simply adding more sets of values, separated by a comma.
  • We insert multiple queries into a MySQL table with full INSERT INTO queries, separated by a semicolon.
Previous
Create a database table
Next
Select data in a database