CODEWITHSUNDEEP
phpmysqlunique
kusanagi
kusanagi

Reputation: 14614

mysql unique field

i have table with unique field uniq_field. what method adding is better- first check if record with unique value exists or simply add record and mysql will check by himself?

Upvotes: 0

Views: 804

Answers (3)

Your Common Sense
Your Common Sense

Reputation: 157897

it depends on the action you gonna take in case of duplicate
possible solutions can be shown in such a pseudo code

switch action
    case "do nothing":
        INSERT IGNORE
    case "delete existing then add new one":
        REPLACE INTO
    case "update existing":
        ON DUPLICATE UPDATE
    default:
        select first and then apply necessary logic

Upvotes: 2

S L
S L

Reputation: 14318

Well for this table

+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id     | int(11) | NO   | PRI | 0       |       |
| unique | int(11) | NO   | UNI | NULL    |       |
+--------+---------+------+-----+---------+-------+

This code returns false

$result = mysql_query("insert into tab1 values(1, 1), (2, 1)");

echo ($result)?($result):"false";

While this code returns 1

$result = mysql_query("insert into tab1 values(1, 1), (2, 2)");

echo ($result)?($result):"false";

It seems that if you want to display message, then you should check for values in you database and display an error. If you don't want to display what actually caused and error then you can directly query.

Upvotes: 1

arnorhs
arnorhs

Reputation: 10429

it depends on how you want to handle it in your code.

If you just want to make sure that element exists, you can do what's called a insert - on duplicate key update - that means that the row gets replaced if it's already present - but if it's not, it gets updated.

More on that topic here: http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

Upvotes: 1

Related Questions