Insert Data into Database in PHP MySQL – Complete Guide

Introduction

After establishing a connection between PHP and MySQL, developers can execute SQL queries to store new records in the database. The process of adding new records to a database table is known as inserting data.

PHP provides functions that allow developers to run SQL INSERT queries easily. These queries take the data submitted by users and store it in the appropriate database table.

What is Insert Data into Database?

Insert Data into Database refers to the process of adding new records into a database table using SQL queries through a PHP script.

In a MySQL database, information is stored in tables, and each table contains rows and columns. Each row represents a record, while each column represents a specific field of information.

For example, a users table might contain columns such as:

  • id
  • name
  • email
  • password

When a new user registers on a website, the application must store the user’s information in this table. PHP can execute an SQL INSERT query to add a new row containing the user’s data.


INSERT INTO users (name, email) VALUES ('John', 'john@example.com');

PHP executes this query using database connection functions such as mysqli_query().

Why It Is Used

Inserting data into a database is essential for many types of web applications.

1. User Registration Systems

Websites store new user information when people sign up.

2. Contact Forms

Messages submitted through contact forms are often saved in a database.

3. Blog Comments

Blog websites store user comments so they can be displayed later.

4. Product Listings

E-commerce websites store product details such as name, price, and description.

5. Data Management

Applications can keep track of records such as orders, customers, or feedback.

Without the ability to insert data into a database, websites would not be able to store user-generated content.

Syntax

The basic SQL syntax for inserting data into a database is:


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

In PHP, this query is executed using the mysqli_query() function.

Example syntax in PHP:


$sql = "INSERT INTO users (name, email) VALUES ('John', 'john@example.com')";
mysqli_query($conn, $sql);

Explanation:

  • INSERT INTO specifies the table where data will be stored.
  • The column names define which fields will receive the data.
  • VALUES specifies the values to be inserted into the table.

Example

Below is a simple example demonstrating how to insert data into a MySQL database using PHP.


$servername = "localhost";
$username = "root";
$password = "";
$database = "testdb";

$conn = mysqli_connect($servername, $username, $password, $database);

if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}
$name = "John";
$email = "john@example.com";
$sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')";
if (mysqli_query($conn, $sql)) {
  echo "New record inserted successfully";
} else {
  echo "Error: " . mysqli_error($conn);
}

Explanation:

  1. The script connects to the MySQL database.
  2. Variables store the data that will be inserted.
  3. The SQL INSERT query is created.
  4. The mysqli_query() function executes the query.
  5. If successful, a confirmation message is displayed.

Real-Life Example

A common real-life example of inserting data into a database is a user registration form.

When a user signs up on a website, they enter information such as:

  • Name
  • Email
  • Password

After the form is submitted, PHP receives the data and inserts it into the users table.

Example process:

  1. User fills out the registration form.
  2. The form sends data to a PHP script.
  3. PHP connects to the database.
  4. PHP executes an INSERT query to store the user data.
  5. The user account is successfully created.

Another example is an online store where administrators add new products. Each product’s name, price, and description are inserted into a database table.

Common Mistakes

Beginners often make several mistakes when inserting data into a database.

1. Not Escaping User Input

If user input is not properly handled, it may lead to SQL injection attacks.

2. Incorrect Table or Column Names

Using incorrect table or column names will cause the query to fail.

3. Forgetting Quotes Around Text Values

Text values must be enclosed in quotes inside SQL queries.

Incorrect:


VALUES (John, john@example.com)

Correct:


VALUES ('John', 'john@example.com')

4. Not Checking Query Errors

Developers sometimes forget to check whether the query executed successfully.

5. Not Using Prepared Statements

Using simple queries with user input may lead to security vulnerabilities.

Conclusion

Inserting data into a database is a fundamental operation in web development. It allows websites to store user information, comments, product details, and other important records.

Using PHP with MySQL, developers can execute SQL INSERT queries to add new records to database tables. This process enables dynamic websites to store and manage user-generated content efficiently.

Related PHP Tutorials