Copy link to clipboard
Copied
Copy link to clipboard
Copied
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
quote:
If i redesign the database, what is a better way to store information of multiple results related to one record?
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
Copy link to clipboard
Copied
Copy link to clipboard
Copied
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>
Copy link to clipboard
Copied
Copy link to clipboard
Copied
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,
Copy link to clipboard
Copied