I'm trying to create a script that will run via cron and pull the most recent survey results and put them into emails to the survey administrator. I know this is a possible planned future feature, but I need it now and I thought that it seemed simple enough.
Unfortunately, I'm having a hard time deciphering how best to join all the involved tables.
Does anybody have a SQL statement that will return all the questions and answers specified by a given response id?
Once I have the SQL that will do that, the rest will be easy. I'll just have a text file (or maybe a db table) that stores the most recently emailed response id, and the script will compile all the responses with higher rids, and then update text file with the new most recent rid.
The SQL that generates the chart view of responses would be a great place to start, but I haven't been able to finagle that out quite yet.
Thanks for any help.
w
Full Version: Need SQL for pulling all results of one survey response
Big Red Spark Forums > Big Red Spark.com Forums > Unit Command Climate Assessment and Survey System (UCCASS)
Actually, I managed to answer my own question. Apparently, I was operating on way too little sleep the first several times I tried to tackle this before posting because ten minutes after posting my question I figured it out.
However, since other people may have the same question, here's the SQL that I came up with, adapted from the code that runs when you request a CSV output of survey responses:
The "XXXX" on the second to last line is where you put the survey id that you are grabbing, and the "YYYY" on the same line is where you put the last sequence number that has been processed. I suppose you could leave out the "q.sid= XXXX and" in the where clause to include responses to all surveys.
Also, obviously, you need to replace the "sv_" on each table name with the table name prefix that you are using in your database.
Once you load the results of that query into an array, writing an email notification script shouldn't be too difficult. Just have a table storing that last processed sequence number and update it after looping through the array.
I hope that helps somebody.
w
However, since other people may have the same question, here's the SQL that I came up with, adapted from the code that runs when you request a CSV output of survey responses:
CODE
SELECT GREATEST(rt.sequence, r.sequence) AS seq,
GREATEST(rt.qid, r.qid) AS qid,
from_unixtime(GREATEST(rt.entered, r.entered)) AS entered,
q.question,
av.numeric_value,
GREATEST(av.value, rt.answer) as givenanswer
FROM sv_questions q
LEFT JOIN sv_results r ON q.qid = r.qid
LEFT JOIN sv_results_text rt ON q.qid = rt.qid
LEFT JOIN sv_answer_values av ON r.avid = av.avid
WHERE q.sid = XXXX and GREATEST(rt.sequence, r.sequence) > YYYY
ORDER BY seq, q.page, q.oid;
GREATEST(rt.qid, r.qid) AS qid,
from_unixtime(GREATEST(rt.entered, r.entered)) AS entered,
q.question,
av.numeric_value,
GREATEST(av.value, rt.answer) as givenanswer
FROM sv_questions q
LEFT JOIN sv_results r ON q.qid = r.qid
LEFT JOIN sv_results_text rt ON q.qid = rt.qid
LEFT JOIN sv_answer_values av ON r.avid = av.avid
WHERE q.sid = XXXX and GREATEST(rt.sequence, r.sequence) > YYYY
ORDER BY seq, q.page, q.oid;
The "XXXX" on the second to last line is where you put the survey id that you are grabbing, and the "YYYY" on the same line is where you put the last sequence number that has been processed. I suppose you could leave out the "q.sid= XXXX and" in the where clause to include responses to all surveys.
Also, obviously, you need to replace the "sv_" on each table name with the table name prefix that you are using in your database.
Once you load the results of that query into an array, writing an email notification script shouldn't be too difficult. Just have a table storing that last processed sequence number and update it after looping through the array.
I hope that helps somebody.
w
I'm sure it'll help someone - thank you for the code. 
QUOTE(JHolmes763 @ Oct 9 2007, 10:43 PM) 
I'm sure it'll help someone - thank you for the code.
It did. I ended up needing it.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.