CODEWITHSUNDEEP
phpmysqlsqlinsert
radleybobins
radleybobins

Reputation: 981

Checking for existing entry before inserting data

I am trying to insert new clients only if they don't already exist. I am having problems I think with the SQL statement, but I wanted to give context in the PHP too:

   $sql = "INSERT INTO clients
        (`studentEmail`, `studentPassword`, `parentEmail`, `parentPassword`,
        `studentFirstName`, `studentLastName`, `studentPhone`, `parentFirstName`,
        `parentLastName`, `parentPhone`, `school`)
         VALUES ('$studentEmail', '$studentPassword', '$parentEmail',
        '$parentPassword', '$studentFirstName', '$studentLastName',
        '$studentPhone', '$parentFirstName', '$parentLastName', '$parentPhone', '$school')
        SELECT studentEmail
        FROM clients
        WHERE not exists (select * from clients where studentEmail == '"$studentEmail"')";

Upvotes: 1

Views: 2132

Answers (4)

Indranil
Indranil

Reputation: 2471

You should check whether the email exists before inserting the data.

$check = mysql_query("SELECT studentEmail FROM clients WHERE  studentEmail = '{$studentEmail}';");

if (mysql_num_rows($check) == 0) {
    // insert
}

Upvotes: 2

Ted Hopp
Ted Hopp

Reputation: 234795

If you have a unique index on the email field, then you can just do INSERT IGNORE ... and inserts corresponding to existing emails will be quietly discarded.

Upvotes: 0

fge
fge

Reputation: 121702

You can use the MERGE statement:

merge into clients on (select 1 from clients where studentEmail = blabla)
when not matched then insert blabla

(the merge statement syntax will vary according to your RDBMS, so look at the doc for your particular RDBMS implementation)

Upvotes: 0

Andomar
Andomar

Reputation: 238048

In an insert statement, select and values are mutually exclusive. Instead of:

insert table1 (col1, col2, col3, ...)
values (1, 2, 3, ...)
select col1
where  not exists (...)

Try:

insert table1 (col1, col2, col3, ...)
select 1, 2, 3, ...
where  not exists (...)

Upvotes: 3

Related Questions