Jump to content


Photo

Insert Ignore??


  • Please log in to reply
3 replies to this topic

#1 barkster

barkster
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts

Posted 06 September 2006 - 05:52 PM

I have table called email with fields EmailID(Primary Key), Email, and Remove.  I only want to insert new records where Email does not exists.  I can't figure out how to do it.  I changed Email to a primary key but I guess because it is text this insert doesn't work.  How can I insert only if Email is unique?  Thanks

INSERT IGNORE INTO Emails (Email,Remove) VALUES ('test@domain.com','1')

#2 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 06 September 2006 - 06:00 PM

rather than changing email to a primary key, you just need to make it a UNIQUE constraint. then, the SQL will return an error if you try to insert one that already exists. otherwise, you need to check it with PHP first:
<?php
$sql = mysql_query("SELECT * FROM emails WHERE email = '$email'");
if (mysql_num_rows($sql) == 0) {
  // no records yet, so insert it
  mysql_query("INSERT INTO emails (email, remove) VALUES ('$email', '1')");
}
?>

hope this helps
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#3 barkster

barkster
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts

Posted 06 September 2006 - 06:11 PM

Thanks that is actually how I was doing it just thought I could do it somehow with one statement.  Thanks!

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 06 September 2006 - 06:15 PM

I don't see why INSERT IGNORE wouldn't work, once you had the UNIQUE index.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users