In this example we’re going to pretend we have a newsletter signup table which stores a name and an email address. The user has already submitted their details which are stored in $name and $emailAddress. If the table already contains $emailAddress we don’t want to perform the insert.

INSERT INTO tblNewsletter SELECT '$name', '$emailAddress' FROM DUAL WHERE NOT EXISTS (SELECT * FROM tblNewsletter WHERE email = '$emailAddress')

Which when parsed by PHP looks like:

INSERT INTO tblNewsletter SELECT 'Joe Bloggs', 'joe@bloggs.com' FROM DUAL WHERE NOT EXISTS (SELECT * FROM tblNewsletter WHERE email = 'joe@bloggs.com')

How it works

The key to this is the EXISTS statement. It allows us to alter the result of a WHERE depending on whether its subquery returns a result set or not (true or false).

The one tricky part with this method is that EXISTS can’t be used directly in an INSERT query, you have to run it from a SELECT. So what we do is use a SELECT with hard-coded results (notice I used PHP variables and not field names) which passes those details to the INSERT, but only if the WHERE condition is met (i.e. joe@bloggs.com isn’t already in the table)

What’s DUAL?

DUAL is a fake table in MySQL (and Oracle too I believe) which is useful when you’re not really querying a table but need to have proper syntax.

How about with certain fields only?

In the example above the query is rather greedy; we don’t specify any fieldnames and just grab and chuck as much data as we can get our hands on. So what if the table you’re inserting into has an ID field, which is an auto-incrementing number? You could just hard code the value NULL into the query but this is a bit naughty.

Instead we can easily specify which fields we’re inserting into just like with a normal insert query.

INSERT INTO tblNewsletter name, email VALUES SELECT '$name', '$emailAddress' FROM DUAL WHERE NOT EXISTS (SELECT * FROM tblNewsletter WHERE email = '$emailAddress')

Just don’t forget to make sure that the number of specified fields matches the amount of data you’re passing to the INSERT. I’ve not tried it without the VALUES part, but it works for me so I’m not going to fix what isn’t broken.

I hope this little insight helps, if it does let me know – my ego needs feeding!

Spread the word

If you like what you've read, help spread the word on Twitter, Digg, or any of your favourite social sites. Knowledge is power.

About the author

Jonathan Phillips is the founder and main author here on Division by Zero. A PHP developer by trade, Jonathan spends his days building and marketing websites and the rest of the time coming up with ideas for websites and businesses he hasn't got time to implement.