Using 2 tables to edit db information

Report · Jan 17, 2008

Hello;
I am writting an edit form type section for a web site and it uses 2 tables. I have it done in pieces and now am working on sewing it together. Here is my problem.

First I have 2 tables. One is Categories, and the other is Projects. here is what sections I have in these 2 tables:

Categories Table:
CategoryID
Name
MYFile
Description

Projects Table:
ProjectID
CategoryID
Name
Description

I have 2 types of edit pages, one is for just adding and editing the Category Table the other to add and edit the Project table. Now what I am trying to do it the following. (I am going to break this down into parts, doing one part at a time. So this is a multi part question) I want to take the Project page and have the Category Name be the order by for the page, wich means I need an innerjoin. I am kind of at a loss how I would write that and still be able to edit the record without the innerjoin getting in the way. (This will also be used on the Category page as a nav link to edit all the projects with the corresponding category and that will be the only projects that will appear is the ones assigned to that category.

But first I need just to ORDER BY Categories.Name in my Querry so that if you just choose to go to this page and view / edit all categories you can.

Here is my code so far. I need to have the Project manager page organize all the projects by Category.Name

<cfquery name="proMan" datasource="#sitedatasource#" username="#siteUserID#" password="#sitePassword#">
SELECT Projects.Name AS PName, Projects.CategoryID, Projects.ProjectID AS ID
Categories.Name AS CName, Categories.CategoryID
INNERJOIN Projects ON Categories
FROM Projects, Categories
</cfquery>
<head>
</head>
<body>
<cfoutput query="proMan">
Project Name: #PName#
Project category: #CName#
<form action="Project-Action.cfm" method="post">
<input type="hidden" name="ID" value="#ID#">
<input type="submit" name="proj_Edit" value=" Edit ">
<input type="submit" name="proj_Delete" value="Delete">
</form>
</cfoutput>

I know this is wrong, so How do I make it work?
(Then I will go into the next phase of the question about being able to go from teh Category Manager page to edit the projects just associated to the respective category, unless it is easier to do that part now. Then I will post the code from the Categories page so we can do an innerjoin there and make a link to edit projects in that category.)

Thank you. I know this is conveluted, but it is a big section and a lot of little odds and ends to tie in.

Phoenix
</body>

Report · Jan 17, 2008

Irish-Phoenix wrote:
> <cfquery name="proMan" datasource="#sitedatasource#"
username="#siteUserID#"
> password="#sitePassword#">
> SELECT Projects.Name AS PName, Projects.CategoryID, Projects.ProjectID AS ID
> Categories.Name AS CName, Categories.CategoryID
> INNERJOIN Projects ON Categories
> FROM Projects, Categories
> </cfquery>

I am sorry but I did not clearly understand all you are asking. But I
can address this one syntax issue that jumped out at me.

The syntax to do a join is:

FROM aTable INNER JOIN aTable ON aTable.key = bTable.key

Note: There are different kinds of joins, this is the basic inner join
that says give me all records from each table that have a matching key
value.

If I understand your database schema correctly it should look like:

FROM Project INNER JOIN Catergories ON projects.CategoryID =
Categories.CategoryID

Report · Jan 17, 2008

I knew there was a lot of info there.
here lets do this a step at a time.

First, I want to show the Project name, from teh project table, as well as the Category name it is joined with from teh Category Table. I believe the CategoryID in the Project table will be the link to bring the name out from teh Category table? Am I correct?

So if I add:

FROM Project INNER JOIN Catergories ON projects.CategoryID =
Categories.Name

Would that be correct? I can lose the INNERJOIN in my query, make it like this:
<cfquery name="proMan" datasource="#sitedatasource#" username="#siteUserID#" password="#sitePassword#">
SELECT Projects.Name AS PName, Projects.CategoryID, Projects.ProjectID AS ID
Categories.Name AS CName, Categories.CategoryID
FROM Project INNER JOIN Catergories ON projects.CategoryID =
Categories.Name
</query>

And use the rest of my code for cfoutput?
Is this correct?

Report · Jan 17, 2008

I changed around the query and I get an error. Here is my query code:

<cfquery name="proMan" datasource="#sitedatasource#" username="#siteUserID#" password="#sitePassword#">
SELECT Projects.Name AS PName, Projects.CategoryID, Projects.ProjectID AS ID,
Categories.Name AS CName, Categories.CategoryID
FROM Project INNER JOIN Catergories ON projects.CategoryID = Categories.CategoryID
ORDER BY Categories.Name
</cfquery>

This is my error:

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in JOIN operation.

The error occurred in C:\Websites\x9vdzd\admin\project-manager.cfm: line 3

1 : <cfset preappendURL = "../">
2 : <cfset pageType = "admin">
3 : <cfquery name="proMan" datasource="#sitedatasource#" username="#siteUserID#" password="#sitePassword#">
4 : SELECT Projects.Name AS PName, Projects.CategoryID, Projects.ProjectID AS ID,
5 : Categories.Name AS CName, Categories.CategoryID

--------------------------------------------------------------------------------

SQLSTATE 42000
SQL SELECT Projects.Name AS PName, Projects.CategoryID, Projects.ProjectID AS ID, Categories.Name AS CName, Categories.CategoryID FROM Project INNER JOIN Catergories ON projects.CategoryID = Categories.CategoryID ORDER BY Categories.Name
VENDORERRORCODE -3510

I am not good enough with joins yet to figure out where the error is. Can someone help me out? This is just to show the category in my code.

Thank you.

Phoenix

Report · Jan 17, 2008

your table name is Projects, but you use Project in the FROM clause...

---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

Report · Jan 17, 2008

Opps, didn't see that. BUT I'm still getting an error.

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in JOIN operation.

The error occurred in C:\Websites\x9vdzd\admin\project-manager.cfm: line 3

1 : <cfset preappendURL = "../">
2 : <cfset pageType = "admin">
3 : <cfquery name="proMan" datasource="#sitedatasource#" username="#siteUserID#" password="#sitePassword#">
4 : SELECT Projects.Name AS PName, Projects.CategoryID, Projects.ProjectID AS ID,
5 : Categories.Name AS CName, Categories.CategoryID

--------------------------------------------------------------------------------

SQLSTATE 42000
SQL SELECT Projects.Name AS PName, Projects.CategoryID, Projects.ProjectID AS ID, Categories.Name AS CName, Categories.CategoryID FROM Projects INNER JOIN Catergories ON projects.CategoryID = Categories.CategoryID ORDER BY Categories.Name
VENDORERRORCODE -3510

I changed the Project to Projects

Thanks.

Phoenix

Report · Jan 17, 2008

This word "Catergories" is not spelled correctly.

Report · Jan 17, 2008

Wow. can't believe I missed that..

Ok, now for phase 2 of this.

I need the category-manager page to have a link on it. the way it is set up is this:

The Category page has and edit category button, and delete category button. I need to add an edit projects for this category link. This will link to the Project-manager page we just fixed up the innerjoin page.

This is my code for teh Category-manager page:
<cfquery name="catMan" datasource="#sitedatasource#" username="#siteUserID#" password="#sitePassword#">
SELECT Categories.Name AS ViewField1, Categories.CategoryID AS ID, Projects.ProjectID
FROM Categories INNER JOIN Projects ON Categories.CategoryID = Projects.CategoryID
</cfquery>
<head>
</head>
<body>
<cfoutput query="catMan">
#ViewField1#

<a href="projectCat-edit.cfm?CategoryID=#ID#" class="navA">Edit Category</a>
<a href="project-edit.cfm?ID=#ProjectID#" class="navA">Edit Projects</a>

<form action="ProjectCat-Action.cfm" method="post">
<input type="hidden" name="ID" value="#ID#"><input type="submit" name="proj_Delete" class="formButtons" onMouseOver="this.style.backgroundColor='##0099CC'" onMouseOut="this.style.backgroundColor='##00659A'" value="Delete"></form>
</cfoutput>
</body>

What do I need to do on the Projects-manager page to make just the projects attached to selected category appear using the code posted that was just made with teh innerjoin?

<cfquery name="proMan" datasource="#sitedatasource#" username="#siteUserID#" password="#sitePassword#">
SELECT Projects.Name AS PName, Projects.CategoryID, Projects.ProjectID AS ID,
Categories.Name AS CName, Categories.CategoryID
FROM Projects INNER JOIN Categories ON projects.CategoryID = Categories.CategoryID
ORDER BY Categories.Name
</cfquery>
<head>
</head>
<body>
<cfoutput query="proMan">
Project Name: #PName#
Project category: #CName#
<form action="Project-Action.cfm" method="post">
<input type="hidden" name="ID" value="#ID#">
<input type="submit" name="proj_Edit" value=" Edit ">
<input type="submit" name="proj_Delete" value="Delete">
</form>
</cfoutput>
</body>

Thanks for all the help. I am not sure my category code is correct, including the link to edit the projects.

Phoenix

Report · Jan 18, 2008

you need to use GROUP attribute in your CFOUTPUT tag. docs have the details.

---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

Report · Jan 18, 2008

oh, and don;t forget to ORDER BY your query correctly (on the fields you
will be using GROUP attribute of CFOUTPUT tag on)

---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

Adobe Community

Using 2 tables to edit db information

1 Correct answer