seperate poll results

[b_hole]

I have 3 mysql tables dealing with polls:
[u]poll_questions[/u]: id, question, status (status is int, where 0 is future poll, 1 is current poll and 2 is past poll that can't vote for anymore),
[u]poll_answers[/u] (for counting non-register voters): id, question_id, answer, hits
[u]members_poll_answers[/u] (for counting register voters): question_id, answer_id, member_id.

Each poll has different number of answers.
My problem is to calculate how many registers-users vote for each of the answers in the current poll (status is 1). I want to avoid as many DB connections as I can, but I can't think of a better way than:
-select id from poll_questions where status=1 (lets call the result $qid)
-select id from poll_answers where question_id=$qid (let say X is the number of relevant answers we found)
-X times selecting the count of votes in members_poll_answers table (each time for different answer).

That means a total of X+2 connecting to DB(!).
Can someone help me here to do the same thing just "smarter"?

[hitman6003]

How are you storing the responses for the nonregistered voters? Is each response a db entry (meaning lots of db entries), or does each response have a line, and a field on that line (hits) indicated how many times it's been answered (meaning one line for each reponse, with the responses counted in that line)?

[markduce]

Hi,

I think I see what you mean, but post back if I am incorrect.

You could use the mysql_num_rows function to count how many votes you have.


$result=mysql_query("SELECT * FROM poll_answers WHERE question_id='$qid");
$number1=mysql_num_rows($result);
$result2=mysql_query("SELECT * FROM members_poll_answers WHERE question_id='$qid");
$number2=mysql_num_rows($result2);

$total=$number1+$number2;

Is that what you mean?

[b_hole]

[!--quoteo(post=350372:date=Feb 28 2006, 04:19 PM:name=hitman6003)--][div class=\'quotetop\']QUOTE(hitman6003 @ Feb 28 2006, 04:19 PM) [snapback]350372[/snapback][/div][div class=\'quotemain\'][!--quotec--]
How are you storing the responses for the nonregistered voters? Is each response a db entry (meaning lots of db entries), or does each response have a line, and a field on that line (hits) indicated how many times it's been answered (meaning one line for each reponse, with the responses counted in that line)?
[/quote]
For nonregistered votes I have on one line for each answer, with "hits" to indicate how many times it has been answer.
I don't have a problem showing the nonregistered vote - that easy. My problem is just with registered vote.

[!--quoteo(post=350376:date=Feb 28 2006, 04:24 PM:name=markduce)--][div class=\'quotetop\']QUOTE(markduce @ Feb 28 2006, 04:24 PM) [snapback]350376[/snapback][/div][div class=\'quotemain\'][!--quotec--]Hi,

I think I see what you mean, but post back if I am incorrect.

You could use the mysql_num_rows function to count how many votes you have.
$result=mysql_query("SELECT * FROM poll_answers WHERE question_id='$qid");
$number1=mysql_num_rows($result);
$result2=mysql_query("SELECT * FROM members_poll_answers WHERE question_id='$qid");
$number2=mysql_num_rows($result2);

$total=$number1+$number2;

Is that what you mean?
[/quote]
You don't understand how my members_poll_answers table works: for each regostered vote I have a different line, that way I can make sure every registered user votes only once for each poll.

[b_hole]

Someone has an idea?