How to Insert Data into MySQL Using PHP if it Doesn’t Exist

in this article, We’ll learn “INSERT INTO IF NOT EXISTS” with examples. We’ll also provide examples demonstrating its effective implementation. We will cover both core PHP and Laravel approaches. This allows you to add data to a table only if a matching record does not already exist.

INSERT INTO MySQL IF NOT EXISTS

Let’s demonstrate the effective implementation of this feature in a PHP application. We will guide you step by step through various methods to insert records into MySQL only if they do not already exist.

You use this feature on the below real scenarios:

• Preventing Duplicate Entries
• Maintaining Data Integrity
• Logging Actions
• Managing Constraints

The Basic MySQL Syntax:

INSERT INTO table_name (column1, column2, ...)
SELECT value1, value2, ...
WHERE NOT EXISTS (
    SELECT 1
    FROM table_name
    WHERE condition
);

Whereas params are:

• table_name: The name of the table into which you want to insert data.
• (column1, column2, …): The name of the columns to which you want to insert values.
• SELECT value1, value2, …: The values you want to insert into the specified columns.
• WHERE NOT EXISTS: The condition that checks whether a matching record exists in the table.
• SELECT 1 FROM table_name WHERE condition: The subquery that checks for the existence of a record based on the specified condition.

Simple Example:

Let’s use the above syntax(“INSERT INTO IF NOT EXISTS”) in the live example and create a query to insert a record into MySQL if the record does not exist.

We’ll insert a new user into a user database only if their username does not already exist:

INSERT INTO users (username, password)
SELECT 'newuser', 'password123'
WHERE NOT EXISTS (
    SELECT 1
    FROM users
    WHERE username = 'newuser'
);

When you execute the query mentioned above, the data will be added to the ‘users’ table if there is no existing record with the same username.

INSERT INTO IF NOT EXISTS in Laravel

Laravel provides a convenient way to use “INSERT INTO IF NOT EXISTS” using the Eloquent ORM (Object-Relational Mapping) or raw SQL queries.

Using Eloquent ORM

Step 1: Please make sure that you have a model corresponding to the table you want to insert data into.

Step 2: You can use the firstOrNew method provided by Eloquent. This method attempts to retrieve a record matching the given attributes or creates a new one if no matching record is found.

use App\Models\Users; // Replace with your actual model

// Define the data you want to insert
$data = [
    'username' => 'value1',
    'passaword' => 'value2'
];

// Check if a record with specific attributes exists, and create it if not
$record = YourModel::firstOrNew($data);

// Save the record to the database
$record->save();

In the above example, We have defined ‘users’ models and defined data that need to be checked and inserted into the database. I have used firstOrNew method to check if a record exists in the database. If not, it creates a new instance of the model with those attributes and then saves it to the database.

Using Raw SQL Queries

You can also execute RAW SQL queries into Laravel using elequonte. The Laravel provides a DB facade to execute raw SQL queries.

use App\Models\Users; // Replace with your actual model

// Define the data you want to insert
$data = [
    'username' => 'value1',
    'passaword' => 'value2'
];

// Write the raw SQL query
$query = "INSERT INTO users (username, passaword) 
          SELECT :username 
          WHERE NOT EXISTS (
              SELECT 1 FROM users 
              WHERE username = :column1
          )";

// Execute the raw SQL query
DB::statement($query, $dataToInsert);

In this example, We use raw SQL to perform the “INSERT INTO IF NOT EXISTS” operation.

INSERT IGNORE Statement

The ‘INSERT IGNORE’ is used to new records into a table, but if there is a conflict with an existing record based on a unique key or primary key constraint, the new record is ignored, and the existing record remains in place.

It helps to ignore any duplicate key errors that might occur.

The Syntax for the INSERT IGNORE statement:

INSERT IGNORE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

• table_name: The name of the table into which you want to insert data.
• column1, column2, ...: The columns in the table where you want to insert data.
• value1, value2, ...: The values you want to insert into the corresponding columns.

Example:

INSERT IGNORE INTO employees (employee_id, emp_name, emp_salary)
VALUES (3101, 'David', 1234);

If an employee with employee_id 3101 already exists in the table, this INSERT IGNORE statement will not raise an error.

Conclusion:

We’ve explored the ‘INSERT INTO IF NOT EXISTS’ statement using real-world examples. This helps to maintain data integrity and prevent duplicate entries effectively. You have also learned how to use ‘INSERT IGNORE’ statement.