SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    How to make an Insert IF a condition is met

    Hi,

    How does one write an IF statement in MySQL which IF will make an INSERT if certain condition is met.

    For example say we have a Table of users with fields email & q_answered = 'no' or 'yes'

    So we want to make an INSERT into this TABLE only if this user has NOT been sent a Notice over the last 3 days.

    The IF INSERT then would look logically like:

    IF (COUNT(id) FROM qa_history WHERE date_added > (DATE_SUB(CURDATE(), INTERVAL 3 DAY)) AND email = '$email' AND q_answered = 'no' < 3) THEN INSERT INTO qa_email_history (email = '$email' )

    FYI, we currently get the above job done by having a Php code that loops through all the emails in the list to make sure the above condition is met before a new INSERT is made, but this is killing the server. So much faster would be to make the IF & INSERT into one statement.

    ThanX,

    Anoox search engine volunteer

    www.anoox.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WorldNews View Post
    ... having a Php code that loops through all the emails
    dude, get the php to do COUNT instead, just like your suggested IF

    looping is so last millenium

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Really!

    So you are saying that a MySQL command that has an IF statement which IF would then lead to or not to an INSERT is a "so last millenium"?
    I did not know that MySQL IF has become so detested?
    Well, in meanwhile I have come up with a equally nice solution to reduce load on the server while getting the job done.
    But I was hoping, at least I thought, that a MySQL IF then INSERT would be a nice solution.
    Oh well.



    Quote Originally Posted by r937 View Post
    dude, get the php to do COUNT instead, just like your suggested IF
    looping is so last millenium

    Anoox search engine volunteer

    www.anoox.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WorldNews View Post
    I did not know that MySQL IF has become so detested?
    i think you misunderstood

    you said your php was looping over the emails

    i sugggested doing a COUNT instead of looping

    totally different, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •