如果不存在则插入数据

Question

I have two tables cw_users and ref_users, both have a column named id.

I'm using ISAM so can't use a foreign key. So now I wanted to insert id from cw_users into ref_users if it didn't exist.

This is what I did, but didn't help:

$id = $_SESSION['id'];
$ref_code=md5($id);

mysql_query("INSERT INTO ref_users (id) VALUES ('$id') WHERE NOT EXISTS (SELECT FROM cw_users where id='$id')"); 

Answer 1

The correct syntax is INSERT IGNORE INTO

INSERT IGNORE INTO ref_users (id)
VALUES ('$id')

It will insert if the value does not exist, and ignore the statement if it does.

Note that this will only work if id is the Primary Key

EDIT: It seems from your comments that you would be much better off using ON DUPLICATE KEY UPDATE.

Try this query

INSERT INTO ref_users(id, ref_code)
VALUES ('$id', '$ref_code')
ON DUPLICATE KEY UPDATE
ref_code = '$ref_code'