SQL Query - INSERT but only if record doesn't already exist?

Question

So, I am writing an SQL Query, as you do, and I have stumbled across an issue. What I am wanting to do is fire a query at the database every time a unique user visits a certain page. The unique-ness is done through session_id(), as that is generally quite reliable.

What I want is an SQL query which inserts a row of data into two columns, but ONLY if those two columns don't already have the same value.

if user x visits the page named page1, then in the database, it will log as shown below:

+------------+---------+
| page_name  | user_id |
+------------+---------+
|    page1   |    x    |
+------------+---------+

How would I go about writing a query which checks the table to see if the data being inserted will be a duplicate?

My current query is as follows (it's just a standard insert query):

INSERT INTO `page_views` (`page_name`, `user_id`) VALUES ('.$pageName.', \''.session_id().'\')

check answer here:- http://stackoverflow.com/questions/16460397/sql-insert-into-table-only-if-record-doesnt-exist — Serving Quarantine period, Apr 21 '16 at 10:01
Possible duplicate of [Check if a row exists, otherwise insert](http://stackoverflow.com/questions/639854/check-if-a-row-exists-otherwise-insert) — Raj, Apr 21 '16 at 10:02
Possible duplicate of [MySQL Conditional Insert](http://stackoverflow.com/questions/913841/mysql-conditional-insert) — Bill Tür, Apr 21 '16 at 10:04

score 8 · Accepted Answer · answered Apr 21 '16 at 10:03

8

Use INSERT IGNORE rather than INSERT. If a record doesn't duplicate an existing record, MySQL inserts it as usual. If the record is a duplicate, the IGNORE keyword tells MySQL to discard it silently without generating an error.

Following example does not error out and same time it will not insert duplicate records.

The syntax is simple - just add "IGNORE" after "INSERT" like so:

INSERT IGNORE INTO mytable
    (pk, f1, f2)
VALUES
    ('abc', 1, 2);

Inserting multiple records When inserting mutiple records at once, any that cannot be inserting will not be, but any that can will be:

INSERT IGNORE INTO mytable
    (pK, f1, f2)
VALUES
    ('abc', 1, 2),
    ('def', 3, 4),
    ('ghi', 5, 6);

In the second example above, if records already existed for 'abc' and 'def' then those two records would not be inserted, but the record for 'ghi' would be.

answered Apr 21 '16 at 10:03

Jitendra Kumar. Balla

1,123
1
9
15

1

Ohh, I hadn't heard of that until now. That saves an extra query over my method, cool! – RefreshCarts Apr 21 '16 at 10:08
Excellent answer and fix to the problem. Only issue is, there's no error being produced, but it looks as if the 'IGNORE' part is being ignored when using an SQL window on my phpMyAdmin... – Ryan Apr 21 '16 at 10:16
NB: This approach works, but ignores *all* errors, not just duplicate key errors. The `INSERT … ON DUPLICATE KEY UPDATE` approach only ignores duplicate key errors. (https://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql/1361368#1361368) – rinogo Apr 07 '18 at 22:06

score 0 · Answer 2 · answered Apr 21 '16 at 10:07

0

Perform a check on the data base with select to see if it's already there :) Code sample below.

$checkQuery = "SELECT page_name FROM page_views WHERE page_name='".$pageName."' AND user_id='".session_id()."'";
$checkResult = mysqli_query($DBLink, $checkQuery);

    if (mysqli_num_rows($checkResult) != 0){
    // CODE FOR IF DUPLICATE FOUND
    } else {
    // NO DUPLICATE CODE GOES HERE
    }

answered Apr 21 '16 at 10:07

RefreshCarts

106
1
9

1

This would result in multiple queries being used, rather than one query, which is what I was trying to achieve. Thanks for your contribution though. – Ryan Apr 21 '16 at 10:09
1

That's cool, I hadn't heard of insert ignore until today, after over 10 years of php / mysql, crazy eh :) Thanks for the kinds words, I'm going to be using the INSERT IGNORE method in future work myself now also! – RefreshCarts Apr 21 '16 at 10:10

SQL Query - INSERT but only if record doesn't already exist?

2 Answers2