PHP MySQL Insert Data – A Complete Guide

In the previous tutorials, we learned to establish a database connection and create a table. In this tutorial, we will learn to insert data into the table that we had created. We can insert the data into the table using MySQLi as well as PDO. Let’s start with the rules that you must know before writing a PHP script to insert data into the MySQL table.

Also read: PHP MySQL Create Table

Insert Data into MySQL Table

Before writing the code to insert the data into the table, you must follow the following rules-

• The INSERT query must be quoted in the PHP.
• String values must be quoted inside the INSERT query.
• Numeric values must not be quoted. If quoted, those will be considered as string values.
• NULL values must not be quoted. If quoted, NULL will be considered as a string.

These are some important rules that you must follow in order to insert the data into the MySQL table successfully.

We can insert the data into the table using the INSERT statement. In this tutorial, we will insert the data into the table that we had created in the previous tutorial.

Our table has three columns- id, name and city. However, the id column is set to auto-increment. Hence, we should avoid inserting values into the id column.

Note that, if the columns are set to auto-increment values or default values such as current time, you don’t need to insert data into it explicitly. MySQL automatically inserts the data into these columns.

Inserting data using MySQLi (Procedural)

<?php
$server = "localhost";
$username = "root";
$password = "";
$dbname = "journaldev";

$conn = mysqli_connect($server, $username, $password, $dbname);
if (!$conn) {
    die("Connection Failed " . mysqli_connect_error());
}

$query = "INSERT INTO emp(name, city) VALUES('John', 'Boston')";

$res = mysqli_query($conn, $query);
if (!$res) {
    die("Query Failed " . mysqli_error($conn));
} else {
    echo "Data inserted using mysqli procedural method";
}

mysqli_close($conn);
?>Code language: PHP (php)

Here, we have written the INSERT query into the quotation and assigned it to the ‘query’ variable.

Then we execute the query using the mysqli_query() statement. If the query executes successfully, it returns true, else it returns false.

We check if the query returns true. If the query executes successfully, we print the message that “data inserted successfully”. Else, we print the error message using the mysqli_error() function.

Inserting data using MySQLi (Object-Oriented)

<?php
$server = "localhost";
$username = "root";
$password = "";
$dbname = "journaldev";

$conn = new mysqli($server, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed" . $conn->connect_error);
}

$query= "INSERT INTO emp(name,city) VALUES('Mark', 'London')";

$res = $conn->query($query);
if (!$res) {
    die("Query Failed " . $conn->error);
} else {
    echo "Data inserted using mysqli object-oriented method";
}


$conn->close();
?>Code language: PHP (php)

Inserting data using MySQLi (PDO)

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "journaldev";
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // set the PDO error mode to exception

    $query = "INSERT INTO emp(name,city) VALUES('Peter','Helsinki')";
    $conn->exec($query);
    echo "Data inserted successfully using PDO";
} catch (PDOException $e) {
    echo $e->getMessage();
}

$conn = NULL;
?>Code language: PHP (php)

We have successfully executed the PHP script three times. Let’s see if three rows are inserted into the table or not.

SELECT * FROM emp;Code language: SQL (Structured Query Language) (sql)

As you can see, we have got three records in the table.

Conclusion

In this tutorial, we learned to insert the data into the MySQL table using PHP. However, we have tried to insert only a single row each time and not multiple rows. In the next tutorial, we will learn to insert multiple rows in a single query from the PHP script.