h031
asked on
ColdFusion Array Need HELP!
Hi guys hope you guys could help me...
I have finish the page where customers will enter their fav. dishes(products) than it will store what they like in the favourites table as show below. After that i would like to do a page where i would enter the age and gender to search, it will than output the 5 most favourite dishes.
(((i want to see in the fav table how many times each product pops up and than count them and sort to see which one appears the most times...)))
So can some one help me with the array to count how many times the product has been selected by the customers and than sort them out in order of most time selected.
i hope u guys could provide me with the coding cause the coldfusion book does not give any nuts on array...
the link below is my database.
http://www.geocities.com/class32e/others/DBrela.jpg
*NOTE: Data in the favourites goes like this-
cfbno productno
1 1
1 4
1 6
2 18
2 20
2 7
3 3
3 18
3 7
.. so on pls help...
thanks!
I have finish the page where customers will enter their fav. dishes(products) than it will store what they like in the favourites table as show below. After that i would like to do a page where i would enter the age and gender to search, it will than output the 5 most favourite dishes.
(((i want to see in the fav table how many times each product pops up and than count them and sort to see which one appears the most times...)))
So can some one help me with the array to count how many times the product has been selected by the customers and than sort them out in order of most time selected.
i hope u guys could provide me with the coding cause the coldfusion book does not give any nuts on array...
the link below is my database.
http://www.geocities.com/class32e/others/DBrela.jpg
*NOTE: Data in the favourites goes like this-
cfbno productno
1 1
1 4
1 6
2 18
2 20
2 7
3 3
3 18
3 7
.. so on pls help...
thanks!
u can do a:
Select top 5 from tablename where age > 20 and gender = 'm'
but to be honest - i am not completely able to understand ur requirement !
K'Rgds
Anand
Select top 5 from tablename where age > 20 and gender = 'm'
but to be honest - i am not completely able to understand ur requirement !
K'Rgds
Anand
ASKER
sorry did not make it clear, what i meant was to view the 5 most favourite products(dishes) from the favourites table.
so after passing through the age and gender, the output will be showing what this age and gender most 5 favourite dishes are.
hope it helps you guys to help me... thanks
so after passing through the age and gender, the output will be showing what this age and gender most 5 favourite dishes are.
hope it helps you guys to help me... thanks
ok - it seems like the favourites table is gonna have just the 5 records [that r of favourites]
<cfquery name="x">
select age,gender from favourites
</cfquery>
<cfoutput query="x">
#age# - #gender#<br>
</cfoutput>
this will give u all the favourites age/gender
let me know
K'Rgds
Anand
<cfquery name="x">
select age,gender from favourites
</cfquery>
<cfoutput query="x">
#age# - #gender#<br>
</cfoutput>
this will give u all the favourites age/gender
let me know
K'Rgds
Anand
ASKER
the table will have mutiple records more than 100 as shown in the question (after editing to make it sound clearer)
so from there it will store go through the table and count.
so from there it will store go through the table and count.
ok - in that case merge my 2 comments & u have ur answere !
K'Rgds
Anand
K'Rgds
Anand
ok - in that case merge my 2 comments & u have ur answere !
K'Rgds
Anand
K'Rgds
Anand
ASKER
To: Anand
erm, i still do not understand... and it does not work.
could u type out the exect coding sorry....
thanks
erm, i still do not understand... and it does not work.
could u type out the exect coding sorry....
thanks
hi,
<cfquery name="x">
Select top 5 from tablename where age > 20 and gender = 'm'
</cfquery>
<cfoutput query="x">
#age# - #gender#<br>
</cfoutput>
but looking at ur table - i wonder ... on what grounds r u gonna select the top 5 records ???
let me know
K'Rgds,
Anand
PS : I am giving u the logic - u may need to alter it a bit to suit ur needs [as they not very clear from the comments above]
<cfquery name="x">
Select top 5 from tablename where age > 20 and gender = 'm'
</cfquery>
<cfoutput query="x">
#age# - #gender#<br>
</cfoutput>
but looking at ur table - i wonder ... on what grounds r u gonna select the top 5 records ???
let me know
K'Rgds,
Anand
PS : I am giving u the logic - u may need to alter it a bit to suit ur needs [as they not very clear from the comments above]
ASKER
To: Anand
erm, i still do not understand... and it does not work.
could u type out the exect coding sorry....
thanks
erm, i still do not understand... and it does not work.
could u type out the exect coding sorry....
thanks
ASKER
not the top five, but the most favourite five which the customers insert into the favourite table.
AND how wld u have the most fav five inserted in the favourites table ?
K'Rgds
Anand
K'Rgds
Anand
ASKER
not the top five, but the most favourite five which the customers insert into the favourite table.
ASKER
i have a page which the customer will input unlimited dishes(products) they like into the favourites table
therefore it give you the bottom table
cfbno productno
1 1
1 4
1 6
2 18
2 20
2 7
3 3
3 18
3 7
*cfbno is the customer's ID
and productno is the product itself.
therefore it give you the bottom table
cfbno productno
1 1
1 4
1 6
2 18
2 20
2 7
3 3
3 18
3 7
*cfbno is the customer's ID
and productno is the product itself.
when i said top 5 - it means favourite 5 as the query wld select the favourites based on some where condition - so eventually - they wld become top5 - right !
ne-ways from what ur saying now is that the favourites table looks like above - what query condition ru gonna be using - cna u pls paste that - so i can modify it & get back to u
K'Rgds
Anand
ne-ways from what ur saying now is that the favourites table looks like above - what query condition ru gonna be using - cna u pls paste that - so i can modify it & get back to u
K'Rgds
Anand
ASKER
<CFQUERY name="listfav" datasource="#application.d sn#">
SELECT *
FROM cfb, gender, favourites, product
WHERE cfb.gendercode = gender.gendercode
and cfb.cfbno = favourites.cfbno
and favourites.productno = product.productno
and age >= #age1#
and age <= #age2#
order by favourites.productno
</CFQUERY>
SELECT *
FROM cfb, gender, favourites, product
WHERE cfb.gendercode = gender.gendercode
and cfb.cfbno = favourites.cfbno
and favourites.productno = product.productno
and age >= #age1#
and age <= #age2#
order by favourites.productno
</CFQUERY>
Since ur already putting a where clause here - i suppose it will return only the most favourites - but then since u need only 5 - do this
<CFQUERY name="listfav" datasource="#application.d sn#">
SELECT Top 5 *
FROM cfb, gender, favourites, product
WHERE cfb.gendercode = gender.gendercode
and cfb.cfbno = favourites.cfbno
and favourites.productno = product.productno
and age >= #age1#
and age <= #age2#
order by favourites.productno
</CFQUERY>
K'Rgds
Anand
<CFQUERY name="listfav" datasource="#application.d
SELECT Top 5 *
FROM cfb, gender, favourites, product
WHERE cfb.gendercode = gender.gendercode
and cfb.cfbno = favourites.cfbno
and favourites.productno = product.productno
and age >= #age1#
and age <= #age2#
order by favourites.productno
</CFQUERY>
K'Rgds
Anand
ASKER
haha... you got it wrong... the idea i meant. i do not want to see the top 5 orders from the fav table
i want to see in the fav table how many times each product pops up and than count them and sort to see which one appears the most times...
sorry..... maybe i am sending the wrong idea across...
i want to see in the fav table how many times each product pops up and than count them and sort to see which one appears the most times...
sorry..... maybe i am sending the wrong idea across...
the main problem your link is not working
http://www.geocities.com/class32e/others/DBrela.jpg
but after provide this sql, I start to understand
SELECT *
FROM cfb, gender, favourites, product
WHERE cfb.gendercode = gender.gendercode
and cfb.cfbno = favourites.cfbno
and favourites.productno = product.productno
and age >= #age1#
and age <= #age2#
order by favourites.productno
so the search form will send both age and gender
and you are linking favourites table with customer and gender to restrict records to this range of age and this gender.
now, you have selected number of records from favourates table, now you want to group them by product number, then pick the top five.
you need query of query
or as you said you need array processing.
I will try to give you example, but meanwhile check query-of-query syntax, it's wonderful , the idea you query based on the the result of last query
http://www.geocities.com/class32e/others/DBrela.jpg
but after provide this sql, I start to understand
SELECT *
FROM cfb, gender, favourites, product
WHERE cfb.gendercode = gender.gendercode
and cfb.cfbno = favourites.cfbno
and favourites.productno = product.productno
and age >= #age1#
and age <= #age2#
order by favourites.productno
so the search form will send both age and gender
and you are linking favourites table with customer and gender to restrict records to this range of age and this gender.
now, you have selected number of records from favourates table, now you want to group them by product number, then pick the top five.
you need query of query
or as you said you need array processing.
I will try to give you example, but meanwhile check query-of-query syntax, it's wonderful , the idea you query based on the the result of last query
Here is code of "Query-from-Query"
<CFQUERY name="listfav" datasource="#application.d sn#">
SELECT favourites.cfbno , favourites.productno
FROM cfb, gender, favourites, product
WHERE cfb.gendercode = gender.gendercode
and cfb.cfbno = favourites.cfbno
and favourites.productno = product.productno
and age >= #age1#
and age <= #age2#
order by favourites.productno
</CFQUERY>
<cfquery name="top_five" dbtype="QUERY">
SELECT Top 5
select productno , sum(cfbno) as num_hits
from listfav
group by productno
order by productno
</cfquery>
Please review that code, and modify it as you need.
you can do a query of queries, to sort, organize, filter any way you want. and it's not actually connecting to the database
<CFQUERY name="listfav" datasource="#application.d
SELECT favourites.cfbno , favourites.productno
FROM cfb, gender, favourites, product
WHERE cfb.gendercode = gender.gendercode
and cfb.cfbno = favourites.cfbno
and favourites.productno = product.productno
and age >= #age1#
and age <= #age2#
order by favourites.productno
</CFQUERY>
<cfquery name="top_five" dbtype="QUERY">
SELECT Top 5
select productno , sum(cfbno) as num_hits
from listfav
group by productno
order by productno
</cfquery>
Please review that code, and modify it as you need.
you can do a query of queries, to sort, organize, filter any way you want. and it's not actually connecting to the database
ASKER
i can't use query of queries think cause older version 4.5
and i do not want to view the TOP 5 products...
i want to do an array which stores all the products inside, than by comparing with the favourites table (by counting) list down the five products which is listed on the favourites table!
and i do not want to view the TOP 5 products...
i want to do an array which stores all the products inside, than by comparing with the favourites table (by counting) list down the five products which is listed on the favourites table!
Here is the code to populate to array
<CFSET arrayone= ArrayNew(1)>
<CFSET numCount = 1>
<CFOUTPUT QUERY="listfav">
<CFSET Variables.arrayone[#Variab les.numCou nt#] = #group_id#>
<CFSET Variables.numCount = Variables.numCount + 1>
</CFOUTPUT>
I am confused again about the requirment, forget about query_from_query, I need to know if the following logic will work for you
select productno , sum(cfbno) as num_hits
from listfav
group by productno
order by productno
In other words, you want to filter fav table, then count per product to list the top favourates.
<CFSET arrayone= ArrayNew(1)>
<CFSET numCount = 1>
<CFOUTPUT QUERY="listfav">
<CFSET Variables.arrayone[#Variab
<CFSET Variables.numCount = Variables.numCount + 1>
</CFOUTPUT>
I am confused again about the requirment, forget about query_from_query, I need to know if the following logic will work for you
select productno , sum(cfbno) as num_hits
from listfav
group by productno
order by productno
In other words, you want to filter fav table, then count per product to list the top favourates.
Try this...
SELECT TOP 5 productno, count(1) as num_users
FROM favourites
GROUP BY productno
ORDER BY num_users DESC
That should give you the 5 productno's that are in the favourites table most frequently.
SELECT TOP 5 productno, count(1) as num_users
FROM favourites
GROUP BY productno
ORDER BY num_users DESC
That should give you the 5 productno's that are in the favourites table most frequently.
ASKER
well the array help a lot...
thanks...
but the query does not work...
thanks...
but the query does not work...
ASKER
to : danrosenthal
your query is very close to what i wanted but i cannot do the order by num_users desc
so i did this:
<CFQUERY name="test" datasource="#application.d sn#">
SELECT productno, count(1) as num_users
FROM favourites
group by productno
order by productno ASC
</CFQUERY>
<cfoutput query="test">
#test.productno#
#test.num_users#
</cfoutput>
now how to i sort the one the appears the most as the highest and showing the product name?
thanks!
your query is very close to what i wanted but i cannot do the order by num_users desc
so i did this:
<CFQUERY name="test" datasource="#application.d
SELECT productno, count(1) as num_users
FROM favourites
group by productno
order by productno ASC
</CFQUERY>
<cfoutput query="test">
#test.productno#
#test.num_users#
</cfoutput>
now how to i sort the one the appears the most as the highest and showing the product name?
thanks!
I don't know what your actual column names are, so replace productnamefield with the actual name of your column and try this...
<CFQUERY name="test" datasource="#application.d sn#">
SELECT f.productno, count(1) as num_users,
(SELECT product.productnamefield FROM product WHERE productno=f.productno) AS product_name
FROM favourites f
group by f.productno
order by (SELECT COUNT(1) FROM favourites WHERE productno = f.productno) DESC
</CFQUERY>
<cfoutput query="test">
#test.productno#
#test.product_name#
#test.num_users#
</cfoutput>
<CFQUERY name="test" datasource="#application.d
SELECT f.productno, count(1) as num_users,
(SELECT product.productnamefield FROM product WHERE productno=f.productno) AS product_name
FROM favourites f
group by f.productno
order by (SELECT COUNT(1) FROM favourites WHERE productno = f.productno) DESC
</CFQUERY>
<cfoutput query="test">
#test.productno#
#test.product_name#
#test.num_users#
</cfoutput>
ASKER
to : danrosenthal
erm your query does not work , it gives me error.
is there another solution to it?
erm your query does not work , it gives me error.
is there another solution to it?
ASKER
Thanks to all i solved my problem...
this is the coding i used... kinda long... any advise?
well how do i give points to 3 people.
<!--- Header --->
<CFINCLUDE template="../template/head er.cfm">
<cfif #age2# LTE #age1#>
<p>Age Wrongly Entered</p>
<a href="javascript:history.g o(-1)"><p> Back</p></ FONT></a>
<cfelse>
<CFIF #form.genderc# is 3>
<!--- Setting Variable --->
<CFSET testa = 0>
<CFSET testb = 0>
<CFSET testc = 0>
<CFSET testd = 0>
<CFSET teste = 0>
<CFSET testf = 0>
<CFSET testac = 0>
<CFSET testbc = 0>
<CFSET testcc = 0>
<CFSET testdc = 0>
<CFSET testec = 0>
<CFSET testfc = 0>
<!--- Listing of Fav --->
<cfoutput>
<FONT color="Black"><H3><I>View Customer's Favourites Between #age1# - #age2#</I></H3>
</FONT>
</cfoutput>
<cfoutput>
<!--- Query Of Fav --->
<CFQUERY name="test" datasource="#application.d sn#">
SELECT productno, count(1) as num_users
FROM favourites, cfb, gender
where favourites.cfbno = cfb.cfbno
and cfb.gendercode = gender.gendercode
and age >= #age1#
and age <= #age2#
group by productno
order by productno ASC
</CFQUERY>
</cfoutput>
<!--- If No FeedBack Were Made During Period --->
<CFIF #test.recordcount# is 0>
<FONT color="Black"><H3><I>No FeedBack Made Of That Age Group </I></H3></FONT>
<CFELSE>
<cfoutput query="test">
<cfset testz = #test.productno#>
<cfset testzc = #test.num_users#>
<!--- Sorter --->
<cfif #testzc# GT #testac#>
<cfset #teste# = #testd#>
<cfset #testec# = #testdc#>
<cfset #testd# = #testc#>
<cfset #testdc# = #testcc#>
<cfset #testc# = #testb#>
<cfset #testcc# = #testbc#>
<cfset #testb# = #testa#>
<cfset #testbc# = #testac#>
<cfset #testa# = #testz#>
<cfset #testac# = #testzc#>
<!--- #teste#, #testec# +
#testd#, #testdc# +
#testc#, #testcc# +
#testb#, #testbc# +
#testa#, #testac# + --->
<cfelseif #testzc# GT #testbc#>
<cfset #teste# = #testd#>
<cfset #testec# = #testdc#>
<cfset #testd# = #testc#>
<cfset #testdc# = #testcc#>
<cfset #testc# = #testb#>
<cfset #testcc# = #testbc#>
<cfset #testb# = #testz#>
<cfset #testbc# = #testzc#>
<!--- #teste#, #testec# +
#testd#, #testdc# +
#testc#, #testcc# +
#testb#, #testbc# +
#testa#, #testac# + --->
<cfelseif #testzc# GT #testcc#>
<cfset #teste# = #testd#>
<cfset #testec# = #testdc#>
<cfset #testd# = #testc#>
<cfset #testdc# = #testcc#>
<cfset #testc# = #testz#>
<cfset #testcc# = #testzc#>
<!--- #teste#, #testec# +
#testd#, #testdc# +
#testc#, #testcc# +
#testb#, #testbc# +
#testa#, #testac# + --->
<cfelseif #testzc# GT #testdc#>
<cfset #teste# = #testd#>
<cfset #testec# = #testdc#>
<cfset #testd# = #testz#>
<cfset #testdc# = #testzc#>
<!--- #teste#, #testec# +
#testd#, #testdc# +
#testc#, #testcc# +
#testb#, #testbc# +
#testa#, #testac# + --->
<cfelseif #testzc# GT #testec#>
<cfset #teste# = #testz#>
<cfset #testec# = #testzc#>
<!--- #teste#, #testec# +
#testd#, #testdc# +
#testc#, #testcc# +
#testb#, #testbc# +
#testa#, #testac# + --->
</cfif>
</cfoutput>
<CFQUERY name="nameproda" datasource="#application.d sn#">
select name
from product
where product.productno = #testa#
</cfquery>
<CFQUERY name="nameprodb" datasource="#application.d sn#">
select name
from product
where product.productno = #testb#
</cfquery>
<CFQUERY name="nameprodc" datasource="#application.d sn#">
select name
from product
where product.productno = #testc#
</cfquery>
<CFQUERY name="nameprodd" datasource="#application.d sn#">
select name
from product
where product.productno = #testd#
</cfquery>
<CFQUERY name="nameprode" datasource="#application.d sn#">
select name
from product
where product.productno = #teste#
</cfquery>
<!--- <cfoutput>
#nameproda# , #Testac# || #nameprodb# , #Testbc# || #nameprodc# , #Testcc# || #nameprodd# , #Testdc# || #nameprode# , #Testec# ||
</cfoutput> --->
<div align="left">
<table border="0" width="50%" cellspacing="5">
<tr>
<td width="20%" valign="top" align="left"><FONT color="Black" size="4">1st</FONT></td>
<td width="20%" valign="top" align="left">:</td>
<cfoutput query="nameproda">
<td width="20%" valign="top" align="left">#nameproda.na me#</td>
</cfoutput>
<td width="20%" valign="top" align="left"></td>
<td width="20%" valign="top" align="left"></td>
</tr>
<tr>
<td width="20%" valign="top" align="left"><FONT color="Black" size="4">2nd</FONT></td>
<td width="20%" valign="top" align="left">:</td>
<cfoutput query="nameprodb">
<td width="20%" valign="top" align="left">#nameprodb.na me#</td>
</cfoutput>
<td width="20%" valign="top" align="left"></td>
<td width="20%" valign="top" align="left"></td>
</tr>
<tr>
<td width="20%" valign="top" align="left"><FONT color="Black" size="4">3rd</FONT></td>
<td width="20%" valign="top" align="left">:</td>
<cfoutput query="nameprodc">
<td width="20%" valign="top" align="left">#nameprodc.na me#</td>
</cfoutput>
<td width="20%" valign="top" align="left"></td>
<td width="20%" valign="top" align="left"></td>
</tr>
<tr>
<td width="20%" valign="top" align="left"><FONT color="Black" size="4">4th</FONT></td>
<td width="20%" valign="top" align="left">:</td>
<cfoutput query="nameprodd">
<td width="20%" valign="top" align="left">#nameprodd.na me#</td>
</cfoutput>
<td width="20%" valign="top" align="left"></td>
<td width="20%" valign="top" align="left"></td>
</tr>
<tr>
<td width="20%" valign="top" align="left"><FONT color="Black" size="4">5th</FONT></td>
<td width="20%" valign="top" align="left">:</td>
<cfoutput query="nameprode">
<td width="20%" valign="top" align="left">#nameprode.na me#</td>
</cfoutput>
<td width="20%" valign="top" align="left"></td>
<td width="20%" valign="top" align="left"></td>
</tr>
</table>
</div>
</cfif>
<!--- next --->
<cfelse>
<!--- Setting Variable --->
<CFSET testa = 0>
<CFSET testb = 0>
<CFSET testc = 0>
<CFSET testd = 0>
<CFSET teste = 0>
<CFSET testf = 0>
<CFSET testac = 0>
<CFSET testbc = 0>
<CFSET testcc = 0>
<CFSET testdc = 0>
<CFSET testec = 0>
<CFSET testfc = 0>
<!--- Listing of Fav --->
<cfoutput>
<FONT color="Black"><H3><I>View Customer's Favourites Between #age1# - #age2#</I></H3>
</FONT>
</cfoutput>
<cfoutput>
<!--- Query Of Fav --->
<CFQUERY name="test" datasource="#application.d sn#">
SELECT productno, count(1) as num_users
FROM favourites, cfb, gender
where favourites.cfbno = cfb.cfbno
and cfb.gendercode = gender.gendercode
and cfb.gendercode = #form.genderc#
and age >= #age1#
and age <= #age2#
group by productno
order by productno ASC
</CFQUERY>
</cfoutput>
<cfoutput query="test">
<cfset testz = #test.productno#>
<cfset testzc = #test.num_users#>
<!--- Sorter --->
<cfif #testzc# GT #testac#>
<cfset #teste# = #testd#>
<cfset #testec# = #testdc#>
<cfset #testd# = #testc#>
<cfset #testdc# = #testcc#>
<cfset #testc# = #testb#>
<cfset #testcc# = #testbc#>
<cfset #testb# = #testa#>
<cfset #testbc# = #testac#>
<cfset #testa# = #testz#>
<cfset #testac# = #testzc#>
<!--- #teste#, #testec# +
#testd#, #testdc# +
#testc#, #testcc# +
#testb#, #testbc# +
#testa#, #testac# + --->
<cfelseif #testzc# GT #testbc#>
<cfset #teste# = #testd#>
<cfset #testec# = #testdc#>
<cfset #testd# = #testc#>
<cfset #testdc# = #testcc#>
<cfset #testc# = #testb#>
<cfset #testcc# = #testbc#>
<cfset #testb# = #testz#>
<cfset #testbc# = #testzc#>
<!--- #teste#, #testec# +
#testd#, #testdc# +
#testc#, #testcc# +
#testb#, #testbc# +
#testa#, #testac# + --->
<cfelseif #testzc# GT #testcc#>
<cfset #teste# = #testd#>
<cfset #testec# = #testdc#>
<cfset #testd# = #testc#>
<cfset #testdc# = #testcc#>
<cfset #testc# = #testz#>
<cfset #testcc# = #testzc#>
<!--- #teste#, #testec# +
#testd#, #testdc# +
#testc#, #testcc# +
#testb#, #testbc# +
#testa#, #testac# + --->
<cfelseif #testzc# GT #testdc#>
<cfset #teste# = #testd#>
<cfset #testec# = #testdc#>
<cfset #testd# = #testz#>
<cfset #testdc# = #testzc#>
<!--- #teste#, #testec# +
#testd#, #testdc# +
#testc#, #testcc# +
#testb#, #testbc# +
#testa#, #testac# + --->
<cfelseif #testzc# GT #testec#>
<cfset #teste# = #testz#>
<cfset #testec# = #testzc#>
<!--- #teste#, #testec# +
#testd#, #testdc# +
#testc#, #testcc# +
#testb#, #testbc# +
#testa#, #testac# + --->
</cfif>
</cfoutput>
<CFQUERY name="nameproda" datasource="#application.d sn#">
select name
from product
where product.productno = #testa#
</cfquery>
<CFQUERY name="nameprodb" datasource="#application.d sn#">
select name
from product
where product.productno = #testb#
</cfquery>
<CFQUERY name="nameprodc" datasource="#application.d sn#">
select name
from product
where product.productno = #testc#
</cfquery>
<CFQUERY name="nameprodd" datasource="#application.d sn#">
select name
from product
where product.productno = #testd#
</cfquery>
<CFQUERY name="nameprode" datasource="#application.d sn#">
select name
from product
where product.productno = #teste#
</cfquery>
<!--- <cfoutput>
#nameproda# , #Testac# || #nameprodb# , #Testbc# || #nameprodc# , #Testcc# || #nameprodd# , #Testdc# || #nameprode# , #Testec# ||
</cfoutput> --->
<div align="left">
<table border="0" width="50%" cellspacing="5">
<tr>
<td width="20%" valign="top" align="left"><FONT color="Black" size="4">1st</FONT></td>
<td width="20%" valign="top" align="left">:</td>
<cfoutput query="nameproda">
<td width="20%" valign="top" align="left">#nameproda.na me#</td>
</cfoutput>
<td width="20%" valign="top" align="left"></td>
<td width="20%" valign="top" align="left"></td>
</tr>
<tr>
<td width="20%" valign="top" align="left"><FONT color="Black" size="4">2nd</FONT></td>
<td width="20%" valign="top" align="left">:</td>
<cfoutput query="nameprodb">
<td width="20%" valign="top" align="left">#nameprodb.na me#</td>
</cfoutput>
<td width="20%" valign="top" align="left"></td>
<td width="20%" valign="top" align="left"></td>
</tr>
<tr>
<td width="20%" valign="top" align="left"><FONT color="Black" size="4">3rd</FONT></td>
<td width="20%" valign="top" align="left">:</td>
<cfoutput query="nameprodc">
<td width="20%" valign="top" align="left">#nameprodc.na me#</td>
</cfoutput>
<td width="20%" valign="top" align="left"></td>
<td width="20%" valign="top" align="left"></td>
</tr>
<tr>
<td width="20%" valign="top" align="left"><FONT color="Black" size="4">4th</FONT></td>
<td width="20%" valign="top" align="left">:</td>
<cfoutput query="nameprodd">
<td width="20%" valign="top" align="left">#nameprodd.na me#</td>
</cfoutput>
<td width="20%" valign="top" align="left"></td>
<td width="20%" valign="top" align="left"></td>
</tr>
<tr>
<td width="20%" valign="top" align="left"><FONT color="Black" size="4">5th</FONT></td>
<td width="20%" valign="top" align="left">:</td>
<cfoutput query="nameprode">
<td width="20%" valign="top" align="left">#nameprode.na me#</td>
</cfoutput>
<td width="20%" valign="top" align="left"></td>
<td width="20%" valign="top" align="left"></td>
</tr>
</table>
</div>
</cfif>
<p>----------------------- ---------- ---------- ---------< /p>
<A href="vcf.cfm"><FONT>Searc h Another ?</FONT></A>
<p><a href="custfeedback.cfm">Ex it to Customer's FeedBack Menu</a></p>
</cfif>
this is the coding i used... kinda long... any advise?
well how do i give points to 3 people.
<!--- Header --->
<CFINCLUDE template="../template/head
<cfif #age2# LTE #age1#>
<p>Age Wrongly Entered</p>
<a href="javascript:history.g
<cfelse>
<CFIF #form.genderc# is 3>
<!--- Setting Variable --->
<CFSET testa = 0>
<CFSET testb = 0>
<CFSET testc = 0>
<CFSET testd = 0>
<CFSET teste = 0>
<CFSET testf = 0>
<CFSET testac = 0>
<CFSET testbc = 0>
<CFSET testcc = 0>
<CFSET testdc = 0>
<CFSET testec = 0>
<CFSET testfc = 0>
<!--- Listing of Fav --->
<cfoutput>
<FONT color="Black"><H3><I>View Customer's Favourites Between #age1# - #age2#</I></H3>
</FONT>
</cfoutput>
<cfoutput>
<!--- Query Of Fav --->
<CFQUERY name="test" datasource="#application.d
SELECT productno, count(1) as num_users
FROM favourites, cfb, gender
where favourites.cfbno = cfb.cfbno
and cfb.gendercode = gender.gendercode
and age >= #age1#
and age <= #age2#
group by productno
order by productno ASC
</CFQUERY>
</cfoutput>
<!--- If No FeedBack Were Made During Period --->
<CFIF #test.recordcount# is 0>
<FONT color="Black"><H3><I>No FeedBack Made Of That Age Group </I></H3></FONT>
<CFELSE>
<cfoutput query="test">
<cfset testz = #test.productno#>
<cfset testzc = #test.num_users#>
<!--- Sorter --->
<cfif #testzc# GT #testac#>
<cfset #teste# = #testd#>
<cfset #testec# = #testdc#>
<cfset #testd# = #testc#>
<cfset #testdc# = #testcc#>
<cfset #testc# = #testb#>
<cfset #testcc# = #testbc#>
<cfset #testb# = #testa#>
<cfset #testbc# = #testac#>
<cfset #testa# = #testz#>
<cfset #testac# = #testzc#>
<!--- #teste#, #testec# +
#testd#, #testdc# +
#testc#, #testcc# +
#testb#, #testbc# +
#testa#, #testac# + --->
<cfelseif #testzc# GT #testbc#>
<cfset #teste# = #testd#>
<cfset #testec# = #testdc#>
<cfset #testd# = #testc#>
<cfset #testdc# = #testcc#>
<cfset #testc# = #testb#>
<cfset #testcc# = #testbc#>
<cfset #testb# = #testz#>
<cfset #testbc# = #testzc#>
<!--- #teste#, #testec# +
#testd#, #testdc# +
#testc#, #testcc# +
#testb#, #testbc# +
#testa#, #testac# + --->
<cfelseif #testzc# GT #testcc#>
<cfset #teste# = #testd#>
<cfset #testec# = #testdc#>
<cfset #testd# = #testc#>
<cfset #testdc# = #testcc#>
<cfset #testc# = #testz#>
<cfset #testcc# = #testzc#>
<!--- #teste#, #testec# +
#testd#, #testdc# +
#testc#, #testcc# +
#testb#, #testbc# +
#testa#, #testac# + --->
<cfelseif #testzc# GT #testdc#>
<cfset #teste# = #testd#>
<cfset #testec# = #testdc#>
<cfset #testd# = #testz#>
<cfset #testdc# = #testzc#>
<!--- #teste#, #testec# +
#testd#, #testdc# +
#testc#, #testcc# +
#testb#, #testbc# +
#testa#, #testac# + --->
<cfelseif #testzc# GT #testec#>
<cfset #teste# = #testz#>
<cfset #testec# = #testzc#>
<!--- #teste#, #testec# +
#testd#, #testdc# +
#testc#, #testcc# +
#testb#, #testbc# +
#testa#, #testac# + --->
</cfif>
</cfoutput>
<CFQUERY name="nameproda" datasource="#application.d
select name
from product
where product.productno = #testa#
</cfquery>
<CFQUERY name="nameprodb" datasource="#application.d
select name
from product
where product.productno = #testb#
</cfquery>
<CFQUERY name="nameprodc" datasource="#application.d
select name
from product
where product.productno = #testc#
</cfquery>
<CFQUERY name="nameprodd" datasource="#application.d
select name
from product
where product.productno = #testd#
</cfquery>
<CFQUERY name="nameprode" datasource="#application.d
select name
from product
where product.productno = #teste#
</cfquery>
<!--- <cfoutput>
#nameproda# , #Testac# || #nameprodb# , #Testbc# || #nameprodc# , #Testcc# || #nameprodd# , #Testdc# || #nameprode# , #Testec# ||
</cfoutput> --->
<div align="left">
<table border="0" width="50%" cellspacing="5">
<tr>
<td width="20%" valign="top" align="left"><FONT color="Black" size="4">1st</FONT></td>
<td width="20%" valign="top" align="left">:</td>
<cfoutput query="nameproda">
<td width="20%" valign="top" align="left">#nameproda.na
</cfoutput>
<td width="20%" valign="top" align="left"></td>
<td width="20%" valign="top" align="left"></td>
</tr>
<tr>
<td width="20%" valign="top" align="left"><FONT color="Black" size="4">2nd</FONT></td>
<td width="20%" valign="top" align="left">:</td>
<cfoutput query="nameprodb">
<td width="20%" valign="top" align="left">#nameprodb.na
</cfoutput>
<td width="20%" valign="top" align="left"></td>
<td width="20%" valign="top" align="left"></td>
</tr>
<tr>
<td width="20%" valign="top" align="left"><FONT color="Black" size="4">3rd</FONT></td>
<td width="20%" valign="top" align="left">:</td>
<cfoutput query="nameprodc">
<td width="20%" valign="top" align="left">#nameprodc.na
</cfoutput>
<td width="20%" valign="top" align="left"></td>
<td width="20%" valign="top" align="left"></td>
</tr>
<tr>
<td width="20%" valign="top" align="left"><FONT color="Black" size="4">4th</FONT></td>
<td width="20%" valign="top" align="left">:</td>
<cfoutput query="nameprodd">
<td width="20%" valign="top" align="left">#nameprodd.na
</cfoutput>
<td width="20%" valign="top" align="left"></td>
<td width="20%" valign="top" align="left"></td>
</tr>
<tr>
<td width="20%" valign="top" align="left"><FONT color="Black" size="4">5th</FONT></td>
<td width="20%" valign="top" align="left">:</td>
<cfoutput query="nameprode">
<td width="20%" valign="top" align="left">#nameprode.na
</cfoutput>
<td width="20%" valign="top" align="left"></td>
<td width="20%" valign="top" align="left"></td>
</tr>
</table>
</div>
</cfif>
<!--- next --->
<cfelse>
<!--- Setting Variable --->
<CFSET testa = 0>
<CFSET testb = 0>
<CFSET testc = 0>
<CFSET testd = 0>
<CFSET teste = 0>
<CFSET testf = 0>
<CFSET testac = 0>
<CFSET testbc = 0>
<CFSET testcc = 0>
<CFSET testdc = 0>
<CFSET testec = 0>
<CFSET testfc = 0>
<!--- Listing of Fav --->
<cfoutput>
<FONT color="Black"><H3><I>View Customer's Favourites Between #age1# - #age2#</I></H3>
</FONT>
</cfoutput>
<cfoutput>
<!--- Query Of Fav --->
<CFQUERY name="test" datasource="#application.d
SELECT productno, count(1) as num_users
FROM favourites, cfb, gender
where favourites.cfbno = cfb.cfbno
and cfb.gendercode = gender.gendercode
and cfb.gendercode = #form.genderc#
and age >= #age1#
and age <= #age2#
group by productno
order by productno ASC
</CFQUERY>
</cfoutput>
<cfoutput query="test">
<cfset testz = #test.productno#>
<cfset testzc = #test.num_users#>
<!--- Sorter --->
<cfif #testzc# GT #testac#>
<cfset #teste# = #testd#>
<cfset #testec# = #testdc#>
<cfset #testd# = #testc#>
<cfset #testdc# = #testcc#>
<cfset #testc# = #testb#>
<cfset #testcc# = #testbc#>
<cfset #testb# = #testa#>
<cfset #testbc# = #testac#>
<cfset #testa# = #testz#>
<cfset #testac# = #testzc#>
<!--- #teste#, #testec# +
#testd#, #testdc# +
#testc#, #testcc# +
#testb#, #testbc# +
#testa#, #testac# + --->
<cfelseif #testzc# GT #testbc#>
<cfset #teste# = #testd#>
<cfset #testec# = #testdc#>
<cfset #testd# = #testc#>
<cfset #testdc# = #testcc#>
<cfset #testc# = #testb#>
<cfset #testcc# = #testbc#>
<cfset #testb# = #testz#>
<cfset #testbc# = #testzc#>
<!--- #teste#, #testec# +
#testd#, #testdc# +
#testc#, #testcc# +
#testb#, #testbc# +
#testa#, #testac# + --->
<cfelseif #testzc# GT #testcc#>
<cfset #teste# = #testd#>
<cfset #testec# = #testdc#>
<cfset #testd# = #testc#>
<cfset #testdc# = #testcc#>
<cfset #testc# = #testz#>
<cfset #testcc# = #testzc#>
<!--- #teste#, #testec# +
#testd#, #testdc# +
#testc#, #testcc# +
#testb#, #testbc# +
#testa#, #testac# + --->
<cfelseif #testzc# GT #testdc#>
<cfset #teste# = #testd#>
<cfset #testec# = #testdc#>
<cfset #testd# = #testz#>
<cfset #testdc# = #testzc#>
<!--- #teste#, #testec# +
#testd#, #testdc# +
#testc#, #testcc# +
#testb#, #testbc# +
#testa#, #testac# + --->
<cfelseif #testzc# GT #testec#>
<cfset #teste# = #testz#>
<cfset #testec# = #testzc#>
<!--- #teste#, #testec# +
#testd#, #testdc# +
#testc#, #testcc# +
#testb#, #testbc# +
#testa#, #testac# + --->
</cfif>
</cfoutput>
<CFQUERY name="nameproda" datasource="#application.d
select name
from product
where product.productno = #testa#
</cfquery>
<CFQUERY name="nameprodb" datasource="#application.d
select name
from product
where product.productno = #testb#
</cfquery>
<CFQUERY name="nameprodc" datasource="#application.d
select name
from product
where product.productno = #testc#
</cfquery>
<CFQUERY name="nameprodd" datasource="#application.d
select name
from product
where product.productno = #testd#
</cfquery>
<CFQUERY name="nameprode" datasource="#application.d
select name
from product
where product.productno = #teste#
</cfquery>
<!--- <cfoutput>
#nameproda# , #Testac# || #nameprodb# , #Testbc# || #nameprodc# , #Testcc# || #nameprodd# , #Testdc# || #nameprode# , #Testec# ||
</cfoutput> --->
<div align="left">
<table border="0" width="50%" cellspacing="5">
<tr>
<td width="20%" valign="top" align="left"><FONT color="Black" size="4">1st</FONT></td>
<td width="20%" valign="top" align="left">:</td>
<cfoutput query="nameproda">
<td width="20%" valign="top" align="left">#nameproda.na
</cfoutput>
<td width="20%" valign="top" align="left"></td>
<td width="20%" valign="top" align="left"></td>
</tr>
<tr>
<td width="20%" valign="top" align="left"><FONT color="Black" size="4">2nd</FONT></td>
<td width="20%" valign="top" align="left">:</td>
<cfoutput query="nameprodb">
<td width="20%" valign="top" align="left">#nameprodb.na
</cfoutput>
<td width="20%" valign="top" align="left"></td>
<td width="20%" valign="top" align="left"></td>
</tr>
<tr>
<td width="20%" valign="top" align="left"><FONT color="Black" size="4">3rd</FONT></td>
<td width="20%" valign="top" align="left">:</td>
<cfoutput query="nameprodc">
<td width="20%" valign="top" align="left">#nameprodc.na
</cfoutput>
<td width="20%" valign="top" align="left"></td>
<td width="20%" valign="top" align="left"></td>
</tr>
<tr>
<td width="20%" valign="top" align="left"><FONT color="Black" size="4">4th</FONT></td>
<td width="20%" valign="top" align="left">:</td>
<cfoutput query="nameprodd">
<td width="20%" valign="top" align="left">#nameprodd.na
</cfoutput>
<td width="20%" valign="top" align="left"></td>
<td width="20%" valign="top" align="left"></td>
</tr>
<tr>
<td width="20%" valign="top" align="left"><FONT color="Black" size="4">5th</FONT></td>
<td width="20%" valign="top" align="left">:</td>
<cfoutput query="nameprode">
<td width="20%" valign="top" align="left">#nameprode.na
</cfoutput>
<td width="20%" valign="top" align="left"></td>
<td width="20%" valign="top" align="left"></td>
</tr>
</table>
</div>
</cfif>
<p>-----------------------
<A href="vcf.cfm"><FONT>Searc
<p><a href="custfeedback.cfm">Ex
</cfif>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"feedback page that is able to view top 5 products(dishes) using array.
and in the query i am able view by age group and gender"
you don't need array to list top five, just use order by at your query and list only the first five records by using counter or so
<cfset mycount = 0 >
<cfoutput query="MyQuery" >
<cfif mycount lte 5 >
...................
...................
</cfif>
<cfset mycount = #mycount# + 1 >
</cfoutput>