from my db. I've listed the db structure and the query below.
The problem that I now have is that it only lists poll answers that
have been answered once. I know that this is because it's querying
the Results table (fsnep_pollresults) and that if there are answers
that have not been selected, then they won't show up in the results
table.
I don't know of a good, clean way of making this query include them.
The only way I know to do it is to get a list of the answers and set
up a query that looks for the count of each of the answers. So if
there are 5 answers, I'll have 5 queries. It sounds like a resource
hog to me.
Any thoughts?
Query:
<cfquery name="poll_results" datasource="eatsmart">
SELECT p.p_id,p.p_question, a.pA_answer, COUNT(r.pV_poll_answer) AS total
FROM fsnep_polls p, fsnep_pollAnswers a, fsnep_pollresults r
WHERE p.p_id = a.pA_pollID
AND a.pA_id = r.pV_poll_answer
AND p.p_id = #url.poll#
GROUP BY p.p_question, a.pA_answer, p.p_id
ORDER BY p.p_question, a.pA_answer, p.p_id
</cfquery>
Tables:
<!--- a list of polls. Write to db once for each answer and then
group on display.--->
create table fsnep_polls (
p_id NUMBER Primary Key,
p_date_added date,
p_date_last_used date,
p_question VARCHAR2(400),
p_category VARCHAR2(400),
p_status NUMBER
)
<!--- all possible answers for any poll available --->
create table fsnep_pollAnswers (
pA_id NUMBER Primary Key,
pA_pollID NUMBER REFERENCES fsnep_polls(p_id) ON DELETE CASCADE,
pA_answer VARCHAR2(400)
)
<!--- all votes with their poll and answer ID --->
create table fsnep_pollResults (
pV_id NUMBER Primary Key,
pV_date_added date,
pV_poll_id NUMBER REFERENCES fsnep_polls(p_id),
pV_poll_Answer NUMBER REFERENCES fsnep_pollAnswers(pA_id) ON
DELETE CASCADE,
pV_ip VARCHAR2(20)
)
<!--- all comments with their poll ID --->
create table fsnep_pollComments (
pc_id NUMBER Primary Key,
pc_date_added date,
pc_poll_id NUMBER REFERENCES fsnep_polls(p_id) ON DELETE CASCADE,
pc_name VARCHAR2(100),
pc_comment VARCHAR2(3000),
pc_ip VARCHAR2(20)
)
--
Daniel Kessler
Department of Public and Community Health
University of Maryland
Suite 2387 Valley Drive
College Park, MD 20742-2611
301-405-2545 Phone
www.phi.umd.edu
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
