Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

Displaying Data

Is there a way for me to manipulate the display of the following data in my subscriber database in a way that
says of the 96 people who have signed up, x sell on auctions full time, or x sell a certain category?
Here's what I currenly have:

<cfoutput>
Number of Entries: #listview.recordcount#
</cfoutput>

<CFOUTPUT QUERY="listview">
<A HREF="deleted.cfm?ID=#ID#" OnClick="return test()">#Fname# #Lname#</a>
<br>
Title: #title#
Company: #company#
Address:
#Address# #DeptMailStop# - #City#, #State#
#ZipCode# #FCountry#
Phone: #Phone#
Fax: #Fax#
Email: #Email# <br>
Categories of Products Sell Online:
<cfloop from="1" to="36" index="i">
<cfif IsDefined("Products#i#")>
#Evaluate("Products#i#")#
<cfelse>
&nbsp;
</cfif>
</cfloop><br>
Other Categories, if any):
<cfif #ProdDesc# is not "">
#ProdDesc#
<cfelse>
No other category selected.
</cfif><br>
Online auction selling primary business?
<cfif #PrimBus1# is not "">
#PrimBus1#
<cfelse>
#PrimBus2#
</cfif><br>
Amount of Sales:
<cfloop from="1" to="5" index="i">
<cfif IsDefined("AmountSell#i#")>
#Evaluate("AmountSell#i#")#
<cfelse>
&nbsp;
</cfif>
</cfloop><br>
</CFOUTPUT>
   

Thank you,
Ansia
0
Ansia
Asked:
Ansia
  • 14
  • 9
  • 3
1 Solution
 
anandkpCommented:
can u pls brief up ur q's a bit more.

forget the code - just tell me what u want to do & what r ur db fields.

just give me a brief flow of what u have - thats all & i'll get back to u asap.

K'Rgds
Anand
0
 
AnsiaAuthor Commented:
I have a database with subscriber information like name, address, lots of categories ....

I am wondering if there is a way that I could display something that says of the 96 people who have signed up, x sell on auctions full time, or x sell a certain category?

Can you do seperate listmessages for the cfoutput?  I have about 30 categories though.  I'm not quite sure how to approach this.

Thanks, :)
0
 
anandkpCommented:
hi ansia,

from what i can make out is that u r having a display problem with online & primary selling.

the loop u have currently is proper.

Except that u r running a loop for each 36 [online] & 5 [prim business]times.

can u tell me what exactly r u storing in the DB for the above 2 values.

if i can get to know it - i can suggest a better way - whihc would remove the need of the inner loop & also facilitate a better display.

let me know.

K'Rgds
Anand
0
[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
AnsiaAuthor Commented:
Actually, my display page is correct.

I want to make another page to summarize the data.

I am looking for some means for to look at the all the data collected in summary form. So, can we see in a tabular fashion the answers to any one of the questions answered in the sub form. For example, can we look at all the subs at once and see how many answered the question "Is this your primary business?" Can we see, visually, how many of the 105 answered yes and how many answered no? Otherwise, we need to do it by hand.


Thanks! :)
Ansia
0
 
AnsiaAuthor Commented:
For example:

People who sell online ... number
People who don't sell online ... number

Under 1,000 ... number
5,000 - 10,000 ... number
10,000 - 20,000 ... number
20,000 - 100,000 ... number
Over 10,000 ... number

etc.

I tried using recordcount but I'm not quite getting it.

Thanks,
Ansia
0
 
anandkpCommented:
Hi Ansia,

This is simple
eveytime u display ur record keep a check for Y/N

<Cfset yescount = 0>
<cfet nocount = 0>

<cfquery ....>
  ur query here
</cfquery>

<cfloop> <!--- ie when ur displayign ur report --->

  <cfif recordvalue = 'Y'>
    <cfset YesCOunt = Incrementvalue(yescount)>
  <cfelse>
   <cfset NoCount = incrementvalue(noCount)>
  </cfif>

</cfloop>

then use this - instead of recordcount to display individual counts of yes & No.

Total Yes = #YesCount#
Total No = #NoCount#

hope this helps

===========================================================

else easy way out woudl be ..

U'll need to use 2 queries to display ur data in that case ... one whihc filters all who answered yes & other for all who answered No.

then use record count for displaying total count at the end .. .so u know how many of 105 said yes & how many NO.

===========================================================

K'Rgds
Anand
0
 
AnsiaAuthor Commented:
Thanks!  I'll go give this a try! :)
0
 
AnsiaAuthor Commented:
I get this error:
Error Diagnostic Information
Attribute set validation error in tag CFLOOP

The tag has an invalid attribute combination: the most likely attribute combination is

Required attributes: 'FROM,INDEX,TO'. Optional attributes: 'STEP'.
Other possible combinations are
Required attributes: 'COLLECTION,ITEM'. Optional attributes: None.
Required attributes: 'INDEX,LIST'. Optional attributes: 'DELIMITERS'.
Required attributes: 'QUERY'. Optional attributes: 'ENDROW,STARTROW'.
Required attributes: 'CONDITION'. Optional attributes: None.
The error occurred while processing an element with a general identifier of (CFLOOP), occupying document position (27:1) to (27:8).

0
 
anandkpCommented:
Hi ansia ....

uve got to set the attribute for the cfloop - right ???

i just gave a eg of how to go abt with this logic ... the cfloop was just a dummy to replicate ur code there ... i hope this clears ur doubt

let me know
K'Rgds
Anand
0
 
AnsiaAuthor Commented:
yikes .. boy do I feel silly ... I'll be back ... :)
0
 
anandkpCommented:
OK i'll wait for u :)

0
 
AnsiaAuthor Commented:
Actually, I don't think that I want to CFLOOP the queries but display each field seperately.

Here's my code for what I'm trying to achieve for just one of the fields:
Number of Subscribers who sell under $1,000:
      <cfset yescount = 0>
      <cfquery datasource="aartra14.data" name="AmountSell1">
      SELECT AmountSell1 FROM subscribers
      </cfquery>
<cfoutput query="AmountSell1">
        <cfif recordvalue = 'Under $1,000'>
        <cfset YesCount = Incrementvalue(yescount)>
        </cfif>
   Total Yes = #YesCount#
</cfoutput>

But alas! ..... it doesn't work .........
0
 
AnsiaAuthor Commented:
Sorry ... that was stupid ...
I'm using this code now:

      <cfset yescount = 0>
      <cfquery datasource="aartra14.data" name="AmountSell1">
      SELECT AmountSell1 FROM subscribers
      </cfquery>
<cfoutput query="AmountSell1">
        <cfif recordvalue = 'Y'>
        <cfset YesCount = Incrementvalue(yescount)>
        </cfif>
   Total Yes = #YesCount#
</cfoutput>

And I get this error:
Error in expression:

 recordvalue = 'Y'

 Invalid conditional expression. Perhaps you are using = instead of EQ or IS to compare values.

 The error occurred while processing an element with a general identifier of (CFIF), occupying document position (27:9) to
 (27:32).
0
 
AnsiaAuthor Commented:
I messed up.

For one thing, in my access database, these field names that are checkboxes are not set as Yes/No fields. They are text fields.

And on the input form the checkboxes are named for example,

Checkbox Field name = AmountSell1  value= $Under 1,000
Checkbox Field name = AmountSell2  value= $1,000-$2,000

and so on ..........

My page already has 225 entries.  I can't rewrite this database and lose all my entries.  Given this, is there still a way that I can recordcount the VALUES of each field?

0
 
anandkpCommented:
Hi Ansia,

Now i get u ...
This is even simpler

<CFSET YESCOUNT = 0>
<CFSET NOCOUNT = 0>

<CFSET YESLIST = "$Under 1,000~$1,000-$2,000"><!--- insert all values in a list here - whihc u want to appear in YES list
Since ur list is predefined - u can use whatever vlues here u want --->
<CFSET NOLIST = "$6,000-$7,000~$7,000-$8,000"><!--- insert all values in a list here - whihc u want to appear in NO list
Since ur list is predefined - u can use whatever vlues here u want --->


<CFQUERY datasource="aartra14.data" name="AmountSell1">
     SELECT AmountSell1 FROM subscribers
</CFQUERY>

<CFOUTPUT query="AmountSell1">
     <CFIF LISTCONTAINSNOCASE(YESLIST,AMOUNTSELL1,'~') GT 0>
          <CFSET YESCOUNT = INCREMENTVALUE(YESCOUNT)>
     <CFELSEIF LISTCONTAINSNOCASE(NOLIST,AMOUNTSELL1,'~') GT 0>
          <CFSET NOCOUNT = INCREMENTVALUE(NOCOUNT)>
     </CFIF>
</CFOUTPUT>

<CFOUTPUT>
Total Yes : #YesCOunt#
Total No : #NoCount#
</CFOUTPUT>

<!--- Now i suppose this is what u wanted right ... let me know incase u ahve something else --->
0
 
AnsiaAuthor Commented:
With this code, I'm getting no output display at all. And the total yes and total no all say 0.


<CFSET YESCOUNT = 0>
<CFSET NOCOUNT = 0>

<CFSET YESLIST = "$Under 1,000">
<CFSET NOLIST = "$1,000 - $5,000~$5,000 - $10,000~$10,000 - $25,000~Over $$25,000">

<CFQUERY datasource="aartra14.data" name="AmountSell1">
    SELECT AmountSell1 FROM subscribers
</CFQUERY>

<CFOUTPUT query="AmountSell1">
    <CFIF LISTCONTAINSNOCASE(YESLIST,AMOUNTSELL1,'~') GT 0>
         <CFSET YESCOUNT = INCREMENTVALUE(YESCOUNT)>
    <CFELSEIF LISTCONTAINSNOCASE(NOLIST,AMOUNTSELL1,'~') GT 0>
         <CFSET NOCOUNT = INCREMENTVALUE(NOCOUNT)>
    </CFIF>
</CFOUTPUT>

<CFOUTPUT>
Total Yes : #YesCOunt#
Total No : #NoCount#
</CFOUTPUT>

0
 
anandkpCommented:
Hi Ansia,

try this

<CFOUTPUT query="AmountSell1">
   <CFIF LISTCONTAINSNOCASE(YESLIST,AMOUNTSELL1,'~') NEQ 0>
        <CFSET YESCOUNT = INCREMENTVALUE(YESCOUNT)>
   <CFELSEIF LISTCONTAINSNOCASE(NOLIST,AMOUNTSELL1,'~') NEQ 0>
        <CFSET NOCOUNT = INCREMENTVALUE(NOCOUNT)>
   </CFIF>
</CFOUTPUT>

===========================================================

can u send me the data contained in AMOUNTSELL1 !!!
just use output query & print me the data.

K'Rgds
Anand
0
 
AnsiaAuthor Commented:
you can view the page that has the output display here:

http://www.aartrageous.com/OAB/list.cfm


I want to make a seperate page with the summary info.  
What I'm going to want to summarize is ....

Amount the sell each month ...
Primary Business yes or no .....
Categories they sell ...

You can view the sign up page here:
http://www.aartrageous.com/OAB/bottom.cfm


Thanks! :)

0
 
anandkpCommented:
Hi Ansia,

can u do one thing - pls send me the code of the display page u have - this is imp for me.

+ can u send me a HTML of how u want to display the summary.

if u can do this - i'll write teh complete page for U & then explain u with comments - on how the page was written.

let me know.

u could mail me anand@direct2s.com

K'Rgds
Anand
0
 
AnsiaAuthor Commented:
cool~!! thanks .. it's on its way ...
0
 
lpkuahCommented:
Since you're looking for a summary, how about trying these queries :

-- to get total for primary business
select 'People who sell online', count(PrimBus1) as online
from subscribers
union
select 'People who don't sell online', count(PrimBus2) as online
from subscribers


-- to get total for each category
select 'Under $1,000' as category, count(amountSell1) as amt
from subscribers
union
select '$5,000 - $10,000', count(amountSell2)
from subscribers
union
select '$10,000 - $20,000', count(amountSell3)
from subscribers
union
select '$20,000 - $100,000', count(amountSell4)
from subscribers
union
select 'Over $100,000', count(amountSell5)
from subscribers


"count" function gives us the recordcount of rows that meet the stated criteria
0
 
anandkpCommented:
Hi lpkuah,

yes that can help out - but dont u think - thats hitting tooooooo many queries on a page ???

K'Rgds
Anand
0
 
lpkuahCommented:
Basically it's just 2 queries to get the summary data -

1) to get the total for primary business
2) to get the total for each category

If you'll notice, the second query is actually just one connection to the database because I've used "union"
0
 
AnsiaAuthor Commented:
and then to output the queries, would you list it as:

#PrimBus1.count#

etc?
0
 
lpkuahCommented:
It would appear something like this ->

<cfquery name="Query1" datasource="#DSN#">
select 'People who sell online' as desc, count(PrimBus1) as amt
from subscribers
union
select 'People who don't sell online', count(PrimBus2)
from subscribers
</cfquery>

<cfoutput query="Query1">
    #desc# = #amt#
</cfoutput>


<cfquery name="Query2" datasource="#DSN#">
select 'Under $1,000' as category, count(amountSell1) as amt
from subscribers
union
select '$5,000 - $10,000', count(amountSell2)
from subscribers
union
select '$10,000 - $20,000', count(amountSell3)
from subscribers
union
select '$20,000 - $100,000', count(amountSell4)
from subscribers
union
select 'Over $100,000', count(amountSell5)
from subscribers
</cfquery>

<cfoutput query="Query2">
    #category# = #amt#
</cfoutput>
0
 
AnsiaAuthor Commented:
Thank you very much for all of your help, Anand~! :)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 14
  • 9
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now