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')");
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'
...WHERE NOT EXISTS (SELECT id FROM ...
why not run normal insert, it will fail if row exists?
Your query is
"INSERT INTO ref_users (id) VALUES ('$id') WHERE NOT EXISTS (SELECT FROM cw_users where id='$id')"
You can't use were case in insert query . you can use normal insert .