CODEWITHSUNDEEP
phpmysqlsqldatabase
help
help

Reputation: 23

checking if record already exists before inserting ERROR

$sql = "INSERT INTO `diver` (`firstname`, `lastname`, `registrated`)
        SELECT '$fname', '$lname', now()
        WHERE NOT EXISTS (SELECT firstname, lastname FROM `diver` WHERE diver.firstname = '$fname' AND diver.lastname = '$lname')";

i get this error: 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 EXISTS (SELECT firstname, lastname FROM diver WHERE diver.firstname =' at line 3

the code works perfectly fine in localhost, but when in my online database it's not. is there any other solution that will do the exact same thing?

Upvotes: 1

Views: 379

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Learn to use parameters. In MySQL, you need a from to use where. One method is to use from dual (adopted from Oracle).

I tend to go for:

INSERT INTO `diver` (`firstname`, `lastname`, `registrated`)
    SELECT fname, lname, now()
    FROM (SELECT ? as fname, ? as lname) x
    WHERE NOT EXISTS (SELECT 1
                      FROM `diver` d
                      WHERE d.firstname = x.fname AND
                            d.lastname = x.lname
                     );

I assume you intend not exists, because inserting rows with duplicate values is not usually what one wants to do.

Actually, this isn't the best approach. Instead, create a unique index/constraint on the name columns:

create unique index unq_diver_firstname_lastname on diver(firstname, lastname);

insert into `diver` (`firstname`, `lastname`, `registrated`)
     values (?, ?, now())
     on duplicate key update firstname = values(firstname);

This does nothing when a duplicate is inserted.

Also note that names are not necessarily unique, so checking for uniqueness of names might not be a good idea in general.

Upvotes: 1

Related Questions