I don't remember multiple choices being 1 of the options. It was something on my to-do list tho. I'll be having some time on my hands, so I might just work something in when I update the poll mod.
The poll mod takes up so many database fields. Sometimes I wonder if there isn't a more efficient way of storing poll data.
I guess if you want a new DB structure, probably migration from the current one should be the most direct possible. This proposed structure represents a compromise, because it would just solve the problem of wasting dabatase space and allowing more than 15 options.
Basically I suggest that you split the POLLS table into two:
POLL_ANSWER: ANSWER_ID POLL_ID, ANSWER_TEXT, ORDER, <- Order of the answer in the poll COUNT
Then use the POLL_VOTES as you have it now.
This is pretty close to what you have now. It would also mean that migrating from the existing structure to the current one should be really simple.
I can provide further detail, if needed.
This is not the best possible structure, but a reasonable compromise, as I said, considering that there are quite a few installs and changing your code to use this shouldn't be too hard.
Multiple choices are possible now, if options such as ("Both A and C") are used; though when possible single choices exceed four, that can quickly become unwieldy (a, b, c, d, a&b, a&c, a&d, b&c, b&d, c&d, a&b&c, a&b&d, b&c&d, a&c&d, a&b&c&d). It would be much simpler to use checkboxes vs radio buttons and thus allow multiple choices.
Actually Rui, you are wrong. Your method IS the best possible structure. (May be can loose auto increment ANSWER_ID there, since there is ORDER and POLL_ID required) Actually I decided to give it a try right now. However, migration might be the toughest part.
Yeah, guess you can have order and poll_id as the primary key for the POLL_ANSWER table. The poll mod code will have to be redone for this, of course. "Hardest" thing will be the form to create a poll, since the number of answers will now be variable. Probably using a list instead of fixed text fields would be a better option.
Yeah, guess you can have order and poll_id as the primary key for the POLL_ANSWER table. The poll mod code will have to be redone for this, of course. "Hardest" thing will be the form to create a poll, since the number of answers will now be variable. Probably using a list instead of fixed text fields would be a better option.
I still limit it to 15, like + and - signs... (I dont find it reasonable or logical to allow unlimited number of polls) So on For Next, if it sees an empty answer, exits for... And limits the poll like that.
However, database will be a lot lighter... IMO, hardest (well, not hardest, but still need work) part would be "updating" records...
So I went a way to Take all counts of Poll -> Delete answers entirely -> Insert again mode (for empty responses or old stuff not to remain there) and not sure if there is faster way to do that...
I think it works fine on my test server now, also updated pop_delete for that as well (but for Category or Forum deletions, I need to work on it more, but heck, I dont delete cats or forums that much eheh) Most likely I can not make it into a mod, since my poll mod is also modded a lot, but principles remains same.
I could have written a nice little SQL Server script for migration, if you had told me so. Not hard, using cursors. Can't say the same for Access and MySQL, cause it would needed to be done using asp.
DB as following DB Name: strTablePrefix & "POLL_ANSWERS"
POLL_ID (int, not null) ANSWER_TEXT (nvarchar(255), null) ANSWER_ORDER (int, not null) COUNT (default value=0, int, not null)
This is SQL Server version, using GetRows, would be nice if someone know how to apply it to Access and such.
'############## Poll Mod ##################
IsPoll = rsTopic("T_ISPOLL")
Forum_Polls = rsTopic("F_POLLS")
Poll_Status = rsTopic("T_POLLSTATUS")
if IsPoll = 1 then
strSql = "SELECT P.P_LASTVOTE, P.P_WHOVOTES"
' Also removed commas , careful on that
'For i = 1 To 15
' strSql = strSql & ", P.ANSWER" & CStr(i)
' strSql = strSql & ", P.COUNT" & CStr(i)
'Next
strSql = strSql & " FROM " & strTablePrefix & "POLLS P"
strSql = strSql & " WHERE P.TOPIC_ID = " & Topic_ID
set rsPoll = Server.CreateObject("ADODB.Recordset")
rsPoll.Open strSql, my_Conn, adOpenForwardOnly, adLockReadOnly, adCmdText
if not(rsPoll.EOF) or not(rsPoll.BOF) then
Last_Vote = rsPoll("P_LASTVOTE")
strWhoVotes = rsPoll("P_WHOVOTES")
rsPoll.Close
set rsPoll = nothing
strSQL = "SELECT ANSWER_TEXT, ANSWER_COUNT FROM " & strTablePrefix & "POLL_ANSWERS WHERE POLL_ID=" & Topic_ID
set rsPoll = Server.CreateObject("ADODB.Recordset")
rsPoll.Open strSql, my_Conn, adOpenForwardOnly, adLockReadOnly, adCmdText
if not rsPoll.EOF or rsPoll.BOF Then
tumPoll = rsPoll.GetRows
For i=0 to UBound(tumPoll,2)
z = i+1
vAnswers(z) = tumPoll(0,i)
vCount(z) = tumPoll(1,i)
Next
End If
end if
rsPoll.Close
set rsPoll = nothing
end if
if IsPoll = 1 then
pollLink = "poll=1&"
else
pollLink = ""
end if
'##########################################
Also in another Poll mod marked places inside I changed a few lines starting with if to end...
if nNumber <> -1 then
'## Forum_SQL -
strSql = "UPDATE " & strTablePrefix & "POLLS "
strSql = strSql & " SET P_LASTVOTE = '" & DateToStr(strForumTimeAdjust) & "'"
strSql = strSql & " WHERE TOPIC_ID = " & Topic_ID
my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords
strSql = "UPDATE " & strTablePrefix & "POLL_ANSWERS "
strSql = strSql & " SET ANSWER_COUNT = ANSWER_COUNT+1"
strSql = strSql & " WHERE POLL_ID = " & Topic_ID & " AND ANSWER_ORDER = " & nNumber
my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords
Voted = true
'## Forum_SQL -
strSql = "SELECT " & strTablePrefix & "POLLS.P_LASTVOTE"
'For i = 1 To 15
' strSql = strSql & ", " & strTablePrefix & "POLLS.ANSWER" & CStr(i)
' strSql = strSql & ", " & strTablePrefix & "POLLS.COUNT" & CStr(i)
'Next
strSql = strSql & " FROM " & strTablePrefix & "POLLS"
strSql = strSql & " WHERE " & strTablePrefix & "POLLS.TOPIC_ID = " & Topic_ID
set rsPoll = Server.CreateObject("ADODB.Recordset")
rsPoll.open strSql, my_Conn, adOpenForwardOnly, adLockReadOnly, adCmdText
Last_Vote = rsPoll("P_LASTVOTE")
rsPoll.Close
set rsPoll = nothing
strSQL = "SELECT ANSWER_TEXT, ANSWER_COUNT FROM " & strTablePrefix & "POLL_ANSWERS WHERE POLL_ID=" & Topic_ID
set rsPoll = Server.CreateObject("ADODB.Recordset")
rsPoll.Open strSql, my_Conn, adOpenForwardOnly, adLockReadOnly, adCmdText
if not rsPoll.EOF or rsPoll.BOF Then
tumPoll = rsPoll.GetRows
For i=0 to UBound(tumPoll,2)
z = i+1
vAnswers(z) = tumPoll(0,i)
vCount(z) = tumPoll(1,i)
Next
End If
rsPoll.Close
set rsPoll = nothing
Call UpdateVote("0", MemberID, Topic_ID, Forum_ID, Cat_ID)
end if
Poll Mod v2.0.8
Posted - 29 April 2009 : 15:50:20
