2 Replies Latest reply on Sep 24, 2013 11:04 AM by rickaclark54

    Is using an if statment from within a query my best option?

    rickaclark54 Level 1

      I have a mailer that sends out a word of encouragement every morning to several of my clients’ employees. The messages are pre-written. Any client can actually change the message before for that day without affecting the message being sent out to my other clients. I do this by sending the clients the message for the day in advance.

       

      Once a client edits the message it is saved to another table called edits. When the message for the day goes out, I need to loop through the edits table first, if the table is not null for that day, it sends the message from the edits table. If the edits table is null, the message is pulled from the daily table.

       

      Can I do an if statement from within the query, if the table is null for that day then query the second table called daily for the message for that day? 

        • 1. Re: Is using an if statment from within a query my best option?
          p.sim Level 3

          Might be like this:

           

          <!--- Get message from edits table --->

          <cfquery name="getMessage">

                    SELECT message

                    FROM edits

                    WHERE clientID = <cfqueryparam value="#attributes.clientID#" cfsqltype="cf_sql_integer">

                              AND scheduledDate = <cfqueryparam value="#dateFormat(now(), 'mm/dd/yy')#" cfsqltype="cf_sql_date">

          </cfquery>

           

           

          <!--- If there is no record in edits table, then get message from daily table --->

          <cfif NOT getMessage.recordCount>

                    <cfquery name="getMessage">

                              SELECT message

                              FROM daily

                              WHERE clientID = <cfqueryparam value="#attributes.clientID#" cfsqltype="cf_sql_integer">

                    </cfquery>

          </cfif>

           

           

          <cfoutput>#getMessage.message#</cfoutput>

          • 2. Re: Is using an if statment from within a query my best option?
            rickaclark54 Level 1

            Thanks, your advicse was very helpful. I didn't think of using the recordcount.