Skip to main content
A
Anonymous
April 28, 2010
Question

The best way to extract data from MySQL

  • April 28, 2010
  • 1 reply
  • 1766 views

I have a MySQL db that contains information about whether respondents are coming to an airshow, what days they plan to attend and how will they travel (www.hollisterairshow.com/helpusplan.php . I can export data from this to a spreadsheet as the summary info will only be seen by the organizers but ideally I'd like to create a page that will summarize responses. Something like this:

Saturday

Autos: xxxxx Occupants: yyyyyy

Aircraft: xxxxx Occupants: yyyyyy

Other: xxxxx Occupants: yyyyyy

Sunday

Autos: xxxxx Occupants: yyyyy

Aircraft: xxxxx Occupants: yyyyy

Other: xxxxx Occupants: yyyyy

Camping: xxxxx Occupants: yyyyy

How did you hear about the Airshow:

Radio: xxx TV: xxx Newspaper: xxx Magazine: xxx Internet: xxx Other: xxx 

I thought I would create a couple of recordsets, one for respondents who will attend on Saturday and the other for respondents who will attend on Sunday then all I'd need to do is count the number of respondents and sum the number of occupants for each recordset, and count/ sum campers for the whole file and similarly for the "how did you hear about it" question. Is this the best way or am I missing a more elegant solution?

Thanks,

Tony 

This topic has been closed for replies.

1 reply

A
Anonymous
April 29, 2010

Thinking about this some more I seem to have three questions now...

  1. It seems like I'll have to create recordsets for just about every field that I want to display on a results page(the questionnaire is here www.hollisterairshow.com/helpusplan.php) . For most fields this seems like it would work, but I'd hope there's a better/ quicker solution using DW CS4 as this must be a very common task.
  2. One of the fields though has a number entered by the user (number of people in the group) so I'll have to add the contents of the column rather than count the number of reponses, how do I do this using DW CS4?
  3. The final question on the questionnaire asks how people heard about the airshow, it is a radio button group with 6 options, the user must select one option. For this summary field I'd like to display a count and a percentage for each option e.g.

Radio 150 (10%)

TV     300 (20%)

Newspaper  450 (30%)

etc....

Could someone please point me in the right direction on how I'd achieve this?

Thanks,

Tony

B
bregent
Participating Frequently
April 29, 2010

>For this summary field I'd like to display a

>count and a percentage for  each option e.g.

Select Option, Count(*), TotalCount, Count(*) / TotalCount

From MyTable, (Select Count(*) as TotalCount from MyTable)

Group by Option, TotalCount

A
Anonymous
April 30, 2010

I have provided you with the SQL for you to create the recordsets you need. Of course you need to modify the table and column names to your actual database. Before creating the recordset, use your sql tool (ISQL or whatever you use) to verify the results. Once you have your recordsets then you can either use a server behavior to display the results, or code the script by hand.


Thanks again, you're assuming I have knowledge I don't have. The problem I'm having is knowing how to use DW to do this. I can create a Recordset but how do I get the sum or count to appear on the page? This is more of a DW question than a SQL or math question. And I have no idea what ISQL is or how I would use it, I know I can test Recordsets with a test button in DW.

Thanks for your patience

Tony

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices