15 Replies Latest reply on Mar 7, 2007 4:24 PM by JohnGree

    correct sintax

    JohnGree Level 1
      hi i am trying to get my action page to send back if the form element is more than a session,

      this is not working, i have tried a few different ways but cant get it right, can someone help

      <CFIF (form.totalPlayers) greater than '#session.TotalSMSLeft#'>
      <CFLOCATION url="SMS.cfm?Credit=0">
      </CFIF>
        • 1. Re: correct sintax
          Level 7
          1) where is this code located? #form# scope is only available after form
          has been submitted
          2) do not use () around your form var
          3) if both values (in form and session) are integers/numbers, then do
          not use '' around your session var
          4) no need to use ## around your session var inside cfif either
          5) "gt" is short for "greater than"...

          --

          Azadi Saryev
          Sabai-dee.com
          http://www.sabai-dee.com
          • 2. Re: correct sintax
            JohnGree Level 1
            OK THANKS

            i just worked out what i need, i have 5 text boxes which get submitted to my action page

            i need to know how many of them have been filled in ie

            textbox1 = 666666
            textbox2= 666666
            textbox3 = 666666
            textbox4 =
            textbox5 =

            the varibale i can then use would be 3

            how can i do this
            • 3. Re: correct sintax
              Level 7
              since textboxes ALWAYS have a value (empty string if nothing is typed
              in) you will have to check for the LENGTH of the value, i.e.
              <cfif len(trim(form.textbox1)) gt 0>...

              if you want to dynamically check for all textboxes that have len(value)
              gt 0 and set a var to the number of them that have value, then you can
              loop through the form.fieldnames list (which holds names of all form
              elements of the submitted form), and, if your textboxes in question all
              have SIMILAR, IDENTIFIABLE names (i.e. textbox1, textbox2, etc), you can
              do (remember to substitue sample values for your own and NOT to use []:

              <cfset yourvaryouwanttoset = 0>

              <cfloop list="#form.fieldnames#" index="fieldName">

              <cfif left(fieldName, [number of letters in the common part of the name
              of your textboxes; using the example above that would be 7]) is "[common
              part of the name of your textboxes; using example above that would be
              'textbox']" AND len(trim(evaluate("form." & fieldName))) gt 0>
              <cfset yourvaryouwanttoset = yourvaryouwanttoset + 1>
              </cfif>
              </cfloop>

              plain text explanation:

              set your variable to 0 first;
              loop through the list of submitted form fieldnames;
              if left X characters of a filedname match left X chars of the name of
              your textbox field AND the submitted value of that field is not blank
              then add 1 to your variable;
              by the end of the loop your var will hold the value equal to number of
              submitted filled-out textboxes.

              hope this helps

              --

              Azadi Saryev
              Sabai-dee.com
              http://www.sabai-dee.com
              • 4. Re: correct sintax
                JohnGree Level 1
                ok i think i am getting there

                i have the code below, each form field is named phone 1,2,3,4,5

                just getting a error, is the code correct,

                many thanks

                <cfset test = 0>

                <cfloop list="#form.Phone#" index="Phone">

                <cfif left(Phone, [5]) AND len(trim(evaluate("form." & Phone))) gt 0>
                <cfset test = test + 1>
                </cfif>
                </cfloop>
                • 5. Re: correct sintax
                  Level 7
                  ok, you are almost there... just a few things to change:

                  1) form.fieldnames is a standard comma-delimited list of all field names
                  in a submitted form; if you do a <cfdump var="#form#"> on an action page
                  of any form you will see filednames in the list of fileds. you thus have
                  to loop trough #form.fieldnames# list, not the #form.phone#...

                  2) let's just rename your loop index from "Phone", which is same as your
                  textbox mane, to, say, iPhone; just to be on the safe side it does not
                  screw any things up later...

                  4) as i said before, do NOT use square brackets [] ! i entered them only
                  to indicate that you should replace my text in tem with your actual
                  values/names...

                  3) just left(iPhone, 5) does not really do anything, does it? you need
                  to check that left(iPhone) equals the first 5 characters of the name of
                  your textbox...

                  so your correct code should be:

                  <cfset test = 0>

                  <cfloop list="#form.fieldnames#" index="iPhone">

                  <cfif left(iPhone, 5) is "phone" AND len(trim(evaluate("form." &
                  iPhone))) gt 0>
                  <cfset test = test + 1>
                  </cfif>
                  </cfloop>

                  --

                  Azadi Saryev
                  Sabai-dee.com
                  http://www.sabai-dee.com
                  • 6. correct sintax
                    JohnGree Level 1
                    ok many thanks that works, now i have a input to my database which i had five times on my action page, so can i now just add this inside my loop?


                    <cfmail
                    to = "craig@test.com" query="Send"
                    from = "test"
                    subject = "SMS">
                    email sent
                    </cfmail>

                    <cfif left(form.Phone,1) EQ "0">
                    <cfset PhoneNo =removeChars(replace(form.Phone, " ", "", "all"),1,1)><cfelse>
                    <cfset PhoneNo =form.Phone>
                    </cfif>

                    <cfset todayDate = Now()>

                    <cfquery datasource="#application.ds#">
                    INSERT INTO SMS_Records
                    SET
                    DateOfSMS = #todayDate#,
                    SentTo = '#PhoneNo#',
                    ClubID = '#session.ClubLogin#',
                    Cost = 1,
                    RCODE = '#form.CODE#'

                    </CFQUERY>
                    • 7. Re: correct sintax
                      Level 7
                      And my standard suggestion to remove all need for the evaluate function,
                      well not quite all need, but 99% of the time.

                      len(trim(form['form' & iPhone])) is the same as
                      len(trim(evaluate("form." & iPhone)))

                      Most of prefer the first syntax for readability. It used to be faster,
                      but in MX days that is not so much the truth anymore.

                      • 8. Re: correct sintax
                        Level 7
                        JohnGree wrote:
                        > ok many thanks that works, now i have a input to my database which i had five
                        > times on my action page, so can i now just add this inside my loop?
                        >
                        >
                        > <cfmail
                        > to = "craig@test.com" query="Send"
                        > from = "test"
                        > subject = "SMS">
                        >
                        > </cfmail>
                        >
                        >
                        > <cfset todayDate = Now()>
                        >
                        > <cfquery datasource="#application.ds#">
                        > INSERT INTO SMS_Records
                        > SET
                        > DateOfSMS = #todayDate#,
                        > SentTo = '#PhoneNoB#',
                        > ClubID = '#session.ClubLogin#',
                        > Cost = 1,
                        > RCODE = '#form.CODE#'
                        >
                        > </CFQUERY>
                        >

                        Sorry for a delay in getting back to you...
                        Yes, technically you can add the insert query (and the cfmail if needed)
                        into that loop instead of having it inside another loop...
                        But there are a few things not right with your insert query...
                        a) your SQL syntax in that query is for an UPDATE query, not INSERT:
                        correct INSERT synatx is INSERT INTO tablename (column1name,
                        column2name, ...) VALUES (value1, value2, ...)
                        b) i suggest you use CreateODBCDate(Now()) function to get the value for
                        your DateOfSMS field. though not required, this makes the date more
                        database-independent, i.e. it create s a date in a format that almost
                        any db will recognize as date.


                        --
                        Azadi Saryev
                        Sabai-dee.com
                        Vientiane, Laos
                        http://www.sabai-dee.com
                        • 9. Re: correct sintax
                          JohnGree Level 1
                          Ok thanks, i have that all working now,

                          could you please help me on 1 other problem i have, which i need quite urgent.

                          Hi i have a scheduled email system, where a user selects a time and date, which gets converted to my server time and date, and then gets put into my database with the email message,

                          the server then sends out emails if the time and date is less than the current server datetime.

                          what i need to do now is add a daily and weekly tick box to the form, but i am not sure how i should use my database to do this?

                          so at the min i have a table called appoint_table which has

                          Appt_ID (unique)
                          date time (message was created)
                          server time (if this is less than the current server time the email gets sent)


                          so then if i have a table called freqency_table with

                          frequency_id
                          every integer
                          time_unit text (week, minute, hour, etc)

                          what would every integer be?

                          and how would i link this to my appoint_table

                          many thanks for all your help
                          • 10. Re: correct sintax
                            Level 7
                            JohnGree wrote:
                            > Hi i have a scheduled email system, where a user selects a time and date,
                            > which gets converted to my server time and date, and then gets put into my
                            > database with the email message,
                            >
                            > the server then sends out emails if the time and date is less than the current
                            > server datetime.
                            >
                            > what i need to do now is add a daily and weekly tick box to the form, but i am
                            > not sure how i should use my database to do this?
                            >
                            > so at the min i have a table called appoint_table which has
                            >
                            > Appt_ID (unique)
                            > date time (message was created)
                            > server time (if this is less than the current server time the email gets sent)
                            >
                            >
                            > so then if i have a table called freqency_table with
                            >
                            > frequency_id
                            > every integer
                            > time_unit text (week, minute, hour, etc)
                            >
                            > what would every integer be?
                            >
                            > and how would i link this to my appoint_table
                            >
                            > many thanks for all your help
                            >

                            let's start with your table arrangements then:

                            to link your tables i suggest you add an Appt_ID field to your frequency
                            table. presumably, your user can type in the message to send, select a
                            date until which to send this message (? not sure about this part...)
                            and select how often to send it - i.e. every 2 hours - this part (2 and
                            hours) you will store in your frequency table together with Appt_ID
                            which will uniquely link the two tables.
                            now, obviously, you will have to get the Appt_ID of the message entered
                            to insert it into your frequency table.
                            so, on the action page of your form, first insert the data into the
                            appoint_table, then get the Appt_ID of the inserted record. this bit is
                            tricky without any other unique identifier for each record in the
                            appoint_table...
                            you will either have to enclose the insertion of data and retrieval of
                            id into a transaction - then use a select MAX(Appt_ID) to retrieve the
                            latest inserted id...
                            or you will have to modify your appoint_table to include another column,
                            say called "uuid", and insert a uuid into it created with CreateUUID()
                            cf function
                            i will elaborate on the second approach as it is more viable, i believe:

                            your appoint_table structure will be:
                            Appt_ID (unique)
                            date time (message was created)
                            server time (if this is less than the current server time the email gets
                            sent)
                            uuid (unique uuid)

                            on the action page the insertion of user-entered data code will be:
                            <cfset appt_uuid = CreateUUID()>
                            <cfquery name="insertAppt" datasource="whatever">
                            INSERT INTO appoint_table (date, server, uuid) VALUES
                            ('#form.whateverfieldnameyouuse#', '#form.whateverfieldnameyouuse#',
                            '#appt_uuid#');
                            </cfquery>

                            then to retrieve the created record's Appt_ID (to be inserted into the
                            Appt_ID field in the frequency table) use:
                            <cfquery name="getApptID" datasource="whateverDSN">
                            SELECT Appt_ID FROM appoint_table WHERE uuid = '#appt_uuid#';
                            </cfquery>
                            <cfset newApptID = getApptID.Appt_ID>

                            then you can insert #newApptID# into the Appt_ID field and other data
                            into other fields in your frequency table...


                            now, how to set up your scheduler to send out emails only the selected
                            number of times and at selected intervals is another issue... you will
                            probably have to add a few more fields to your tables to record time and
                            times emails have been sent to keep track of that... or have separate
                            scheduled tasks for each time_unit... or something like that. too
                            complicated to include in this post... i will have to think about that
                            one more...

                            hope this helps

                            --

                            Azadi Saryev
                            Sabai-dee.com
                            http://www.sabai-dee.com
                            • 11. Re: correct sintax
                              JohnGree Level 1
                              Hi Azadi

                              Many thanks for this, i will put this together now, if you can work out how i should send out the emails for each hour, week etc that would be great

                              Many thanks once again
                              • 12. Re: correct sintax
                                JohnGree Level 1
                                Hi i have now setup my database as below

                                frequency_table
                                Appt_ID = G6pCf
                                Time_Unit = Weekly, monthly etc

                                appoint_table
                                GroupView = G6pCf
                                Message = email message here
                                ServerSMS = datetime that the message gets sent ou the first time

                                so all i need now is the code to send the emails out, i have this code below already

                                many thanks
                                • 13. Re: correct sintax
                                  Level 7
                                  i am still thinking how to code your scheduler, but meanwhile...

                                  the following does not look right, especially the first INNER JOIN: you
                                  are not really joining anything in it! what you have there should
                                  probably be in your WHERE clause...

                                  > <CFQUERY datasource="#application.ds#" Name="GetSch">
                                  > SELECT *
                                  > FROM SMS_Players_Table
                                  >
                                  > INNER JOIN appoint_table
                                  > ON ServerSMS <= #CreateODBCDateTime(Now())# AND ServerSMS <> ''
                                  >
                                  > INNER JOIN SMS_Clubs_Table
                                  > ON appoint_table.LoginID = SMS_Clubs_Table.ClubID
                                  >
                                  > WHERE appoint_table.App_ClientID = SMS_Players_Table.PlayerID
                                  > </cfquery>

                                  not sure what to join SMS_PlayersPtable and appoint_table on since you
                                  never posted structure of your SMS_Players_Table...

                                  --
                                  Azadi Saryev
                                  Sabai-dee.com
                                  Vientiane, Laos
                                  http://www.sabai-dee.com
                                  • 14. correct sintax
                                    JohnGree Level 1
                                    the sms_players_table just hold info like the email addrress to be sent to,

                                    appoint_table.App_ClientID = SMS_Players_Table.PlayerID

                                    so this matches the record of the player to get the email address,

                                    i have chabged the query now as you surgested, do you know how long the scheduler code will take?
                                    is there any thing i can do.

                                    many thanks
                                    • 15. correct sintax
                                      JohnGree Level 1
                                      Hi

                                      going back to my orginal post, i have a problem, can you help, i have been playing around with but just cant get it quite right.

                                      i need to run a loop through the code below if

                                      1. evaluate to see if the form element is empty
                                      2. if not empty get rid of spaces and leading zero
                                      3. once that is done loop through this code.

                                      i have tried this

                                      <cfloop list="#form.fieldnames#" index="iPhone">
                                      <cfif left(iPhone, 5) is "phone" AND len(trim(evaluate("form." & iPhone))) gt 0>
                                      <cfset PhoneNo =removeChars(replace(iPhone, " ", "", "all"),1,1)><cfelse>
                                      <cfset PhoneNo =iPhone>