The best way to extract data from MySQL

[Guest]

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 

[Guest]

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

[bregent]

>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?

This will give you the count for each mode of transportation as well as the number of occupants for each day/mode

Select Day, TransportMode, Sum (Occupants), Count(*)

group by Day, TransportMode

[bregent]

>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

[Guest]

OK, thanks, I see this will create the info I need to display but just how exactly do I tell DW to do this?

Sorry if this is a very basic question, I just don't see where this goes - nearest I can find is a server behavior, but I think what I should be inserting is a variable with this code behind it.

Thanks again

Tony 

[bregent]

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.

[Guest]

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

[bregent]

So, have you created the advanced recordset and does it work? I haven't used server behaviors in quite a while, but they certainly work with advanced recordsets. You may need to alias aggregate columns (like sum and count)  in order for them to be avaiable to use with the SB.

[Guest]

The page I'm trying to create is here www.hollisterairshow.com/plan-results.php and the questionnaire is here www.hollisterairshow.com/helpusplan.php .

I went ahead and created recordsets using advanced recordsets and these seem to be working, here's a sample SQL

SELECT *
FROM plan
WHERE Saturday = 'Saturday' AND plan.howArriving = 'Auto'

I created a recordset for each combination of day and transportation mode - six in all

This selected the correct records.

To display the count of how many records selected I then used in DW Insert/Data Objects/ Display Record Count/Total Records which generated the following code:

<?php echo $totalRows_rsSatAuto ?>

This worked OK - not elegant but OK and I repeated it for each combination of Day and Transportation mode!!

So there are two problems I'm having:

1. I know I can select the records I want but how do I display the sum of the column containing "How many in your party" - i.e. in the PHP code generated above what do I replace $totalRows_rsSatAuto with?
2. There are two questions using radio buttons where I'd like to display the count and % for each option selected. I can select all records for these questions but what code do I put to sum the number of selections for each option and what code do I put to calculate the percentage

I understand the arithmetic involved which is pretty simple but it's the mechanics of coding that has me stumped. It would really help if you could give me a sample for say the "Are you camping overnight" question which has a "Yes/No" option only. In my mundane way I would create two recordsets, one for "Yes" and one for "No", and use the same technique I describe above for counting Total Records in each recordset, but then I'm stuck with how do I calculate and display the percentages. I must be missing something very obvious as I'm sure there just has to be a more elegant solution.

Thanks so much for your continued interest.

Tony

[bregent]

>I created a recordset for each

>combination of day and transportation  mode - six in all

That's much too difficult. You only need ONE recordset. Create an advanced recordset using the SQL I gave you. Then create a repeating region and insert a dynamic table in that. That gives you everything for your first set of results. The other sets (camping and how heard) can use the same method. So all together, not more than 3 queries and recordsets are needed for the entire page.

>To display the count of how many records selected

>I then used in DW  Insert/Data Objects/ Display Record

>Count/Total Records which generated  the following code:

You do not need to do this. The counts and sums for each day and transportation mode are all in the same query. The rest of your questions should not need an answer once you understand this.

[Guest]

Maybe I'm not explaining myself clearly. I understand conceptually what you are saying. The problem I'm having is the mechanics of displaying the results on a page. Just exactly what do I put in the html to display the counts/totals. I inserted the code you gave me modified for my column names etc and surrounded by <?php and ?> and the web page displayed nothing at all, not even a background picture. I must be missing something blindingly obvious.

Tony

[bregent]

Sorry Tony, maybe I'm not explaining things clearly enough. First of all, I don't do php so I'm not going to give you any code for that. I was suggesting using DW's server behaviors to ouput the results of the query into an html table. Are you familiar with DW's server behaviors?

[Guest]

I've gone through each of your e-mails and I thought I'd focus on the easiest one first which is the "Are you camping overnight" question with a "Yes"/"No" answer. So I set up a new Advanced Recordset and in the SQL: box entered

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

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

Group by RadioGroup1, TotalCount

When I click "Test" it shows "MySQL error #1248 Every derived table must have it's own alias"

I have no idea what this means. Sorry to be such a pain.

[bregent]

Try this syntax instead:

Select RadioGroup1, Count(*), t.TotalCount, Count(*) / t.TotalCount

From  plan, (Select Count(*) as TotalCount from plan) t

Group by  RadioGroup1, t.TotalCount

And to be clear, these tables/column names are just invented by me - you need to substitute your actual names from your database. right?

[Guest]

Sorry for the delay in replying and thanks for your suggested code. I found another web site (tizag.com) with tutorials doing exactly what I was trying to do, I copied the code and it worked. It wasn't the ideal solution as I really wanted to do this using DW. I will be doing another web page in the next few days doing something similar (this time looking for an average rather than a total) so I'll look again at Behaviors and your suggested solution with appropriate modifications.

I really appreciated your patience in walking me through this, the DW learning curve can be pretty steep at times !!

Tony