PHP: MySQL Insert into if not exists

on this article, We’ll study “INSERT INTO IF NOT EXISTS” with examples. We’ll additionally present examples demonstrating its efficient implementation. We are going to cowl each core PHP and Laravel approaches. This lets you add knowledge to a desk provided that an identical file doesn’t exist already.

INSERT INTO MySQL IF NOT EXISTS

Let’s exhibit the efficient implementation of this characteristic in a PHP utility. We are going to information you step-by-step by numerous strategies to insert data into MySQL provided that they don’t exist already.

You employ this characteristic on the under actual situations:

• Stopping Duplicate Entries
• Sustaining Information Integrity
• Logging Actions
• Managing Constraints

The Fundamental MySQL Syntax:

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

Whereas params are:

• table_name: The identify of the desk into which you wish to insert knowledge.
• (column1, column2, …): The identify of the columns to which you wish to insert values.
• SELECT value1, value2, …: The values you wish to insert into the required columns.
• WHERE NOT EXISTS: The situation that checks whether or not an identical file exists within the desk.
• SELECT 1 FROM table_name WHERE situation: The subquery that checks for the existence of a file based mostly on the required situation.

Easy Instance:

Let’s use the above syntax(“INSERT INTO IF NOT EXISTS”) within the reside instance and create a question to insert a file into MySQL if the file doesn’t exist.

We’ll insert a brand new consumer right into a consumer database provided that their username doesn’t exist already:

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

While you execute the question talked about above, the info can be added to the ‘customers’ desk if there isn’t a current file with the identical username.

INSERT INTO IF NOT EXISTS in Laravel

Laravel gives a handy means to make use of “INSERT INTO IF NOT EXISTS” utilizing the Eloquent ORM (Object-Relational Mapping) or uncooked SQL queries.

Utilizing Eloquent ORM

Step 1: Please just be sure you have a mannequin equivalent to the desk you wish to insert knowledge into.

Step 2: You should utilize the firstOrNew methodology offered by Eloquent. This methodology makes an attempt to retrieve a file matching the given attributes or creates a brand new one if no matching file is discovered.

use AppModelsUsers; // Exchange together with your precise mannequin

// Outline the info you wish to insert
$knowledge = [
    'username' => 'value1',
    'passaword' => 'value2'
];

// Test if a file with particular attributes exists, and create it if not
$file = YourModel::firstOrNew($knowledge);

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

Within the above instance, Now we have outlined ‘customers’ fashions and outline knowledge that must test and insert into database. I’ve used firstOrNew methodology to checks if a file exists within the database. If not, it creates a brand new occasion of the mannequin with these attributes after which saves it to the database.

Utilizing Uncooked SQL Queries

You may also execute RAW sql queries into the laravel utilizing elequonte. The laravel procvide DB facade to execute uncooked SQL queries.

use AppModelsUsers; // Exchange together with your precise mannequin

// Outline the info you wish to insert
$knowledge = [
    'username' => 'value1',
    'passaword' => 'value2'
];

// Write the uncooked SQL question
$question = "INSERT INTO customers (username, passaword) 
          SELECT :username 
          WHERE NOT EXISTS (
              SELECT 1 FROM customers 
              WHERE username = :column1
          )";

// Execute the uncooked SQL question
DB::assertion($question, $dataToInsert);

On this instance, We use uncooked SQL to carry out the “INSERT INTO IF NOT EXISTS” operation.

INSERT IGNORE Assertion

The ‘INSERT IGNORE’ is used to new data right into a desk, but when there’s a battle with an current file based mostly on a novel key or main key constraint, the brand new file is ignored, and the prevailing file stays in place.

It helps to disregard any duplicate key errors which may happen.

The Syntax for the INSERT IGNORE assertion:

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

• table_name: The identify of the desk into which you wish to insert knowledge.
• column1, column2, ...: The columns within the desk the place you wish to insert knowledge.
• value1, value2, ...: The values you wish to insert into the corresponding columns.

Instance:

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

If an worker with employee_id 3101 already exists within the desk, this INSERT IGNORE assertion is not going to increase an error.

Conclusion:

We’ve explored the ‘INSERT INTO IF NOT EXISTS’ assertion utilizing real-world examples. This helps to take care of knowledge integrity and forestall duplicate entries successfully. You’ve additionally realized the best way to use ‘INSERT IGNORE’ assertion.