"Structurizing Your Database " Vol 2. Issue 3, p. 10  

Listing 1:

<cfset datasource = "structures">

<!---query the database for the categories--->

<cfquery datasource="#datasource#" name="category_query">

SELECT *

FROM product_category

</cfquery>

<!---initialize the top level category structure--->

<cfset server.product_category = structnew()>

<!---loop through all the rows of the query--->

<cfloop query="category_query">

<cfscript>

//make a nested structure for the category data

"server.product_category.record#category_ID#" = structnew();

//make keys for this category's name and description

"server.product_category.record#category_ID#.category_name" =

category_name;

"server.product_category.record#category_ID#.category_description" = cat-egory_

description;

</cfscript>

</cfloop>

 

 

Listing 2:

<cfquery datasource="#datasource#" name="product_query">

SELECT *

FROM Product

</cfquery>

<!---loop through all the products in the query--->

<cfloop query="product_query">

<cfscript>

//Nest each product two levels deeper than its product category to enable

easier data retrieval

//make the product nested structure for this category if it hasn't been cre-ated

yet

if (not isdefined("server.product_category.record#category_id#.product"))

"server.product_category.record#category_id#.product" = structnew();

//make the structure for this specific product

"server.product_category.record#category_id#.product.record#product_id#"

= structnew();

//fill this structure with data

"server.product_category.record#category_ID#.product.record#product_id#.pro

duct_name" = product_name;

"server.product_category.record#category_ID#.product.record#product_id#.pro

duct_description" = product_description;

"server.product_category.record#category_ID#.product.record#product_id#.nu

mber_in_stock" = number_in_stock;

</cfscript>

</cfloop>

 

 

Listing 3:

<!---retrieve one category name from the structure (primary key is soft-coded

for demonstration purposes)--->

<cfset local_variable = 1>

<cfoutput>

#evaluate("server.product_category.record#local_variable#.category_name")#

</cfoutput>

 

 

Listing 4:

<!---create a product category html table--->

<table border="1">

<tr>

<td>Product ID</td>

<td>Category Name</td>

<td>Category Description</td>

</tr>

<!---loop through the product category structure--->

<cfloop collection="#server.product_category#" item="i">

<cfoutput>

<tr>

<!---parse the word "record" out of the structure key--->

<td>#right(i, len(i) - 6)#</td>

<!---evaluate the category name and description--->

<td>#evaluate("server.product_category.#i#.category_name")#</td>

<td>#evaluate("server.product_category.#i#.category_description")#</td>

</tr>

</cfoutput>

</cfloop>

</table>

 

Listing 5:

<cfset local_variable = 1>

<cfscript>

"server.product_category.record#local_variable#.category_name" = "New

Name";

</cfscript>

 

 

Listing 6:

<!---change product 1 from product category 1 to product category 2--->

<cfset original_product_category_ID = 1>

<cfset new_product_category_ID = 2>

<cfset product_ID = 1>

<cfscript>

//make sure the new product structure for this product_category exists

if (not

isdefined("server.product_category.record#new_product_category_ID#.prod-uct"))

"server.product_category.record#new_product_category_ID#.product" =

structnew();

//copy the original structure to the new structure

"server.product_category.record#new_product_category_ID#.product.record#pr

oduct_ID#" =

structcopy(evaluate("server.product_category.record#original_product_catego-ry_

ID#.product.record#product_ID#"));

//delete the original structure

structdelete(evaluate("server.product_category.record#original_product_cate-gory_

ID#.product"), "record#product_id#");

</cfscript>