SQL Insert Ignore Not Working

Question

I am very sorry yes I know this has been asked before but when I look at the post it looks like I am doing everything right yet it isn't working.

So I want to make a sql insert only if that $domain does not exist in the database, I don't mind if multiple domains have the same IP.

Here is my current sql query which doesn't add any domains and IPs to the database.

mysql_query("INSERT INTO domains (domain, ip) VALUES ('$domain', '$ip') WHERE NOT EXISTS (SELECT FROM domains where domain='$domain')");

if you don't mind if multiple domains have the same ip, then... what's the problem? just insert — Eevee, Feb 10 '14 at 06:08
Because I want to store multiple domains on same IP but not multiple domains. For example one shared hosting server with the IP 1.1.1.1 might have 3 domains running on it. But I don't want the same IP for the same domain in the DB — Orca, Feb 10 '14 at 06:12
OK, so make a unique index on `domain`, and `INSERT IGNORE INTO domains...`. also by the way you are way open to sql injection, which means a bored nerd could delete your entire database; `mysql_query` is super deprecated and you should either use PDO or mysqli with bound parameters — Eevee, Feb 10 '14 at 06:14
I see, I will fix the sqli. Also how would I make the Insert Ignore statement? — Orca, Feb 10 '14 at 06:15
`INSERT IGNORE INTO domains (domain, ip) VALUES (:domain, :ip)`. there's also a `REPLACE` and a handful of other alternatives, depending on what you want to happen when there's a duplicate domain. — Eevee, Feb 10 '14 at 06:17
@Eevee ok with INSERT IGNORE INTO domains (domain, ip) VALUES (:domain, :ip) the lines are now being added to the DB. But if I insert the same duplicate values again they will get added to the DB instead of being ignored — Orca, Feb 10 '14 at 06:19
you need to create a unique index on `domain`. `CREATE UNIQUE INDEX domains_domain ON domains (domain);` -- http://dev.mysql.com/doc/refman/5.5/en/create-index.html — Eevee, Feb 10 '14 at 06:20

zzlalani · Answer 1 · 2014-02-10T06:12:23.093

0

You also need to apply SELECT to domain

INSERT INTO domains (domain, ip)
SELECT * FROM
(SELECT ('$domain', '$ip')) AS tmp
WHERE NOT EXISTS (
    SELECT * FROM domains where domain='$domain'
);

edited Feb 10 '14 at 06:12

answered Feb 10 '14 at 05:58

zzlalani

19,534
16
41
72

Hello sir, thank you for reply just tried that and not working. – Orca Feb 10 '14 at 06:05
Ok just tried mysql_query("INSERT INTO domains (domain, ip) VALUES ('$domain', '$ip') SELECT domain, ip FROM domains WHERE NOT EXISTS ( SELECT null FROM domains where domain='$domain' )"); and not working – Orca Feb 10 '14 at 06:07
you can't use `VALUES` and `SELECT` at the same time; that doesn't make any sense. they're different ways of specifying what rows to insert. – Eevee Feb 10 '14 at 06:09
i don't entirely understand what you're trying to do here; do you want to only avoid duplicated _pairs_ of `(domain, ip)`? or only one ip per domain at all? – Eevee Feb 10 '14 at 06:11
you could just `SELECT :domain, :ip`; no need to wrap it in a subquery. also `WHERE :domain NOT IN (SELECT domain FROM domains)` seems more natural and possibly easier to optimize. but really this should just be done with a unique index – Eevee Feb 10 '14 at 06:15
What's a unique index? Thank you for the help Eevee – Orca Feb 10 '14 at 06:16
that's a pretty basic database concept; you might want to step back and read a little deeper on database design. for mysql, explained here: http://dev.mysql.com/doc/refman/5.5/en/create-index.html – Eevee Feb 10 '14 at 06:18

score 0 · Accepted Answer · edited May 23 '17 at 11:49

0

Here's a long solution but good for columns that don't have UNIQUE attribute.

INSERT INTO your_table (your_column)
SELECT 'your_value' FROM (SELECT 1) t
WHERE NOT EXISTS
    (
        SELECT your_same_column
        FROM you_same_table
        WHERE your_same_column = 'your_same_value'
    );

Got the solution here

But if your column in question has a UNIQUE attribute, then you could just do this short method.

INSERT IGNORE INTO your_table(your_column) VALUES('your_value');

edited May 23 '17 at 11:49

Community

1
1

answered Feb 10 '14 at 06:27

Craftein

762
5
10

1

`FROM DUAL` should work just as well, if you need a `FROM` clause at all. also that `NOT EXISTS` will take a full table scan if you don't have an index... and if you have an index you don't need to do it :) – Eevee Feb 10 '14 at 06:34
Eevee I am not sure how to set an Index I researched the link you said and tried making the following command which is even better if it worked but it adds duplicates. mysql_query("REPLACE INTO domains (domain, ip) VALUES ('$domain', '$ip')"); . TY Craftein, currently trying this out – Orca Feb 10 '14 at 06:41
i gave you the query to create a unique index as a comment on your question; just paste that into a mysql client. – Eevee Feb 10 '14 at 22:57

SQL Insert Ignore Not Working

2 Answers2