11 Replies Latest reply on May 17, 2006 1:37 PM by chrome88

    Need help with query and output

    chrome88
      I have a table that contains a list of Assets and each Asset contains a Category field, Each asset can have multiple categories assigned to it, so the Category column is populated as a list (e.g. 14, 16, 17, 19).

      The values are drawn from a Category table using the primary key as a reference no. (e.g. 14 = Keyboards, 16 = Trackballs, etc.)

      I've been trying to figure out how to output the Categories so that when the Category is displayed, the list will appear as the Category name, and not as the referenced no.

      example:
      <cfquery name="qIndex" datasource="#appDSN#">
      SELECT Asset.Reference, Asset.AssetName, Asset.Category
      FROM Asset
      WHERE Category LIKE '%#URL.Category#%'
      </cfquery>

      <cfoutput query="qIndex">
      #Asset.Reference#<br />
      #Asset.AssetName#<br />
      #Asset.Category#
      </cfoutput>

      Displays:
      AD_987738
      XYZ Keyboard
      11, 14, 17, 18

      Table for Categories are:
      CatID = Primary Key
      Category = Category Name
      CatImage = Image for header

      Thanks for any help.
        • 1. Re: Need help with query and output
          The ScareCrow Level 1
          You should redesign your db to have another table
          This table should have the asset id and the category id (I call this a bridging table)
          The category column would then be removed from the asset table.
          You then do a join on the tables to gain the category description.

          But to fix your problem with the existing structure
          Your query
          <cfquery name="qIndex" datasource="#appDSN#">
          SELECT Asset.Reference, Asset.AssetName, Asset.Category
          FROM Asset
          WHERE Category LIKE '%#URL.Category#%'
          </cfquery>

          <cfoutput query="qIndex">
          <cfset catids = ValueList(qIndex.category])>
          <cfquery name="qCat" datasource="#appDSN#">
          Select Category_Name
          From categories
          Where Category_ID IN (#catids#)
          </cfquery>
          #qIndex.Reference#<br />
          #qIndex.AssetName#<br />
          <cfloop query="qCat">
          #qCat.Category#<br>
          </cfloop>
          </cfoutput>

          Ken
          • 2. Re: Need help with query and output
            hrpatel34
            You can do it in this way aslo


            <cfquery name="qIndex" datasource="#appDSN#">
            SELECT Asset.Reference, Asset.AssetName, Asset.Category
            FROM Asset
            WHERE Category LIKE '%#URL.Category#%'
            </cfquery>
            <cfquery name="qIcat" datasource="#appDSN#">
            SELECT *
            FROM Category
            </cfquery>
            <cfoutput query="qIndex">
            #Asset.Reference#<br />
            #Asset.AssetName#<br />
            <cfquery name="qIcatName" dbtype="query">
            select * from qIcat where catid in (0#Asset.Category#)
            </cfquery>
            <cfoutput query="qIcatName">
            #qIcatName.CategoryName#,
            </cfoutput>
            </cfoutput>
            • 3. Re: Need help with query and output
              chrome88 Level 1
              quote:

              Originally posted by: The ScareCrow
              You should redesign your db to have another table
              This table should have the asset id and the category id (I call this a bridging table)
              The category column would then be removed from the asset table.
              You then do a join on the tables to gain the category description.

              But to fix your problem with the existing structure
              Your query
              <cfquery name="qIndex" datasource="#appDSN#">
              SELECT Asset.Reference, Asset.AssetName, Asset.Category
              FROM Asset
              WHERE Category LIKE '%#URL.Category#%'
              </cfquery>

              <cfoutput query="qIndex">
              <cfset catids = ValueList(qIndex.category])>
              <cfquery name="qCat" datasource="#appDSN#">
              Select Category_Name
              From categories
              Where Category_ID IN (#catids#)
              </cfquery>
              #qIndex.Reference#<br />
              #qIndex.AssetName#<br />
              <cfloop query="qCat">
              #qCat.Category#<br>
              </cfloop>
              </cfoutput>

              Ken




              Ken, Thanks. It's really close, but all of the repeating records are taking the Category output from the first record in the set and repeating it...e.g. if the first record is "Mouse, Keyboards, Trackballs", all of the subsequent records show the same.

              Also should "ValueList(qIndex.category])", have been "ValueList(qIndex.category)"?

              If there's not a way to do this using the current db, i'll try using a bridging table.

              thanks.
              paul
              • 4. Re: Need help with query and output
                philh Level 1
                You really should redesign the database. Comma-delimited lists of information in one field are a big no-no.

                Also, instead of

                <cfset catids = ValueList(qIndex.category)>

                try

                <cfset catids = ValueList(category)>

                Using the query prefix may be forcing the code to look at the first record every time.

                HTH,
                • 5. Re: Need help with query and output
                  chrome88 Level 1
                  quote:

                  Originally posted by: philh
                  You really should redesign the database. Comma-delimited lists of information in one field are a big no-no.

                  Also, instead of

                  <cfset catids = ValueList(qIndex.category)>

                  try

                  <cfset catids = ValueList(category)>

                  Using the query prefix may be forcing the code to look at the first record every time.

                  HTH,


                  Changing that varialble as noted returned the following error:
                  Parameter 1 of function ValueList which is now (Category) must be pointing to a valid query name.

                  If i redesign the database, what is a better way to store information of multiple results related to one record?

                  Thanks, Paul
                  • 6. Need help with query and output
                    paross1 Level 2
                    Entered in error.
                    • 7. Need help with query and output
                      chrome88 Level 1
                      quote:

                      Originally posted by: hrpatel34
                      You can do it in this way aslo


                      <cfquery name="qIndex" datasource="#appDSN#">
                      SELECT Asset.Reference, Asset.AssetName, Asset.Category
                      FROM Asset
                      WHERE Category LIKE '%#URL.Category#%'
                      </cfquery>
                      <cfquery name="qIcat" datasource="#appDSN#">
                      SELECT *
                      FROM Category
                      </cfquery>
                      <cfoutput query="qIndex">
                      #Asset.Reference#<br />
                      #Asset.AssetName#<br />
                      <cfquery name="qIcatName" dbtype="query">
                      select * from qIcat where catid in (0#Asset.Category#)
                      </cfquery>
                      <cfoutput query="qIcatName">
                      #qIcatName.CategoryName#,
                      </cfoutput>
                      </cfoutput>


                      tried this solution as well, but am getting an error message
                      Element CATEGORY is undefined in ASSET.
                      (from: select * from qIcat where catid in (0#Asset.Category#)

                      I assume that:
                      <cfoutput query="qIndex">
                      #Asset.Reference#<br />
                      #Asset.AssetName#<br />

                      was meant to be:
                      <cfoutput query="qIndex">
                      #qIndex.Reference#<br />
                      #qIndex.AssetName#<br />

                      Thanks.
                      • 8. Re: Need help with query and output
                        The ScareCrow Level 1
                        can you post your code as you have it for my solution ?

                        The reason you get the error from hrpatel34 solution is because the table name is being used in the cfoutput and not the query name.

                        quote:

                        If i redesign the database, what is a better way to store information of multiple results related to one record?

                        With a "bridging" table. You will then have one record for each asset/category combination.

                        Ken
                        • 9. Re: Need help with query and output
                          chrome88 Level 1
                          quote:

                          Originally posted by: The ScareCrow
                          can you post your code as you have it for my solution ?

                          The reason you get the error from hrpatel34 solution is because the table name is being used in the cfoutput and not the query name.

                          quote:

                          If i redesign the database, what is a better way to store information of multiple results related to one record?

                          With a "bridging" table. You will then have one record for each asset/category combination.

                          Ken


                          I caught that error in hrpatel34's solution. The error message was with the revised code.

                          Code from your solution follows:

                          <cfquery name="qIndex" datasource="#appDSN#">
                          SELECT *
                          FROM Asset
                          WHERE Category LIKE '%#URL.Category#%'
                          </cfquery>

                          <cfoutput query="qIndex">
                          <cfset catids=ValueList(qIndex.Category)>
                          <cfquery name="qCat" datasource="#appDSN#">
                          SELECT Category From Category WHERE CatID IN (#catids#)
                          </cfquery>
                          <td>
                          Category: <cfloop query="qCat">#qCat.Category#, </cfloop><br>
                          #qIndex.Asset#<br />
                          #qIndex.Reference#<br />
                          </cfoutput>

                          So if I use a bridging table, is an individual record created for each Category related to an asset, as opposed to entering as a comma delimited list?

                          Thanks again for your help Ken.
                          paul
                          • 10. Re: Need help with query and output
                            The ScareCrow Level 1
                            That code should work.

                            quote:

                            So if I use a bridging table, is an individual record created for each Category related to an asset, as opposed to entering as a comma delimited list?


                            Yes, that's correct

                            Ken
                            • 11. Re: Need help with query and output
                              chrome88 Level 1
                              Finally figured this out. Had to insert an intermediate query within the output to filter each record, otherwise it was outputting the results from the first record only and repeating it.

                              <cfquery name="qIndex" datasource="#appDSN#">
                              SELECT *
                              FROM Asset
                              WHERE Category LIKE '%#URL.Category#%'
                              </cfquery>

                              <cfoutput query="qIndex">

                              <cfquery name="qFilter" datasource="#appDSN#">
                              SELECT CATEGORY, AssetID
                              FROM Asset
                              WHERE assetID = #qIndex.AssetID#
                              </cfquery>

                              <cfset catids=ValueList(qFilter.Category)>


                              <cfquery name="qCat" datasource="#appDSN#">
                              SELECT Category From Category WHERE CatID IN (#catids#)
                              </cfquery>
                              <td>
                              Category: <cfloop query="qCat">#qCat.Category#, </cfloop><br>
                              #qIndex.Asset#<br />
                              #qIndex.Reference#<br />
                              </cfoutput>

                              thanks again for the help.
                              paul