Need help with an innerjoin issue

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CFmonger - 17 May 2008 13:38 GMT

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>


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>

<script language="Javascript">
function changeRecord(){
document.category.submit();
}
</script>
</head>
<body>

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

<cfoutput query="GetProjects" startRow="#startRow#" maxRows="#maxRows#">
#Name#
#Body#
</cfoutput>

<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

Reply to this Message

Azadi - 17 May 2008 16:24 GMT

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/

Reply to this Message

CFmonger - 17 May 2008 17:06 GMT

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>

<cfif getProjects.recordCount is 0>
    No projects in this category.
    <cfabort>
</cfif>

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

<cfoutput query="getProjects" maxrows="1">#cat_name#</cfoutput>


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

Reply to this Message

Webmaster Forum / ColdFusion / Advanced Techniques / May 2008

Need help with an innerjoin issue