Google Answers Logo
View Question
 
Q: Creating ColdFusion/SQL queries for database ( Answered 4 out of 5 stars,   1 Comment )
Question  
Subject: Creating ColdFusion/SQL queries for database
Category: Computers > Programming
Asked by: jgclarke-ga
List Price: $40.00
Posted: 20 Mar 2003 15:53 PST
Expires: 19 Apr 2003 16:53 PDT
Question ID: 178890
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.

Request for Question Clarification by mother-ga on 20 Mar 2003 20:05 PST
Greetings! I have two questions, and a request. First, where are you
storing the category name? You don't mention it being in the category
table, where it should be, not in the Category_Index table. Second,
are you using vendor_id in the Category table? Can that be changed?
Which brings me to my request, which is could you to post your current
set of scripts, minus the html  output? It would be easier to
understand and then simply modify your existing queries to reflect
your new database schema, than try to make it all up blindly. It
sounds like you have a working site, correct?

Thanks so much!
-- mother-ga

Clarification of Question by jgclarke-ga on 20 Mar 2003 21:22 PST
Great, thanks for responding. I hope this can work out as I am behind
on this...ahh!! Okay, I hope these answers clarify your questions to
me. My first answer is the category_name field IS in fact in the
CATEGORY table, as you say it should be. This category_name field
contains the name of the category (FOR EXAMPLE: Deli, Cleaner, Banker,
ETC.) that corresponds to the cateogry_ID. The second answer is, I am
NOT currently using the 'vendor_ID' in the CATEGORY table. Although
that can be changed easily enough, if that's what you recommend.

Okay, now you asked me to reveal my queries. this i can do for you
gladly, however, I want to stress that these queries are not correct,
because they're not working- in fact, these queries only represent the
site in its current state, that is, with only ONE category field. So
they'd need to be fully expanded to include this new 4-category scheme
we're referring to here.

---1.)FIRST QUERY:
Okay, this is the query that is currently used on the 'edit-vendor'
page. this page allows a site editor to log in, choose a vendor, and
then have the vendor's info displayed so that it can be updated.
Here's the query:

<cfquery name="display_info" datasource="#dsn#" username="#db_user#"
password="#db_pass#">
	SELECT *
	FROM VENDOR, CATEGORY 
	WHERE VENDOR.vendor_ID='#url.vendor_ID#' AND VENDOR.category_ID =
CATEGORY.category_ID
</CFQUERY>

---2.) SECOND QUERY-
Okay, this query is what POSTS into the database after you have made
your changes to the 'edit-vendor' form. So, you make the changes to
the form, and then you post the form, and this query dumps yr info
into the DB:

<cfquery name="update_vendor" datasource="#dsn#" username="#db_user#"
password="#db_pass#">
UPDATE VENDOR SET
company_name='#Form.Company#' ,
company_address='#Form.company_address#', phone='#Form.phone#',
fax='#Form.fax#', web_address='#Form.web_address#',
email_address='#Form.email_address#',
business_description='#Form.Business_Description#', bbb='#Form.bbb#',
cslb='#Form.cslb#', category_ID='#Form.category_ID#',
alist_recomendation='#Form.alist#', city='#Form.city#',
state='#Form.state#', zip='#Form.zip#', contact='#Form.contact#',
logopath='#logopath#', input_name='#Form.input_name#',
input_phone='#Form.input_phone#'

WHERE vendor_ID = '#Form.vendor_ID#'

</cfquery>

---3.) 3RD QUERY
The third query i need help with concerns how the heck do I output a
simple list of vendors, including each category name they're
associated with? In other words, I have a basic page that just lists
all the vendors in order (for printable page sake), and i'd like to
display them along with their 4 categories. This is what I have so far
to do this:

<cfquery name="get_vendors" datasource="#dsn#" username="#db_user#"
password="#db_pass#">
	SELECT *, CATEGORY.name 
	FROM VENDOR, CATEGORY 
	WHERE VENDOR.category_ID = CATEGORY.category_ID
	ORDER BY company_name ASC
</cfquery>

---4.) FOURTH QUERY
The 4th and final thing I need is I need to know the process involved
in UPDATING the categories by themselves. I have a page where an
editor can login and then ADD a category, but then I also have a thing
where the editor can select a category from the full list and then
either edit the category name, or just plain delete it all together.
Since the whole schema of my category arrangements will be changing, i
will assume this aspect of the site will need to change as well.

---5.) WRAP UP
I know this seems like alot, but if you have insight on how it should
be constructed, i am in great hopes that it wouldn't take you too long
at all to simply change these queries. Because I am very much in need
of having this done, I am also in favor of giving you specific access
to the site if you need it, or request it, if that would help you in
agreeing to help me on this. I want to also make it clear that I am
willing to pay more than the $40 if need be. SO, if you review this
and determine you can do it but $40 is not a reasonable sum to pay you
for your time, please let me know and we can work out arrangements.
Also, If you have any additional questions, please let me know.

I thank you very much for your time and consideration in this matter.
Answer  
Subject: Re: Creating ColdFusion/SQL queries for database
Answered By: mother-ga on 21 Mar 2003 00:24 PST
Rated:4 out of 5 stars
 
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"

Clarification of Answer by mother-ga on 21 Mar 2003 05:54 PST
I misspoke regarding the fourth query, regarding managing categories.
I am sorry about the oversight.  I need to add that if a category is
DELETED, then all associations in the CATEGORY_INDEX table also need
to be deleted. Here is that query, given that a category_ID is passed
from a form. This should occur right after the query that deletes the
category from the CATEGORY table.


<cfquery name="delete_categories_assignments" datasource="#dsn#"
username="#db_user#"
password="#db_pass#"> 
DELETE FROM CATEGORY_INDEX  
WHERE category_ID = '#Form.category_ID#'  
  
</cfquery>

Request for Answer Clarification by jgclarke-ga on 21 Mar 2003 07:32 PST
Thank you for your prompt response. I have a question here in the
first going. I installed the code on the 'edit-vendor' page, and
clicked "update", but when i did, i got the resulting success page.
However, there was some text at the top of the success page indicating
that things didn't go well. The text was:

startloop (through #vendor_category_ids#) { } 

Which tells me that the loop only went through once and then stopped.
I als know this to be the case because i went back in to the
'edit-vendor' page on the same vendor and it only showed 1 of the
categories i had just selected. so...this startloop is only looping
once, im afraid to say. ALSO, in that line of code above, where do you
get that #vendor_category_ids# variable? im confused by that. THANK
YOU!!!

Request for Answer Clarification by jgclarke-ga on 21 Mar 2003 07:59 PST
NEVERMIND! I figure out what you were doing- you were simply putting
that in there as a placeholder for me to find the correct CF loop
statement. Well I have checked around and am trying to build that part
right now. I am going to stay on this until I get it right and then
move to your next bit of code, so i may have some additional questions
for you later on. Again, thank you very much for your help with this.

Clarification of Answer by mother-ga on 21 Mar 2003 11:16 PST
You are very welcome. I should have been more explicit when I used
pseudo-code, I hope you didn't lose too much time over that. I am
hopeful that we can get this working for you in time! If you have a
specific "how-to" question, please let me know.

-- mother-ga

Request for Answer Clarification by jgclarke-ga on 21 Mar 2003 11:29 PST
ah! well its a few hours later and im still stuck on the CF Loop. I
have tried a few different loop scenarios but so far nothing has
worked. I am hoping you either know some CFLOOP, or could point me in
the right direction...Really, ive been trying quite hard but cant get
past this section...it seems like this project is just going to be a
never-ending list of roadblocks for me. any insight on the cfloop
would be a great help, thank you!!

Clarification of Answer by mother-ga on 21 Mar 2003 11:32 PST
Sorry that you are stuck... Please post what you have tried that is
not working, and in the meantime I will work on that loop for you.

Clarification of Answer by mother-ga on 21 Mar 2003 12:10 PST
The following is the code you need for Query number two - updating
vendor categories. I found additional documentation which you should
read through before testing it.

"Listing 20.5  SAVEMANY2MANY.CFM--A Multiselect Box Functioning as an
Interface for a Many-to-Many Relation" from the Cold Fusion Web
Database Construction Kit
http://docs.rinet.ru:8080/Cold/ch20/ch20.htm

The basic logic:

1. The user submits chosen vendor category ID's via a MULTIPLE
selection list or series of checkboxes.
2. CF automatically assigns these chosen categories to an array
variable, called "vendor_category_ids"
3. You "walk through" each selected category, and for each instance,
insert one row into the CATEGORY_INDEX table.

<CFLOOP INDEX="ii" FROM="1" TO="#vendor_category_ids(ca)#">

     <CFSET #this_category# = #listgetat(vendor_category_ids,ii)#>

     <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>

</CFLOOP>


While I was looking into this, I found the exact output syntax you
need to accomplish Query number 4, listing Vendors with their assigned
categories. You can clean up the HTML output (like putting the Vendor
and Categories into a table) but the logic is the same.


<CFOUTPUT QUERY="get_vendors">
<CFIF #vendor_name# is #last_vendor_name#>
     #category_name<BR>
<CFELSE>
     <BR><BR>
     <B>#vendor_name</B><BR>
     #category_name<BR>
</CFIF>
<CFSET #last_vendor_name# = #vendor_name#>
</CFOUTPUT>

Let me know if this helps you.

Request for Answer Clarification by jgclarke-ga on 21 Mar 2003 12:34 PST
great, thank you, im working on these things right now. my msn
messenger info is jgclarke@hotmail.com and my aol im name is
jasonclarke46 and there is more $ in this to be sure if we can connect
and solve this today...waiting on your thoughts on that...

Request for Answer Clarification by jgclarke-ga on 21 Mar 2003 14:32 PST
OKAY, ive tried and tried, and it keeps giving me an error on this
line of code:
<CFLOOP INDEX="ii" FROM="1" TO="#vendor_category_ids(ca)#">
it says "(ca)" is not recognized as a query at all...ahhhh!!!! I am
available tonight and again tomorow morning, if you could clarify
this, that would really really help...thank you!

Clarification of Answer by mother-ga on 21 Mar 2003 19:31 PST
Sorry for the delay in responding. Try this instead,  and remember you
can always refer to the documentations and examples posted here:

"Listing 20.5  SAVEMANY2MANY.CFM--A Multiselect Box Functioning as an
Interface for a Many-to-Many Relation" from the Cold Fusion Web
Database Construction Kit
http://docs.rinet.ru:8080/Cold/ch20/ch20.htm 
 

<CFLOOP INDEX="ii" FROM="1" TO="#listlen(vendor_category_ids)#"> 
 
     <CFSET #this_category# = #listgetat(vendor_category_ids,ii)#> 
 
     <cfquery name="add_vendor_categories#ii#" datasource="#dsn#"
username="#db_user#" password="#db_pass#">
       INSERT INTO CATEGORY_INDEX (vendor_ID, category_ID) 
       VALUES ('#Form.vendor_ID#', '#this_category#')  
     </cfquery> 
 
</CFLOOP> 


Thanks,
-- mother-ga
jgclarke-ga rated this answer:4 out of 5 stars
Really stuck with it until then end. thank you.

Comments  
Subject: Re: Creating ColdFusion/SQL queries for database
From: mother-ga on 24 Mar 2003 13:33 PST
 
Thank you for the rating, and I hope you got it working!

-- mother-ga

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy