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

[Matthew Macdonald-Wallace]

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.


-- 
Matthew Macdonald-Wallace
matthew at truthisfreedom.org.uk
http://www.threedrunkensysadsonthe.net/