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