9 Replies Latest reply on Jan 18, 2008 6:20 AM by Newsgroup_User

    Using 2 tables to edit db information

    Irish-Phoenix Level 1
      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>
        • 1. Re: Using 2 tables to edit db information
          Level 7
          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
          • 2. Re: Using 2 tables to edit db information
            Irish-Phoenix Level 1
            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?


            • 3. Re: Using 2 tables to edit db information
              Irish-Phoenix Level 1
              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
              • 4. Re: Using 2 tables to edit db information
                Level 7
                your table name is Projects, but you use Project in the FROM clause...

                ---
                Azadi Saryev
                Sabai-dee.com
                http://www.sabai-dee.com
                • 5. Re: Using 2 tables to edit db information
                  Irish-Phoenix Level 1
                  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
                  • 6. Re: Using 2 tables to edit db information
                    Dan Bracuk Level 5
                    This word "Catergories" is not spelled correctly.
                    • 7. Re: Using 2 tables to edit db information
                      Irish-Phoenix Level 1
                      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


                      • 8. Re: Using 2 tables to edit db information
                        Level 7
                        you need to use GROUP attribute in your CFOUTPUT tag. docs have the details.

                        ---
                        Azadi Saryev
                        Sabai-dee.com
                        http://www.sabai-dee.com
                        • 9. Re: Using 2 tables to edit db information
                          Level 7
                          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