ok guys, the table i have at the moment is as follows...
CREATE TABLE [dbo].[categories] (
[cat_id] [int] IDENTITY (1, 1) NOT NULL ,
[dModified] [datetime] NULL ,
[dCreated] [datetime] NULL ,
[cat_parent_id] [int] NULL ,
[cat_lft] [int] NULL ,
[cat_rgt] [int] NULL ,
[cat_level] [int] NULL,
[cat_sort_order] [int] NULL
) ON [PRIMARY]
GO
there are definitely not going to be 100's of records in fact it should at all
times be reasonably small. so i am going for the nested set model.
to work work out the levels each record i am using...
<cffunction name="rebuild_levels" access="public" output="false">
<cfquery name="getCats" datasource="#Request.App.dsn#"
password="#Request.App.DBpassword#" username="#Request.App.DBusername#">
SELECT child.cat_id, (COUNT(parent.cat_id) - 1) AS depth
FROM categories AS child, categories AS parent
WHERE child.cat_lft BETWEEN parent.cat_lft AND parent.cat_rgt
GROUP BY child.cat_id, child.cat_lft
ORDER BY depth
</cfquery>
<cfloop query="getCats">
<cfquery name="update" datasource="#Request.App.dsn#"
password="#Request.App.DBpassword#" username="#Request.App.DBusername#">
UPDATE categories
SET cat_level = #getCats.depth#
WHERE cat_id = #getCats.cat_id#
</cfquery>
</cfloop>
</cffunction>
i am still not quite seeing how i can display (lets say the top level
categories eg. Summer, Winter, Essentials, Sale Items) a particular level
without having the cat_parent_id to use in the url link to the child category?
my ordering at the moment is a bit clunky (to say the least)...
<cffunction access="public" name="reorderCat" output="false">
<cfargument required="true" name="cat_id" type="numeric"/>
<cfargument required="true" name="order" type="numeric"/>
<cftransaction>
<cfquery name="check" datasource="#Request.App.dsn#"
password="#Request.App.DBpassword#" username="#Request.App.DBusername#">
SELECT cat_parent_id, cat_level, cat_sort_order
FROM categories
WHERE cat_id = <cfqueryparam value="#arguments.cat_id#"
cfsqltype="CF_SQL_INTEGER">
</cfquery>
<cfif arguments.order EQ 1>
<!--- move up --->
<cfquery name="checkNextOrder" datasource="#Request.App.dsn#"
password="#Request.App.DBpassword#" username="#Request.App.DBusername#">
SELECT cat_id, cat_sort_order
FROM categories
WHERE cat_parent_id = #check.cat_parent_id#
AND cat_sort_order = (#check.cat_sort_order# - 1)
AND cat_level = #check.cat_level#
</cfquery>
<cfelse>
<!--- move down --->
<cfquery name="checkNextOrder" datasource="#Request.App.dsn#"
password="#Request.App.DBpassword#" username="#Request.App.DBusername#">
SELECT cat_id, cat_sort_order
FROM categories
WHERE cat_parent_id = #check.cat_parent_id#
AND cat_sort_order = (#check.cat_sort_order# + 1)
AND cat_level = #check.cat_level#
</cfquery>
</cfif>
<cfif checknextorder.recordcount>
<cfquery name="updateOrder1" datasource="#Request.App.dsn#"
password="#Request.App.DBpassword#" username="#Request.App.DBusername#">
UPDATE categories
SET cat_sort_order = <cfqueryparam
value="#check.cat_sort_order#" cfsqltype="CF_SQL_INTEGER">
WHERE cat_id = <cfqueryparam
value="#checkNextOrder.cat_id#" cfsqltype="CF_SQL_INTEGER">
</cfquery>
<cfquery name="updateOrder2" datasource="#Request.App.dsn#"
password="#Request.App.DBpassword#" username="#Request.App.DBusername#">
UPDATE categories
SET cat_sort_order = <cfqueryparam
value="#checkNextOrder.cat_sort_order#" cfsqltype="CF_SQL_INTEGER">
WHERE cat_id = <cfqueryparam value="#arguments.cat_id#"
cfsqltype="CF_SQL_INTEGER">
</cfquery>
</cfif>
</cftransaction>
</cffunction>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231044
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
