2 Replies Latest reply on May 17, 2008 9:06 AM by CFmonger

    Need help with an innerjoin issue

    CFmonger Level 1
      Hello;
      I am writting a small app for my web site and have run into an issue with an innerjoin. Let me explain what the function is for this so maybe you can understand what I am doing.

      There are 2 pages to this. Page 1, is a category selection page. It loads all the categories on the page with a link that passes a category ID to page 2. Page 2, then allows you to view all the projects that are associated to the specific category. There is a numbered navigation on this page, kind of like google, 1 2 3 4 More> Next>

      Now what I am trying to do it create a select function that allows you to choose another category to look through on page 2 so you don't have to go back to page 1 and select another category there.

      Right now, I have the innerjoin working well with page 1 going to page 2 and the numbered nav, it is doing what it is supposed to. BUT the select function is working off the where clause and not working independantly from what the numbered nav and body is supposed to be doing. There is also a spot I added to this page that kind of works like the select function, it puts the name of the category your viewing on the page.

      What is happening with the select function and this name function is lets say there are 5 project records tied to the category you selected, then the select function gets 5 names of the same category in it and so does the category name function.

      (Follow this?)

      Let me post the code for page 1 and page 2. also will add what the tables are in the db.

      DB tables:
      Table1: Categories
      Name (this is the actual category name), MYFile(the file that give an image of the category), Description(a description of this category), CategoryID (this is added to the projects table for each project that is connected to each category)

      Table 2: Projects
      ProjectID (this is the ID of the projects), Name (this is the name of the project) Body (thi sis the description and picture of the project), CategoryID (this is the id of what category the project falls under, also assigned to tproject from the categories table)

      There is a lot of code that goes in this, so I am going to post the code that is pertinent to this question, 90% of it is working at this time, just not the select function and category name on page 2.

      Page1 Code:
      <CFQUERY name="GetRecord" datasource="#APPLICATION.dataSource#">
      select Name, MYFile, Description, CategoryID
      FROM Categories
      </CFQUERY>
      <head>
      </head>
      <body>
      <cfloop query="GetRecord" startRow="#URL.startRow#" endRow="#endRow#"><cfoutput>
      <img src="../img/cat/#MYFile#" width="50" height="50">
      <a href="portfolio-detail.cfm?CategoryID=#CategoryID#">#Name#</a>
      #Description#
      </cfoutput></cfloop>
      <!--- there is a numbered nav on this page, it works nice so I didn't add the code, that is why I am using a cfloop --->

      Page2 code:

      <CFQUERY name="getProjects" datasource="#APPLICATION.dataSource#">
      SELECT Projects.ProjectID, Projects.Name, Projects.Body, Projects.CategoryID,
      Categories.CategoryID AS catID, Categories.Name AS cat_name
      FROM Projects
      INNER JOIN Categories
      ON Categories.CategoryID = Projects.CategoryID
      WHERE Projects.CategoryID = #categoryID#
      ORDER BY ProjectID
      </CFQUERY>
      <cfif getProjects.recordCount is 0>
      No projects in this category.
      <cfabort>
      </cfif>
      <head>
      <!--- this script is for the select function so when you make a selection, it goes to the next category without having to hit a submit button --->
      <script language="Javascript">
      function changeRecord(){
      document.category.submit();
      }
      </script>
      </head>
      <body>
      <!--- here are the Category name and select functions that aren't working properly yet --->
      <cfoutput query="getProjects">#cat_name#</cfoutput>
      <form Name="category" method="post" Action="portfolio-detail.cfm?CategoryID=true">
      <select name="CategoryID" size="1" onChange="category.submit();">
      <option value=""> --Select a Category-- </option>
      <CFOUTPUT query= "getProjects">
      <option value="#CategoryID#">#cat_name#</option>
      </CFOUTPUT>
      </select>
      </form>
      <!--- this part is working properly, it also has a numbered nav, like google that is working properly at this point --->
      <cfoutput query="GetProjects" startRow="#startRow#" maxRows="#maxRows#">
      #Name#
      #Body#
      </cfoutput>
      <!--- next is the cfmodule for the nav, the nav is a lot of code, a whole page on it's own so I am not posting it --->
      <cfmodule
      template="../CFdocs/PageNav.cfm"
      totalItems="#numRows#"
      numPerPage="#maxRows#"
      startRow="#startRow#"
      url="#cgi.script_name#?categoryID=#categoryID#&startRow=#startRow#">

      The main part that is not working properly is the select, and category name. How to I get those to work independantly from my where clause? (I need the where clause for the numbered nav and cycling through the projects tied to the category you called up from page 1.

      Can someone help me tweek my code from this point? I am stumped, I looked at the cf docs and I can't find a solution. I am using CF 8.

      Any help would be appreciated. Thank you!

      CFmonger
        • 1. Re: Need help with an innerjoin issue
          Level 7
          you need the same query as on your page1 to power your categories select
          list on you page2. you can then add some <cfif> blocks to pre-select on
          page load the category that was selected on page1 or in the select list
          on page2.

          i also assume your <cfoutput query="getProjects">#cat_name#</cfoutput>
          shows the category name as many times as you have projects in that
          category? change that to <cfoutput>#getProjects.cat_name#</cfoutput>
          instead then.

          you also would want to:
          a) start properly scoping your variables
          b) use <cfqueryparam> tag
          c) maybe use <cfparam> tags to set some default vars in various scopes,
          especially in conjunction with a) above
          d) start calling things what they are: a query is NOT a function :)


          Azadi Saryev
          Sabai-dee.com
          http://www.sabai-dee.com/
          • 2. Re: Need help with an innerjoin issue
            CFmonger Level 1
            I fixed a lot of this since I posted it this morning. Now all that isn't working properly is the select, it isn't passing the proper variable to make the query work.

            This is what I have now:
            Page 2:

            <CFQUERY name="getProjects" datasource="#APPLICATION.dataSource#">
            SELECT Projects.ProjectID, Projects.Name, Projects.Body, Projects.CategoryID,
            Categories.CategoryID AS catID, Categories.Name AS cat_name
            FROM Projects
            INNER JOIN Categories
            ON Categories.CategoryID = Projects.CategoryID
            WHERE Projects.CategoryID = #categoryID#
            ORDER BY ProjectID
            </CFQUERY>
            <!--- I will add the <cfqueryparam> in a bit --->
            <cfif getProjects.recordCount is 0>
            No projects in this category.
            <cfabort>
            </cfif>
            <!--- This query populates the select nav --->
            <CFQUERY name="cata" datasource="#APPLICATION.dataSource#">
            select Name, MYFile, Description, CategoryID
            FROM Categories
            </CFQUERY>
            <head>
            <script language="Javascript">
            function changeRecord(){
            document.category.submit();
            }
            </script>
            </head>
            <body>
            <!--- this output works well now, gives me what I need and changes when you select a category from page 1 --->
            <cfoutput query="getProjects" maxrows="1">#cat_name#</cfoutput>

            <!-- the select isn't passing the CategoryID properly, it is now throwing an error --->
            <form Name="category" method="post" Action="portfolio-detail.cfm?CategoryID=#CategoryID#">
            <select name="CategoryID" size="1" onChange="category.submit();">
            <option value=""> --Select a Category-- </option>
            <CFOUTPUT query= "cata">
            <option value="#CategoryID#">#Name#</option>
            </CFOUTPUT>
            </select>
            </form>
            <!-- the rest of the code goes after this, it ias all working --->

            The error I get when using the select is this:

            Error Executing Database Query.
            [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Projects.CategoryID ='.

            The error occurred in C:\Websites\folio\portfolio-detail.cfm: line 12

            10 : INNER JOIN Categories
            11 : ON Categories.CategoryID = Projects.CategoryID
            12 : WHERE Projects.CategoryID = #categoryID#
            13 : ORDER BY ProjectID
            14 : </CFQUERY>

            I also tried doing it this way:
            <form Name="category" method="post" Action="portfolio-detail.cfm?CategoryID=true"

            This way didn't work at all, it only made the numbered nav on the page cycle through all the projects in all the categories.

            What do I need to change to pass the proper variable from the select nav to the innerjoin query and my where statement?