[devtalk] Retrieving monthly counts for multiple choice answers from MySQL for charting

[Ross Clutterbuck]

Thanks again Matt but it didn't work, or rather it did work but it's 
not doing what I want it to do.

I'm going to start from scratch and rethink this because trying to 
retrofit this with more complex requirements is probably more hassle 
than it's worth.

I'll be in touch if/when I get stuck again.

Ross




On 11/01/2011 17:49, Matthew Macdonald-Wallace wrote:
> Hi Ross,
>
> On Mon, 2011-01-10 at 22:28 +0000, Ross Clutterbuck wrote:
>> Hi Matt
>>
>> Sorry, it's a single table:
>>
>> id: INT (11) auto-increment
>> q1: INT(1)
>> q2: INT(1)
>> q3: INT(1)
>> q4: INT(1)
>> posted: DATE
>> comments: TEXT
>> subjects: TEXT
>> product_id: TINYTEXT
>>
>> and everything goes into there. Was always nice and simple when I was
>> just collecting some basic feedback, but I guess this won't do for the
>> trend analysis I'm after. Don't worry about the text fields, they're
>> just free text boxes for additional information.
> OK, in that case, try the following:
>
> SELECT count(q1) AS q1,count(q2) AS q2,count(q3) AS q3,count(q4) AS q4,
> EXTRACT(MONTH FROM posted) AS monthNumber FROM questions WHERE
> EXTRACT(MONTH FROM posted) == $MonthNumber;
>
> That should return a table similar to the following:
>
> -----------------------------------
> | q1 | q2 | q3 | q4 | monthNumber |
> -----------------------------------
> | 2  | 3  | 4  | 12 | 01          |
> | 8  | 9  | 10 | 13 | 02          |
> -----------------------------------
>
> Let me know if this doesn't work! :)
>
>> What would you propose as a more viable/correct database structure?
> I would usually tend to have one table for each "thing" I needed to
> record similar to the following:
>
> Questions
> =========
> QuestionId
> SubjectId
> ProductId
> QuestionPostedDate
> Comments
>
> Subjects
> ========
> SubjectId
> SubjectName
>
> Answers
> =======
> AnswerId
> AnswerValue
> QuestionId
> AnswerDate
>
> Products
> ========
> ProductId
> ProductName
>
> Or something like that anyway... :)
>
> Cheers,
>
> M.
>
>