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 <[EMAIL PROTECTED]> 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? > > [EMAIL PROTECTED] > > > > > > > > -- > 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/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
