Show Selected Value from Query

[shamrock3]

I am running a query,
<cfquery name="getRecords" datasource="******">
SELECT Prods.*, ProdMatrix.*, Categories.*
FROM Prods, ProdMatrix, Cats
WHERE (Prods.ProdID = ProdMatrix.ProdID) AND (Prods.CatID = Cats.CatID)
</cfquery>
All information is displaying properly. What I would like to do is show the user the columns that have lists but have the selected value of that column selected. I tried using the cfselect and all I get is the value of the first record for all the records. How do I set the cfselect to properly display the selected value?

 

[cfStarlight]

SELECT Prods.*, ProdMatrix.*, Categories.*
..

You should avoid using SELECT * (all) for several reasons. It usually retrieves more information than is actually needed, which can harm performance. Second, it can cause problems if the same column exists in more than one of the tables in your FROM clause. Third, when you use SELECT * its impossible to know which columns you're retrieving or which table the columns belong too. That makes it makes it more difficult to help with your code.

What I would like to do is show the user the columns that have lists but have the selected value of that column selected.

I don't understand what that means

 

[shamrock3]

I am using * because I need to display information from all the columns. How do you suggest I proceed?

 

[cfStarlight]

First, can you explain or give an example of what you mean by this

What I would like to do is show the user the columns that have lists but have the selected value of that column selected.

How many columns do you need for your cfselect list? Could you provide an explicit column list? That will make it easier to understand what kind of list you're generating.

SELECT Prods.ColumnName1, Prods.ColumnName2, 
       ProdMatrix.ColumnName3, ... other columns
...

 

[shamrock3]

I am pulling information from three tables that I need to display all the columns from. The Cats.CatID is a list that I need to show all the values in the list but have the selected value, selected. Please let me know if you need more detail. Thanks.

 

[r937]

does your Cats.CatID column contain atomic values, or comma-delimited lists?

if the latter, good luck

if the former, use the ValueList() function

r937.com | rudy.ca

 

[shamrock3]

It is the latter.

 

[r937]

good luck ;-)

r937.com | rudy.ca

 

[cfStarlight]

shamrock3,

I would strongly suggest you redesign your tables so they are not storing comma delimited lists. What you're trying to achieve is a simple task. But storing CSV lists unnecessarily complicates the code and sql queries. It will also lead to a reduction in performance. Truthfully, you wouldn't be having problems like this if you were working with a more normalized design.

 

[shamrock3]

Unfortunatly that is out of my control. I appreciate the input and will forward your recommendations to the DBA.

 

[r937]

if your DBA designed a column to contain a list, that's a violation of first normal form

but something about this doesn't make sense

if Cats.CatID contains a list, how in the world can a query with Prods.CatID = Cats.CatID work?

i betcha Cats.CatID is actually atomic after all

have you had a look at the ValueList function yet?



r937.com | rudy.ca

 

[shamrock3]

I have not used the ValueList function and I am doing some reading on it. Any suggestions for the format?

 

[cfStarlight]

if Cats.CatID contains a list, how in the world can a query with Prods.CatID = Cats.CatID work?

i betcha Cats.CatID is actually atomic after all

Yes, that's a good point. Something about this doesn't add up.

I tried using the cfselect and all I get is the value of the first record for all the records. How do I set the cfselect to properly display the selected value?

Can you provide a small example? Either your CF code or run the .cfm page and use "view source" to copy the html code that was generated.

 

[LyndonOHRC]

Shamrock3,

Did you misuse the term "Cats.CatID is a list" in you problem description? Did you possibly mean that cats.catid is a cloumn with a seperate cat id in each row?

When you say "list" it has a definite inference to a linerar data storage strategy, where data is stored in a single row with some delimeter to divide the elements, usually a comma.



Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey

 

[shamrock3]

I have received some great feedback. Right now I'm going to try to redesign the queries and output from scratch. I took over this project that was half way complete so with all the questionable results I will try a redesign. I'm half way through already and it is looking much better. I'll keep everyone posted on the results.,,,thanks.