TotallyMe
asked on
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?
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?
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
Just add to the select query
ORDER BY date desc LIMIT 1
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`
)
ALTER TABLE `myTableName` ADD UNIQUE (
`domain` ,
`rank`
)
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
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
ASKER
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
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
ASKER
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
"WHERE NOT EXIST"
rather than
"WHERE NOT EXISTS"
Fixed it but I'm still getting same error
ASKER
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.
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.
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
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
ASKER
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
#1267 - Illegal mix of collations (utf8_unicode_ci,COERCIBLE
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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"
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"
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.
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.
ASKER
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.
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.
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.
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.
ASKER
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;
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;
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.
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.
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.
$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);