Introduction

Whatever we see on our website, it is either saved within HTML or being fetched from a table. The HTML has a script file attached with it which calls backend, where SQL codes are written. These SQL codes interact with database. In this article we will learn how to insert unique data in PHP. There are different ways to do that, we will showcase a few of the options.

How to insert unique data in PHP

We can have multiple use cases where unique data upload is required, we will discuss a few of them:

1. If one column is a primary key

In this particular use case, we can either first check if data already exists and accordingly return message:
// Perform query to check for existing data

if ($result = $conn -> query("SELECT * FROM transactions WHERE ReceivedDate = '{$checkDate}'")) {
 if($result->num_rows > 0) {
 $dataArray[0] = 'Data already exists!';
echo json_encode($dataArray);
 }
else{
// SQL Query to Insert
}

Or we can simply try Insertion and return error raised by SQL query:

$sql = "INSERT INTO transactions(Date, Type) VALUES ('$Date', '$Type')";
$result = mysqli_query($conn,$sql) or die(mysqli_error($db));
echo $result;

2. If an array is required to be pushed and has duplicate data

In this use case, we need to just remove duplicates from the array as shown below. The array_unique function does the task.

$result = array_unique($your_list);

3. If you want to only Insert Unique data of an array and ignore other data

In this use case, we need to just ignore the duplicates one and continue insertion operation. This is very helpful in case you have a large set of data. The INSERT IGNORE statement does the task.

INSERT IGNORE INTO transactions (Date, Type) VALUES ('$Date', '$Type');

4. If you want to only Insert Unique data and update in case of duplicate data

In this use case, we need to just update the duplicates one and continue insertion operation. This is very helpful in case you have a large set of data. The ON DUPLICATE KEY UPDATE statement does the task.

INSERT INTO transactions (Date, Type) VALUES ('$Date', '$Type') ON DUPLICATE KEY UPDATE transactions SET Type = '{$Type}' WHERE Date = '{$Date}';

5. Even your primary keys can be duplicate

In this particular use case, you can simply create a timestamp column in table and auto assign it the value of timestamp.

The above solution will fail in case the data is uploaded in form of array as timestamp will be same for all the data. To solve this, we need to have a column called GUID (globally unique identifier). This can be either generated from the frontend or can be generated in PHP using function NEWID().

INSERT INTO transactions(guid, Type) VALUES (NEWID(), '$Type');

SQL Changes to insert only unique data

To make a table to accept only unique data, it is required to have one of the following:

1. At least one unique or primary key

We need to have at least one unique or primary key

2. Unique index on multiple columns

This can be achieved by making multiple columns as keys or by making multiple columns combinations as unique using the following SQL query:

CREATE UNIQUE INDEX index_name ON table_name (field_1,field_2);

ALTER TABLE table_name ADD UNIQUE (field_1,field_2);