Thought I’d post on a page I recently had to revisit and how it used array notation to help display a query I was using.  In this page I was displaying a listing which contains a list of parents with x number of children each.  In a university for accreditation purposes you have to assess all of your programs.  This is done by creating a plan which is a list of Objectives which can have multiple Outcomes underneath that are being assessed.  Put all of this together and you get the Master Assessment Plan which people need to be able to view, PDF, edit, etc.  So I needed to display this. 

How I set up the query could be another blog post in the future on what I did and revisit if there is an easier way.  Essentially i defined my own query, did a query for all the objectives, looped them, added the objective to my custom query, checked for children, added them, then repeat till I had the plan all together in a single query I could use.  Well maybe not a whole blog post for that topic cause I just summed it up pretty easily.  More likely a blog post on why didn’t I just make an array to start with and try that.  I was just so used to queries at the time it was written that it was my first idea and I ran with it.

So now the display side as I have a nice query to use named qryMasterPlan.  I set a variable called isTableStarted to 0 which I will use later.  So I loop the master plan query and if it is an Objective I put it in a div tag and show the text.  Next I check to see if the table has been started and if it hasn’t I open a new table, put in the table header, start the body, and set isTableStarted to 1.  Then I just put in a row for each outcome.  Now how do you know when to close the table?

Sure you could close the table when you see that you have an Objective again on the next iteration and isTableStarted is 1.  In that case you know the table needs to be closed before starting the next table.  In retrospect I could have done that too, but this is a reminder that queries are arrays.  So before the next iteration of the loop starts I do this <cfif isTableStarted and qryMasterPlan.type[currentRow+1] eq ‘Objective’>.  Yes it’s tag syntax but I still do tags in display pages and try to use script everywhere else.  The thing to note is the currentRow + 1.  CurrentRow is the index for the row you are currently using in your query and provided by default.  By using array notation I am taking this current index, adding 1, and seeing what the next row’s “type” is in the query.  If it is an Objective then close the table.  Note that qryMasterPlan[“type”][currentRow+1] also would have worked just fine.   For the sticklers out there I do one final check on isTableStarted after the loop is finished to close out the last table. 

This is a simple real world example of how you can take advantage of using a query as if it were an array.  Even more real world as this is existing code and after revisiting it years after it was written I see SO many better ways to have done the same task.  Maybe this is useful option to solve some problem down the road or maybe not.  It is an option in working with a query that can get overlooked and possibly come in handy.

An insight into some refactoring ideas this example brought to mind looking back at this old code.
– Why not keep the plan as an array instead of another query since making it myself anyway?
– Could this have been accomplished in single SQL query with ‘Group By’ in combination with the group attribute of <cfoutput>?