MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:sheeri kritzer Date:October 19 2005 8:48pm
Subject:Re: Turning tables on their side
View as plain text  
I agree with Brent.

One particular bit of SQL you may find helpful is this:

concat(ifnull(a_id,""),ifnull(a_text,""))

concat with anything and a null value will produce a null value.  That
snippet of sql code will help you get one answer from the 2 the
original database had.  Unless there's ever an answer_id AND an
answer_text, although the example doesn't support that.

so what you want is for something like php to take the result of:

select respondent_id,question_id,concat(ifnull(answer_id,""),ifnull(answer_text,""))
as answer from test order by respondent_id,question_id;

(which, in your example, gets you:)
+------+------+-----------+
| r_id | q_id | answer    |
+------+------+-----------+
|   23 |  201 | 56        |
|   23 |  202 | 20        |
|   23 |  203 | 1         |
|   23 |  204 | Arlington |
|   24 |  201 | 52        |
|   24 |  202 | 21        |
|   24 |  203 | 0         |
|   24 |  204 | Richmond  |
+------+------+-----------+

and process each row -- compare the respondent_id to a variable to see
if you're still on the same respondent, and use the question_id to put
the answer (id or text) into a hash or array.

-Sheeri

On 10/19/05, Brent Baisley <brent@stripped> wrote:
> The person you inherited from formatted the data correctly in my
> opinion. With the existing format, you can index all the data with a
> minimum number of indexes and quickly compile results. It can scale
> to any number of questions without having to modify the underlying
> data structure. It can also easily answer queries like, who missed
> one or more questions?
>
> What you are trying to do is store the data as you see it, which is
> rarely a normalized data model. Your presentation layer should handle
> the formatting for the user. The model you are envisioning would also
> be difficult to query to determine missed questions.
>
> I would use the presentation layer (i.e. Perl, PHP, Python, Ruby,
> Java, etc) to "pivot" the data for display. That's where you also add
> things like coloring to highlight errors or interesting information.
>
> On Oct 19, 2005, at 3:24 PM, Jeffrey Goldberg wrote:
>
> > I suspect that this is the wrong list for this kind of question,
> > but if someone could point me to appropriate sources, I would very
> > much appreciate it.
> >
> > I am new to SQL but inherited project designed by someone who
> > doesn't seem answer his email anymore.
> >
> > Essentially date were collected on the web using PHP inserting
> > things into a MySQL data base.  It took me time, but I now have a
> > handle on what is in which of the 15 tables involved.
> >
> > Each response to each question by each respondent produced its own
> > record (row).  That is, I have something like
> >
> >
> >      respondent_id    question_id  answer_id  answer_text
> >      ----------------------------------------------------
> >
> >       23               201          56         NULL
> >       23               202          20         NULL
> >       23               203           1         NULL
> >       23               204        NULL         Arlington
> >       24               201          52         NULL
> >       24               202          21         NULL
> >       24               203           0         NULL
> >       24               204        NULL         Richmond
> >
> >
> > and so on for other respondent_ids as well.
> >
> > What I would like to get for my users is something that looks like
> >
> >
> >  respondent_id   q201             q202
> > q203               ...
> >
> > ----------------------------------------------------------------------
> > -
> >   23             text-for-ans56   text-for-ans20  text-for-
> > answer1   ...
> >   24             text-for-ans52   text-for-ans21  text-for-
> > answer0   ...
> >
> >
> > So instead of having a record for each response, I'd like to have a
> > single record for each respondent that shows all of that
> > respondents responses.
> >
> > For someone who knows SQL this should be easy.  I suspect that a
> >
> >  group by respondent_id
> >
> > clause will play a role, but I just don't see it.
> >
> > As I said, references to books or sites that I should learn from
> > would also be welcome.
> >
> > -j
> >
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:    http://lists.mysql.com/mysql?
> > unsub=brent@stripped
> >
> >
> >
>
> --
> Brent Baisley
> Systems Architect
> Landover Associates, Inc.
> Search & Advisory Services for Advanced Technology Environments
> p: 212.759.6400/800.759.0577
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>
Thread
Turning tables on their sideJeffrey Goldberg19 Oct
  • Re: Turning tables on their sideBrent Baisley19 Oct
    • Re: Turning tables on their sideJeffrey Goldberg19 Oct
    • Re: Turning tables on their sidesheeri kritzer19 Oct
      • Re: Turning tables on their sideJeffrey Goldberg20 Oct
RE: Turning tables on their sideJon Frisby19 Oct
RE: Turning tables on their sideJon Frisby19 Oct
  • Re: Turning tables on their sideJeffrey Goldberg20 Oct