How to edit a spreadsheet with Coldfusion?

Creating ColdFusion/SQL queries for database

• QUESTION: Creating ColdFusion/SQL queries for database QUESTION: Creating ColdFusion/SQL queries for database REGARDING: ColdFusion/SQL/mySQL database/Website QUESTION: I am in need of some help understanding how to write and manage a couple of queries relating to a database/Website. The website lists local businesses, known in this website as vendors. The mySQL database currently has two tables- VENDOR and CATEGORY. The CATEGORY table has two fields, 'CATEGORY_ID' and 'VENDOR_ID'. The VENDOR table currently has a field called 'CATEGORY_ID' - that way, the site editor can add/delete new categories and then the VENDOR's table will be updated dynamically with the updated category name. NOW, the problem I have is that the site owner has just asked me to make room for 3 more categories for each vendors entry, so that each vendor can be listed under a total of 4 categories. SO, he told me to create a new table called CATEGORY_INDEX and give it the fields 'CATEGORY_ID' and 'VENDOR_ID' and I think he said to put one other in there (i think it may have been category_name, im not sure). ALSO...I have an admin section with a bunch of pages that allow for editing of the site. SO THE PROBLEM IS, i need help writing the new queries that will allow me to do the following: 1) EDIT THE VENDOR INFO - in this form, i need a query to display the 4 categories from the 3 tables and also make them available for editing and updating. 2) ADD NEW VENDOR - in this form, i need a query to display the categories so that the editor can select the 4 categories and then hit "submit" to add the new vendor. 3) ADD/EDIT CATEGORIES - in this form, i need to be able to type in new categories to be added to the database, and also i need to be able to display these categories names so that they can be edited if the editor wants to change/update them. Well, that's it. I would be happy to provide any clarification of this, but I should stress that I am on a limited time frame here. I only have a very limited amount of time to get this done. Thank you very much in advance for your help.

• Answer:

  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"