Element undefined. What am I missing here?

Report · Jan 03, 2012

I can't seem to get this template to work correctly. As it is now it generates the following error message;

Element PRODUCTTYPE is undefined in PRODUCTS.

Here is my code;

<cfquery datasource="farmky" name="getProductLists">
SELECT CatalogGroup.ID,
       CatalogGroup.Group,
       Products.ProductID,
       Products.Manufacturer,
       Products.ProductTitle,
       Products.CatalogGroup,
       Products.ProductType,
       ProductTypes.ID,
       ProductTypes.ProductType
FROM ProductTypes INNER JOIN (CatalogGroup INNER JOIN Products ON CatalogGroup.ID = Products.CatalogGroup) ON ProductTypes.ID = Products.ProductType
ORDER BY ProductTitle ASC
</cfquery>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<cfdump var="#getProductLists#">
<cfoutput query="getProductLists" group="#Products.ProductType#">
<table width="850" border="0">
<tr>
    <td colspan="3" bgcolor="##CCCCCC"><strong>#ProductTypes.ProductType#</strong></td>
</tr>
<cfoutput><tr>
    <td width="236" align="left">#ProductTitle#</td>
    <td width="254" align="left">#Manufacturer#</td>
    <td width="212" align="left">#Group#</td>
</tr></cfoutput>
</table></cfoutput>
</body>
</html>

The results of my <cfdump> do not show anything for the ProductTypes.ProductType column. I suspect the issue is with either my JOIN statements in my FROM clause or my group syntax or both. Any help on this would be greatly appreciated. Thanks in advance.

Report · Jan 03, 2012

Table names only mean something within the context of your SQL command. Once that's done, you have a recordset called getProductLists with a bunch of columns. If you have two columns named ProductType, you'll have to alias at least one of them in your query. Then, you can refer to getProductLists.ProductType and getProductLists.WhateverYourAliasIs.

As an aside, from a design perspective, your schema would make a bit more sense if you didn't use the column name "ProductType" to mean a numeric value in one table and a string in another. Instead, you should name your fields in ProductTypes something like this:

ProductTypeID

ProductType

Then, your foreign key in Products would be ProductTypeID instead of ProductType.

But you can feel free to ignore all that, it's not the cause of your problem which is described in full in the first paragraph of my response.

Dave Watts, CTO, Fig Leaf Software

Dave Watts, Eidolon LLC

Report · Jan 03, 2012

Thanks for the response Dave. I modified my code as per your suggestion and am now getting this error message;

Variable PRODUCTS_PRODUCTTYPE is undefined.

Here is the new code;

<cfquery datasource="farmky" name="getProductLists">
SELECT CatalogGroup.ID AS CatalogGroup_ID,
       CatalogGroup.Group,
       Products.ProductID,
       Products.Manufacturer,
       Products.ProductTitle,
       Products.CatalogGroup,
       Products.ProductType AS Products_ProductType,
       ProductTypes.ID AS ProductTypes_ID,
       ProductTypes.ProductType AS ProductTypes_ProductType
FROM ProductTypes INNER JOIN (CatalogGroup INNER JOIN Products ON CatalogGroup.ID = Products.CatalogGroup) ON ProductTypes.ID = Products.ProductType
ORDER BY ProductTitle ASC
</cfquery>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Central Farm Supply of Kentucky, Inc.</title>
</head>

<body>
<cfdump var="#getProductLists#">
<cfoutput query="getProductLists" group="#Products_ProductType#">
<table width="850" border="0">
<tr>
    <td colspan="3" bgcolor="##CCCCCC"><strong>#ProductTypes_ProductType#</strong></td>
</tr>
<cfoutput><tr>
    <td width="236" align="left">#ProductTitle#</td>
    <td width="254" align="left">#Manufacturer#</td>
    <td width="212" align="left">#Group#</td>
</tr></cfoutput>
</table></cfoutput>
</body>
</html>

My <cfdump> is showing the correct results for the ProductTypes.ProductType column whereas before it showed no results at all.

EDIT: I removed the #'s from the group="Products_ProductType" and now the page displays correctly except for the order. I modified the Order By clause to ORDER BY Products_ProductType, ProductTitle ASC and now get an error message stating Too few parameters. Expected 1. Thoughts?

Report · Jan 04, 2012

What about referencing the table in the order clause - Products.ProductTitle instead of just ProductTitle?

Report · Jan 04, 2012

ProductTitle isn't the issue, it is sorting properly. The problem is I have the titles grouped by ProductType and I can't get them to sort properly. If I add a GROUP BY clause or reference ProductType in my ORDER BY clause I get a 'Too few parameters. Expected1.' error. Any ideas why?

Report · Jan 04, 2012

Adding a group by clause to your query is wrong because you are not selecting any aggregates such as count(), or sum(). You can handle that part in your display code with the group attribute in cfoutput.

For the order by clause, use the alias you assigned in the select clause. Also, order your results by the field you intend to group by in the output.

This line has a problem:

FROM ProductTypes INNER JOIN (CatalogGroup INNER JOIN Products ON CatalogGroup.ID = Products.CatalogGroup) ON ProductTypes.ID = Products.ProductType

If you want to join on two fields, use the word "and".

Report · Jan 04, 2012

When I just change the ORDER BY clause to Products_ProductType I still get the Too few parameters error. I tried to change the FROM clause but that results in a syntax error. This is what I changed it to:

FROM ProductTypes (CatalogGroup AND Products ON CatalogGroup.ID = Products.CatalogGroup) AND ProductTypes.ID = Products.ProductType

What is the correct way to write this?

Report · Jan 04, 2012

The correct way to join tables is

from table1 join table2 on table1.field1 = table2.field1 and table1.field2 = table2.field2

You have too much going on with your code and it's hard for you to see what is causing the problems. I suggest starting your query again with this:

select count(*)

from ProductTypes

Once you run it successfully, add the joins and run it again. If successful, fill out your select clause and run again. Then your order by clause. Once your query runs successfully you can start working on your output.

Report · Jan 04, 2012

This line has a problem:
FROM ProductTypes INNER JOIN (CatalogGroup INNER JOIN Products ON CatalogGroup.ID = Products.CatalogGroup) ON ProductTypes.ID = Products.ProductType
If you want to join on two fields, use the word "and".

Actually, no, there's nothing wrong with this nested inner join syntax. I don't generally like writing queries that way, but they're not wrong. This query is joining the joined result within the parentheses to ProductTypes.

Dave Watts, CTO, Fig Leaf Software

Dave Watts, Eidolon LLC

Report · Jan 04, 2012

EDIT: I removed the #'s from the group="Products_ProductType" and now the page displays correctly except for the order. I modified the Order By clause to ORDER BY Products_ProductType, ProductTitle ASC and now get an error message stating Too few parameters. Expected 1. Thoughts?

Some databases don't let you use aliases for ORDER BY or GROUP BY clauses. Use the full name of the field (table name and field name) there.

Dave Watts, CTO, Fig Leaf Software

Dave Watts, Eidolon LLC

Report · Jan 04, 2012

Thanks Dave. Works like a charm.

Element undefined. What am I missing here?

1 Correct answer