Solved

coldfusion infinity

Posted on 2009-05-13
11
274 Views
Last Modified: 2013-12-24
I'm still building my testing application.  There have been a few changes in the scope of the project and I am returning for your expert advise.

Here's the situation:

A test has infinite chapters.
A chapter has infinite questions.

When a test taker enters the site, they sign in to their profile page.  This page needs to list the chapters that have been passed, and the ones that are available to take.

I'm wondering when and best way to call data from mysql db, and weather or not to put the past/available questions into the session, or individual page requests.  

Would there be any advantage to doing some of the "heavy lifting" by the mysql server?

Is that a clear enough explanation of what i need to do?

Thanks for your wisdom!
0
Comment
Question by:Short-Order-Coder
  • 7
  • 4
11 Comments
 
LVL 27

Expert Comment

by:azadisaryev
ID: 24381579
if you have A LOT of different pages that will display the passed/not passed chapters, or if it is just one page, but it is requested by a user many times during the session (i.e. user is returned to that page after completing each new chapter) - then yes, stick that data into session scope structure after the user loggs into their profile. all you basically need to store in that structure is name and id of the chapters and a true/false completion flag (if the chapter has been completed or not by this user).

after a user completes a chapter, change the completion flag for that chapter in the session structure

[if your app is going to be under heavy load (lots of concurrent users) you better make sure your hardware is up-to-par for this...]

i do not really understand what 'heavy lifting' you are talking about having your db doing... what you have described does not look 'heavy' at all...

Azadi
0
 

Author Comment

by:Short-Order-Coder
ID: 24381606
Hey Azadi!  

If there are an infinite number of chapters, how/when would I call them and display them as a list?

Am I making this more difficult than it needs to be?

0
 
LVL 27

Accepted Solution

by:
azadisaryev earned 500 total points
ID: 24381741
on the page that displays the list of chapters, add code similar to this at the top of the page (assuming you have session management enabled for your application):

<cfif NOT structkeyexists(session, 'userchapters')>
  <cfquery name="getChapters" datasource="...">
  SELECT c.chapter_id, c.chapter_name, IFNULL(uc.chapter_id, 0, 1) AS chapter_completed
  FROM chapters c LEFT JOIN user_chapters uc ON c.chapter_id = uc.chapter_id
  WHERE uc.user_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#some-variable-that-holds-user-id#">
  ORDER_BY chapter_completed DESC, chapter_name
  </cfquery>
  <cflock scope="session" type="exclusive" timeout="20" throwontimeout="no">
    <cfset session.userchapters = structnew()>
    <cfoutput query="getChapters">
      <cfset session.userchapters[getChapters.chapter_id] = structnew()>
      <cfset session.userchapters[getChapters.chapter_id]['chaptername'] = getChapters.chapter_name>
      <cfset session.userchapters[getChapters.chapter_id]['completed'] = yesnoformat(getChapters.chapter_completed)>
    </cfoutput>
  </cflock>
</cfif>
<!--- display chapters list from session --->
<cfoutput>
<cfloop collection="#session.userchapters#" item="key">
  <p>#session.userchapters[key]['chaptername']# | <cfif session.userchapters[key]['completed']>completed<cfelse><a href="chapter.cfm?chapter_id=#key#>go to chapter</a></cfif></p>
</cfloop>
</cfoutput>

the above code checks to see if session var exists that holds the chapters, and if not runs the query to select chapters for the user and populates the session structure with query data. it then outputs the data from that session var as chapters list with either 'completed' text or a link to chapter's page next to each chapter name.

edit the code as necessary.

Azadi
0
 

Author Comment

by:Short-Order-Coder
ID: 24381900
Wow.  I need to break this down to understand it.  I'll post back with any specific questions, but this looks to be perfect!
0
 

Author Comment

by:Short-Order-Coder
ID: 24382094
I am not this advanced.  Hell, I didn't even know there was a bracket structure in CF or what cflock was really for.

Is there a "less correct" way of doing this?  The strain on the server isn't going to be that intense.

I know this goes against the point of asking an expert....
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:Short-Order-Coder
ID: 24382141
For example,

If I have a table called "passed" where I stored passedId, chapId, userId, passedDate;

And I had a table that had a list of all chapters,

Would there be anything that could be spun from that?

The number of chapters is not going to change more than once per year.  Does that help me any?
0
 

Author Comment

by:Short-Order-Coder
ID: 24382159
or if when a user signs up, a table were created that stored all of the chapterId's and a null value for not-passed?
0
 
LVL 27

Expert Comment

by:azadisaryev
ID: 24382304
you do not need to create a separate table for each user to store all chapters with passed/not passed value - your current db setup is more than adequate for what you want to do.

you can sure forgo storing user's chapters in a session structure - just run a query and output the results, without putting them into any persistent scope.

based on your explanation of your table structures, this query will be something like this:

  <cfquery name="getChapters" datasource="...">
  SELECT c.chapId, c.chapName, IFNULL(p.chapId, 0, 1) AS chapPassed, p.passedDate
  FROM chapters c LEFT JOIN passed p ON c.chapId = p.chapId
  WHERE p.userId = <cfqueryparam cfsqltype="cf_sql_integer" value="#some-variable-that-holds-user-id#">
  ORDER_BY chapPassed DESC, chapName
  </cfquery>

just stick it on top of the page that displays the chapters list for user.
then output it to display capters to user:

<cfoutput query="getChapters">
  <p>#chapName# | <cfif chapPassed>passed (#dateformat(passedDate, "dd-mmm-yy")#<cfelse><a href="chapter.cfm?id=#chapID#>proceed to chapter</a></cfif></p>
</cfoutput>

that's as simple as it gets...

Azadi
0
 

Author Comment

by:Short-Order-Coder
ID: 24382350
That makes much more sense to me (youre too smart).  

What is <cfqueryparam cfsqltype="cf_sql_integer" ?
0
 
LVL 27

Expert Comment

by:azadisaryev
ID: 24382438
from CFML Reference for <cfqueryparam> tag:
"Verifies the data type of a query parameter and, for DBMSs that support bind variables, enables ColdFusion to use bind variables in the SQL statement. Bind variable usage enhances performance when executing a cfquery statement multiple times."

you should use <cfqueryparam> tag, especially in your queries that use user-editable data, for these main reasons:
1) it creates 'bind variables' in your SQL, which make the query execute faster, especially if query caching is enabled
2) it helps protect your application against SQL Injections (because your DB knows never to execute a bind variable as if it was SQL code)
3) if can be helpful in verifying datatypes of data you pass to your query

link: http://cfquickdocs.com/cf8/?getDoc=cfqueryparam#cfqueryparam

if you do not have CFML Reference Manual, i strongly suggest you download one (free PDF) from adobe.com:
http://www.adobe.com/support/documentation/en/coldfusion/

Azadi
0
 

Author Comment

by:Short-Order-Coder
ID: 24382506
That certainly makes more sense.

I am working my way through the CFML manual.  I think I may also need to seek some more edu on programming fundamentals.  Any thoughts on where to find this?
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now