How do I insert a record into a MySQL Database only if an existing record doesn't exist?

I have MySQL table like the following :

Domain -=- A domain name
Date -=- The date the domain was added to the database
Rank -=- A metric that we calculate for the domain

None of the values are unique. For example you could have the domain name "example.com" with a different Rank value for the past 7 days so there'd be 7 entries in the database all with different dates.

We are calculating a new "Rank" value in PHP but we only want to add it to the database if it's different to the last value in the database. So we need to look at the date column for the specified domain and only add a record if the Rank is different from the currently calculated Rank.

I'm not sure if I should be doing this with multiple queries or if there's a way to do this with one mySQL query?
TotallyMeAsked:
Who is Participating?
 
birwinConnect With a Mentor Commented:
As I said, I hadn't tested it. When I did I found you have to slightly hot-wire it, as shown below. In your code you had missed the insert list.  I tested this, and it works perfectly, although, of course, I was using my own database.
Note that the LIMIT 1 is outside the last parenthesis.
$query = "INSERT INTO database_name (domain, date, rank) select '$domain', NOW(), '$rank' FROM database_name  WHERE NOT EXISTS (SELECT * FROM database_name WHERE domain= '$domain' AND rank='$rank' ORDER BY date desc ) LIMIT 1 "
0
 
birwinCommented:
I couldn't test this, but the basic form of the query should be:
$query = "INSERT INTO database_name (domain, date, rank) values ($domain, NOW(), $rank)  WHERE NOT EXISTS (SELECT * FROM database_name WHERE domain= $domain AND rank=$rank);

 
0
 
birwinCommented:
I re-read your query, and realized I didn't handle the different from the last entry.
Just add to the select query  
ORDER BY date desc LIMIT 1
 
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Beverley PortlockCommented:
Make a composite index on the table so that the domain and rank are UNIQUE and then just use an ordinary INSERT statement. The INSERT will fail if the record is already present but you can either catch the failure or just ignore it.

 ALTER TABLE `myTableName` ADD UNIQUE (
`domain` ,
`rank`
)
0
 
Beverley PortlockCommented:
If you want to catch the error then

mysql_query("INSERT INTO.....

if ( mysql_errno() == 1062 ) {
    .... attempt to add a duplicate failed
}

The advantage of doing it with a UNIQUE index is that the database ensures the uniqueness
0
 
TotallyMeAuthor Commented:
birwin: Thanks for your help I'm getting closer but still getting an error when I test this query in phpMyAdmin:

INSERT INTO `my_table` VALUES ('domain.com', now(), '9') WHERE NOT EXIST (SELECT * FROM `my_table` WHERE `domain` = 'domain.com' AND `rank` = 6 ORDER BY `date` DESC LIMIT 1);

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE NOT EXIST (SELECT * FROM `my_table` WHERE `domain` = 'domain.com' AND `rank` ' at line 1
0
 
TotallyMeAuthor Commented:
Ok just found one bug, I was using :
"WHERE NOT EXIST"
rather than
"WHERE NOT EXISTS"

Fixed it but I'm still getting same error
0
 
TotallyMeAuthor Commented:
I just had a Google and can't seem to find anyone using INSERT statements with WHERE NOT EXISTS so I'm starting to wonder if it's even possible.

I'm trying to look for another way to achieve this. Unfortunately bportlocks method won't work as it doesn't allow me to compare the Rank I'm creating with the current Rank.
0
 
mabbj747Commented:
Try this

INSERT INTO my_table(domain, date, rank)
SELECT
   vwData.domain
   , vwData.date
   , vwData.rank
FROM
   (
      SELECT
             'domain.com' As domain
            , now() date
            , '9' rank
    )vwData
LEFT OUTER JOIN
   my_table
ON
   vwData.domain = my_table.domain
AND        
   vwData.date = my_table.date
AND        
   vwData.rank = my_table.rank
WHERE
   my_table.domain IS NULL
   
0
 
TotallyMeAuthor Commented:
mabbj747: WOW, that blew my mind, didn't realise it could be that confusing. The error it returned was:

#1267 - Illegal mix of collations (utf8_unicode_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '='
0
 
TotallyMeAuthor Commented:
Awesome, that works great. The only thing I've noticed is it doesn't insert anything if the table is empty, as soon as I add one row manually it works fine but it won't insert any data into an empty table.

This is my latest SQL, had to include "AND date != now()"  :

  $sql = "INSERT INTO `domains` (domain, date, rank)
              SELECT '$data[1]', NOW(), '$data[0]' FROM `domains`
              WHERE NOT EXISTS (
                SELECT * FROM `domains` WHERE domain = '$data[1]'
                AND rank = '$data[0]' AND date != now() ORDER BY date DESC
              ) LIMIT 1"
0
 
birwinCommented:
I don't see how adding the logic of adding AND != date works. Does that allow you to enter into a blank database and it won't without it?
Try adding LIMIT 1 on both sides of the parentheses.
ORDER BY date DESC LIMIT 1 ) LIMIT 1"
I tried that on an empty table and it worked.
0
 
TotallyMeAuthor Commented:
The "AND date != now()" is just so it wouldn't add a duplicate entry for the same day.

That extra LIMIT 1 didn't work, I think the problem is because the INSERT is dependent on the SELECT? I've been having a play around with it but can't seem to work it out.

0
 
birwinCommented:
Can you post the actual structure of your database? Are there any fields other than the three you mentioned? Is your date expressed at datetime or just YYYY-MM-DD?
I tested my code on a database with about a dozen fields. I copied  the database, emptied it and ran the query. It worked as expected without error.
0
 
TotallyMeAuthor Commented:
This is the table, yes date is as YYYY-MM-DD.

CREATE TABLE IF NOT EXISTS `domains` (
  `domain` varchar(253) NOT NULL,
  `date` date NOT NULL,
  `rank` int(9) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
0
 
mabbj747Commented:
TotallyMe, I don't know why you found the query so confusing. It only inserting the data which is not in the tbale by joining to the same table. If you are getting the collation conflicts then you can specify the collation with the string columns in the join class.
0
 
birwinCommented:
Can you clarify the problem? You earlier mentioned an issue with empty tables. This is a problem that I tested for, but could not duplicate.
I also still don't get why you need the date in the select statement. The point of this code is that if the domain name exists, and it has a ranking equal to the ranking you are planning to insert, then the query should insert nothing.
If it is used on a blank table, it iserts the data, but once it iserts that data, it should be unable to reinsert the same domain / rank information again. if the oldest entry for that domain in the table has the same rank as you are trying to enter. This code does not just test for yesterday's date, it finds the latest entry in the database for that domain, regardless of its date.
I am not sure if your issue is not being able to enter into a blank table, or if the code is throwing off an error. As I mentioned, I have tried several scenarios with a test database, and I cannot duplicate a problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.