Good morning! I have not stayed up this late coding for a very long
time... I empathize with you, dealing with this approaching deadline.
Please make a backup of your scripts before testing anything posted
here. It may seem silly to have to say it, but if you are lacking
sleep, a reminder couldn't hurt!
Your database should resemble the following, as far as the tables we
have discussed.
Table: CATEGORY
category_ID, category_name
Table: VENDOR
vendor_ID, (etc)
Table: CATEGORY_INDEX
vendor_ID, category_ID
---1.)FIRST QUERY:
This query is pretty straightforward and similar to what you have
already, for the vendor info. Then, a second query gets the vendor's
assigned categories, and lists them separately.
<cfquery name="display_info" datasource="#dsn#" username="#db_user#"
password="#db_pass#">
SELECT *
FROM VENDOR
WHERE VENDOR.vendor_ID='#url.vendor_ID#'
</CFQUERY>
<cfquery name="display_vendor_categories" datasource="#dsn#"
username="#db_user#"
password="#db_pass#">
SELECT *
FROM CATEGORY, CATEGORY_INDEX
WHERE CATEGORY_INDEX.vendor_ID='#url.vendor_ID#'
AND CATEGORY.category_id = CATEGORY_INDEX.category_ID
</CFQUERY>
You have several options for the assigned category update interface,
depending on how many possible categories you are dealing with. You
could display the assigned categories in plain text, and then offer a
scrolling selection list populated with all available categories, so
that the editor can assign different categories, like this:
Assigned Categories:
Deli, Cleaner, Banker
Now, get all the categories:
<cfquery name="display_all_categories" datasource="#dsn#"
username="#db_user#"
password="#db_pass#">
SELECT *
FROM CATEGORY
ORDER BY CATEGORY.category_name
</CFQUERY>
Change Assigned Categories:
__________________
| -- Choose -- ^
| Acupuncture
| Banker
| Cleaner
| Deli
| Domestics v
__________________
(Make sure you set the list to "MULTIPLE" so that more than one
category can be chosen)
Or, you could do the same with checkboxes, if there are not more than
20 or so:
Assigned Categories:
Deli, Cleaner, Banker
Change Assigned Categories:
(*) Acupuncture
( ) Banker
( ) Cleaner
(*) Deli
(*) Domestics
(etc...)
For either option, use your vendor category database results to
dynamically select the original assigned categories. This will
automatically create an array with the selected categories to use with
the next set of queries (whether or not they are changed by the
editor). You should play around with interface choices, and make sure
you have carefully worded instructions and error handling to lessen
the confusion.
See the Cold Fusion Web Database Construction Kit's "Working with
Select Boxes" for the syntax.
http://docs.rinet.ru:8080/Cold/ch20/ch20.htm#Heading4
---2.) SECOND QUERY-
To update the vendor information first, keep your original SET
statement (copied below) the same, with the "category_ID=" line
removed:
<cfquery name="update_vendor" datasource="#dsn#" username="#db_user#"
password="#db_pass#">
UPDATE VENDOR
SET
company_name='#Form.Company#' ,
company_address='#Form.company_address#',
(etc...)
WHERE vendor_ID = '#Form.vendor_ID#'
</cfquery>
Then add this set of queries to update the assigned categories. The
first one deletes all CATEGORY_INDEX entries associated with the
vendor. The second one just adds the newly selected categories, in a
loop using the array that was created when the form was submitted.
<cfquery name="delete_vendor_categories" datasource="#dsn#"
username="#db_user#"
password="#db_pass#">
DELETE FROM CATEGORY_INDEX
WHERE vendor_ID = '#Form.vendor_ID#'
</cfquery>
startloop (through #vendor_category_ids#) {
<cfquery name="add_vendor_categories" datasource="#dsn#"
username="#db_user#" password="#db_pass#">
INSERT INTO CATEGORY_INDEX (vendor_ID, category_ID)
VALUES ('#Form.vendor_ID#', '#this_category#')
</cfquery>
}
See the Cold Fusion Web Database Construction Kit's "CF Looping
Structures" for the exact syntax.
http://docs.rinet.ru:8080/Cold/ch17/ch17.htm#Heading7
---3.) 3RD QUERY
Display Vendors with their assigned categories - more of the same from
the first query, except you don't have to bother with all of the other
categories. Assume each vendor has been assigned at least one
category. If this is not the case, you may have to do an outer join,
which I don't think is supported by MySQL. Use error checking when
adding a vendor to make sure that at least one category is selected to
prevent this from happening.
<cfquery name="get_vendors" datasource="#dsn#" username="#db_user#"
password="#db_pass#">
SELECT *
FROM VENDOR, CATEGORY, CATEGORY_INDEX
WHERE VENDOR.vendor_ID = CATEGORY_INDEX.vendor_ID
AND CATEGORY.category_id = CATEGORY_INDEX.category_ID
ORDER BY VENDOR.vendor_name ASC
</cfquery>
Your raw results will look like this:
Company 1 Acupuncture
Company 1 Banker
Company 1 Cleaner
Company 2 Banker
Company 2 Cleaner
Company 2 Dentist
etc...
So you will have to compare each row (company id) with the one that
came before it, using a counter, to clean up the results to look like
this:
Company 1 : Acupuncture, Banker, Cleaner
Company 2 : Banker, Cleaner, Dentist
etc...
The logic:
for each (returned row) {
current_vendor_name = current vendor name
if (current_vendor_name = last_vendor_name) {
display the category only
} else {
display the vendor name and the category
}
last_vendor_name = current vendor name
}
Again, refer to your CF documentation for the exact syntax.
---4.) FOURTH QUERY
Updating, adding, and deleting of categories alone don't require any
changes. Whew! Your new CATEGORY_INDEX table is only a
vendor->category relation. As long as your current queries deal with
the CATEGORY table only, you should be fine. If you want to post those
as well, I can look at them and see if they need to change, but at
this point I really doubt it.
I hope this helps. Please don't hesitate to ask for clarification if
you are unclear about any part of this answer. Thanks!
-- mother-ga
Additional resources:
The Cold Fusion Web Database Construction Kit
http://docs.rinet.ru:8080/Cold/index.htm
Search strategy:
"cold fusion" syntax
mysql "cold fusion" |
