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!
h031Asked:
Who is Participating?
 
anandkpConnect With a Mentor Commented:
instead can u try this for sorting - if thats what u've tried to achieve

after hitting the query - do :

<cfset testz = #listsort(valuelist(test.productno),'textnocase')#>
<cfset testzc = #listsort(valuelist(test.num_users),'textnocase')#>

i dont know - if this is what u need - but may be a workaround this might help u out ... i am not sure

PS : instead of hitting 5/6 queries one after another - use a procedure - so the connection b/w the cf & db server is made only once & it takes less time to execute.

K'Rgds
Anand
0
 
HamdyHassanCommented:
I don't understand the question
"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>

0
 
anandkpCommented:
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
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
h031Author Commented:
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
0
 
anandkpCommented:
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
0
 
h031Author Commented:
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.
0
 
anandkpCommented:
ok - in that case merge my 2 comments & u have ur answere !

K'Rgds
Anand
0
 
anandkpCommented:
ok - in that case merge my 2 comments & u have ur answere !

K'Rgds
Anand
0
 
h031Author Commented:
To: Anand

erm, i still do not understand... and it does not work.
could u type out the exect coding sorry....
thanks
0
 
anandkpCommented:
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]
0
 
h031Author Commented:
To: Anand

erm, i still do not understand... and it does not work.
could u type out the exect coding sorry....
thanks
0
 
h031Author Commented:
not the top five, but the most favourite five which the customers insert into the favourite table.
0
 
anandkpCommented:
AND how wld u have the most fav five inserted in the favourites table ?

K'Rgds
Anand
0
 
h031Author Commented:
not the top five, but the most favourite five which the customers insert into the favourite table.
0
 
h031Author Commented:
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.


0
 
anandkpCommented:
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
0
 
h031Author Commented:
<CFQUERY name="listfav" datasource="#application.dsn#">
     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>
0
 
anandkpCommented:
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.dsn#">
    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
0
 
h031Author Commented:
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...
0
 
HamdyHassanCommented:
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


0
 
HamdyHassanCommented:
Here is code of "Query-from-Query"

<CFQUERY name="listfav" datasource="#application.dsn#">
    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


0
 
h031Author Commented:
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!
0
 
HamdyHassanCommented:
Here is the code to populate to array

<CFSET arrayone= ArrayNew(1)>
<CFSET numCount = 1>

<CFOUTPUT QUERY="listfav">
<CFSET Variables.arrayone[#Variables.numCount#] = #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.

0
 
danrosenthalCommented:
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.
0
 
h031Author Commented:
well the array help a lot...
thanks...
but the query does not work...
0
 
h031Author Commented:
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.dsn#">
     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!
0
 
danrosenthalCommented:
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.dsn#">
    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>

 
0
 
h031Author Commented:
to : danrosenthal

erm your query does not work , it gives me error.
is there another solution to it?
0
 
h031Author Commented:
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/header.cfm">
<cfif #age2# LTE #age1#>
<p>Age Wrongly Entered</p>
<a href="javascript:history.go(-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.dsn#">
    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.dsn#">
select      name
from      product
where      product.productno = #testa#
</cfquery>
<CFQUERY name="nameprodb" datasource="#application.dsn#">
select      name
from      product
where      product.productno = #testb#
</cfquery>
<CFQUERY name="nameprodc" datasource="#application.dsn#">
select      name
from      product
where      product.productno = #testc#
</cfquery>
<CFQUERY name="nameprodd" datasource="#application.dsn#">
select      name
from      product
where      product.productno = #testd#
</cfquery>
<CFQUERY name="nameprode" datasource="#application.dsn#">
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.name#</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.name#</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.name#</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.name#</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.name#</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.dsn#">
    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.dsn#">
select      name
from      product
where      product.productno = #testa#
</cfquery>
<CFQUERY name="nameprodb" datasource="#application.dsn#">
select      name
from      product
where      product.productno = #testb#
</cfquery>
<CFQUERY name="nameprodc" datasource="#application.dsn#">
select      name
from      product
where      product.productno = #testc#
</cfquery>
<CFQUERY name="nameprodd" datasource="#application.dsn#">
select      name
from      product
where      product.productno = #testd#
</cfquery>
<CFQUERY name="nameprode" datasource="#application.dsn#">
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.name#</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.name#</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.name#</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.name#</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.name#</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>Search Another ?</FONT></A>
<p><a href="custfeedback.cfm">Exit to Customer's FeedBack Menu</a></p>
</cfif>
0
All Courses

From novice to tech pro — start learning today.