How to Insert Data in MySQL using PHP

In this tutorial, we will learn how to insert data in MySQL using PHP. After creating database and table in MySQL, we can start adding data to them.

INSERT INTO Command in MySQL

The SQL provides the INSERT INTO statement to add new records (rows) into a MySQL table. The basic syntax to add new records into a table using the INSERT INTO statement is:

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

Syntax Rules to Follow in PHP

Here are some important rules when using INSERT INTO in PHP:

Rule 1: In PHP, the SQL query must be quoted inside quotes (single or double). This is because the SQL query is a string in PHP. For example:

$sql = "INSERT INTO students (name, age) VALUES ('Deepak', 21)";

Rule 2: String values inside the SQL query must be enclosed in single quotes.

Rule 3: Numeric values should not be enclosed in quotes (recommended practice). However, MySQL will still accept numeric values in single quotes.

Rule 4: The NULL keyword must not be enclosed in quotes, because ‘NULL’ is treated as a string, not a NULL value.

Requirements Before Inserting Data into Table

Before inserting data into table, make sure:

  • MySQL database is created.
  • PHP is connected to MySQL database.
  • Table is created in MySQL using PHP.

In the previous tutorial, we created an empty table named “students” with four columns: “id”, “name”, “email” and “age”. Now, let us fill the table with data.
[blocksy-content-block id=”12371″]
If you don’t know how to create MySQL database and connect  it with PHP, read this: Connect PHP to MySQL Database.

Insert Data Using MySQLi (Object-Oriented Method)

To insert data into a table in MySQL using PHP, first you will have to create table in MySQL database. After creating a table, you can insert data into it.

Example: PHP Code to Insert Data in MySQL Table (Object-Oriented MySQLi)

Inside the htdocs folder, create a new file with name insert_data.php and paste this code in it.

<?php
// Include database connection.
require_once "connect.php";

$sql = "INSERT INTO students (id, name, email, age)
VALUES (12345, 'Deepak', 'deepak@gmail.com', 28)";

if (mysqli_query($conn, $sql)) {
     echo "Data inserted successfully. <br>";
} else {
     echo "Error: " . mysqli_error($conn);
}
mysqli_close($conn);
?>

Now save this file using Ctrl + S.

Now open the browser and go to http://localhost/insert_data.php. If data inserted into table, you will see the following output in your browser.

Output:

Data inserted successfully.

Check Inserted Data in Database Using phpMyAdmin

To check inserted data into table using phpMyAdmin, follow all these steps:

  • Open your browser and type the following URL in the address bar: http://localhost/phpmyadmin.
  • Press Enter.
  • The phpMyAdmin dashboard will open in your browser.
  • In the left-side panel, you will see a list of databases.
  • Click on your database name (Example: scientecheasy_db).
  • After clicking the database name, you will see all the tables inside the database.
  • Click on the table name. (Example: students)
  • After clicking the table, you will see several tabs at the top:
    • Structure tab → Shows columns (id, name, email, etc.)
    • Browse tab → Shows inserted data.
    • Insert tab → Add new record.
    • SQL tab → Run queries.

[blocksy-content-block id=”12121″]

Explanation of PHP Code:

  • The line require_once “connect.php”; includes the database connection file.
  • This file connect.php contains the code that connects PHP to the MySQL database.
  • The require_once makes sure the file is included only one time. If the file is missing, the script will stop execution with a fatal error.
  • After this line require_once “connect.php”;, the variable $conn is available for use.
  • Without this connection file, PHP cannot communicate with the database.
  • Then, we have created a SQL statement and stored it inside the variable $sql.
  • The INSERT INTO statement inserts a new row into the students table and adds the following values:
    • id = 12345
    • name = Deepak
    • email = deepak@gmail.com
    • age = 28
  • So basically, this query is adding one new student record to the table.
  • The mysqli_query() function in PHP sends the SQL query to the database and executes it.
  • Inside the parentheses, $conn is the database connection and $sql is the insert query. If the query runs successfully, it returns TRUE.
  • If the data is inserted without any errors, PHP will display a successful message in the browser.
  • If something goes wrong, PHP will show the error message.
  • The line mysqli_close($conn); closes the connection between PHP and the database. It is good practice to close the connection after completing the task.

Insert Data into Table Using PDO Method

PDO is one of the most secure and widely used methods to insert data into a MySQL table. Follow the steps below to insert data using PDO.

Example: PHP Code to Insert Data in MySQL Table (PDO Method)

<?php
// Include database connection file
require_once "config.php";

try {
// Write SQL INSERT query
   $sql = "INSERT INTO users (id, name, email, age)
   VALUES (3465, 'Amit', 'amit@gmail.com', 24)";

// Execute query
   $conn->exec($sql);
   echo "Record inserted successfully. <br>";
}
catch (PDOException $e) {
   echo "Error: " . $e->getMessage();
}

// Close connection
$conn = null;
?>

Explanation of PHP Code

  • The line require_once “config.php”; contains the PDO database connection code.
  • The require_once ensures that the file config.php is included only once. If the file is missing, the script stops execution with a fatal error.
  • We are using exception handling. If any error occurs inside try, the catch block will catch it as a PDOException object.
  • We called exec() method using PDO connection object $conn.
  • The exec() method executes an SQL statement and returns the number of affected rows as an integer on success.
  • If an error occurs and PDO is not set to exception mode, it returns FALSE. However, if PDO is set to PDO::ERRMODE_EXCEPTION, it throws a PDOException instead of returning FALSE.

Output:

Record inserted successfully.

Insert Multiple Records into Table using PHP

You can insert multiple records into a table using multiple queries.

Example: Adding Multiple Records

<?php
// Include database connection.
require_once "connect.php";

$sql1 = "INSERT INTO students (id, name, email, age)
VALUES (12345, 'Deepak', 'deepak@gmail.com', 28)";

$sql2 = "INSERT INTO students (id, name, email, age)
VALUES (35462, 'Amit', 'amit@gmail.com', 21)";

$sql3 = "INSERT INTO students (id, name, email, age)
VALUES (11123, 'John', 'john@gmail.com', 22)";

$sql4 = "INSERT INTO students (id, name, email, age)
VALUES (12123, 'Saanvi', 'saanvi@gmail.com', 18)";

$sql5 = "INSERT INTO students (id, name, email, age)
VALUES (12332, 'Tripti', 'tripti@gmail.com', 26)";

$conn->query($sql1);
$conn->query($sql2);
$conn->query($sql3);
$conn->query($sql4);
$conn->query($sql5);

echo "Multiple records inserted successfully.";
mysqli_close($conn);
?>

Output:

Multiple records inserted successfully.

Conclusion

PHP offers several methods to insert data into a MySQL database, such as MySQLi Procedural, MySQLi Object-Oriented, and PDO. Among these, prepared statements are the most secure and recommended approach because they improve security and the overall performance by preventing SQL injection attacks.

In this tutorial, we learned the most important methods: MySQLi Object-Oriented, and PDO. We will learn prepared statements in detail in the next tutorial.

By using these methods, you can easily insert user data from HTML forms, applications, and websites into a MySQL database.

DEEPAK GUPTA

DEEPAK GUPTA

Deepak Gupta is the Founder of Scientech Easy, a Full Stack Developer, and a passionate coding educator with 8+ years of professional experience in Java, Python, web development, and core computer science subjects. With strong expertise in full-stack development, he provides hands-on training in programming languages and in-demand technologies at the Scientech Easy Institute, Dhanbad.

He regularly publishes in-depth tutorials, practical coding examples, and high-quality learning resources for both beginners and working professionals. Every article is carefully researched, technically reviewed, and regularly updated to ensure accuracy, clarity, and real-world relevance, helping learners build job-ready skills with confidence.

Related Posts