Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • Last Modified:

Using cfloop and cfquery

I need a way to query a database and over and over until a new record is added.  The record will be added by another application, so my app just needs to wait until the new record is created, then immediately redirect my user to a page where they can edit that new record.

I think I'm on the right track with the code I have and the help I've received from the Macromedia forums.  But I don't know how to run the query over and over...or if there is another way to do this.  Setting up a scheduled task is not the answer as this could happen at any time.

Here's the code I have so far:
<!--- User clicks a button triggering an update to the user table, then an outside app see this new update to the user
table, and ads a new record to the activity table --->

<cfif IsDefined("FORM.submit")>
  <cfquery datasource="slx">
    UPDATE sysdba.USERINFO SET TICKETNOTIFY = 'T', ACCOUNTINGUSERID = '#FORM.CONTACTID#' WHERE USERID = '#FORM.userid#'
  </cfquery>
         
  <!--- There is a field in the ACTIVITY table called CREATEDATE that is the date on which the record gets created.  I
  figured, if I set a variable for current time, I can then some how check the CREATEDATE against the currentTime variable, thus knowing a new record exists. --->

  <cfset currentTime = Now()>

  <!--- Not sure what to do with this.  I need to query the ACTIVITY table and see if a new record has been created since "currentTime" --->
         
  <cfquery name="activCnt" datasource="slx">
    SELECT ACTIVITYID, CREATEDATE
    FROM sysdba.ACTIVITY
  </cfquery>

  <!--- Once I do determine that a new record has been completed, I redirect my user to the edit form where they can edit this newly created record. Don't know if this is correct either.--->
         
  <cfif #activCnt.CREATEDATE# GT #currentTime#>
    <cflocation url="#ulcURL#slx/activEdit.cfm?user=#url.user#&activityID=#activCnt.ACTIVITYID#">
  </cfif>
         
</cfif>
0
IndyB
Asked:
IndyB
1 Solution
 
danrosenthalCommented:
You should use a screen refresh after an arbitrary amount of seconds (say 2 for instance).  So every 2 seconds the screen will reload until the record you are looking for is found at which point it will redirect using cflocation.

You can use the META tag to refresh or use CFHEADER like this:
<CFHEADER name="refresh" value="2; url=pagename.cfm">
0
 
anandkpCommented:
hi what u cld also do is :

have a query on ur page to chk for the status of "TICKETNOTIFY" - if its "T" then redirect else do nothing !

<CFHEADER name="refresh" value="2; url=pagename.cfm">
put this in ur head tag of ur page & change ur code on the page to :

<cfquery name="x" datasource="slx">
   Select sysdba.USERINFO where TICKETNOTIFY = 'T'
And USERID = '#FORM.userid#'
 </cfquery>

<cfif x.recordcount gt 0>
    redirect the user ...
</cfif>

K'Rgds
Anand
0
 
danrosenthalCommented:
anandkp...
I don't think that will work.
IndyB is first setting the value TICKETNOTIFY = 'T' in the USERINFO table to have some other process add a record.

Also, for clarification the CFHEADER tag can go anywhere on the page, it doesn't have to be put in the HEAD tag.

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
anandkpCommented:
yeah i wasnt impying abt teh CFHeader tag to be ONLY in head tag - its a good practice to keep it there - so i just mentioned it.

Also the query part is just a logical explanation of what he cld do ... i just took up a eg to make him understand - i hope he gets it :)

PS : pls dont think i am trying to overrule ur comment ... no offence intended ... excuse me if it has caused ne-problems to u

Cheers
Anand
0
 
hartCommented:
i would suggest that you use triggers.

after update, after insert.

Then you don't have to refresh the page and send the user directly to the next page as the triggers will be fired as soon as the update occurs.

hope this helps,

if you require help in creating triggers then let me know.

Also you could go with the page refreshing funda if required.

Regards
Harish(Hart)
0
 
anandkpCommented:
hello - ne-luck yet ???

K'Rgds
Anand
0
 
mrichmonCommented:
No comment has been added lately, so it's time to clean up this question.
I will leave the following recommendation in the Cleanup topic area:

Accept hart

Please leave any comments here within the next four days.

mrichmon
EE Cleanup Volunteer
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now