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.

[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

[chrome88]

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

[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

[chrome88]

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

[The_ScareCrow]

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

[chrome88 · Accepted solution]

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

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

[chrome88]

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.

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

[chrome88]

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

[paross1]

Entered in error.