Skip to main content
A
Anonymous
January 4, 2012
Answered

Element undefined. What am I missing here?

  • January 4, 2012
  • 1 reply
  • 1665 views

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.

    This topic has been closed for replies.
    Correct answer Dave Watts

    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

    1 reply

    D
    Community Expert
    Dave WattsCommunity Expert
    Community Expert
    January 4, 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
    A
    Anonymous
    January 4, 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?

    I
    iSaid
    Known Participant
    January 4, 2012

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

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices