This week, I will teach you how to write a class for poll. Basically, you need to write static function to:

- query your poll question id, question, answer, option count, total vote count and status from tbl_poll
- query user voted record to check whether user has voted before or not.
- Do delete record from tbl_user_vote and insert user_id, qst_id and option value if he has voted.
- Update option count and total vote count.

You can create php file named Poll.php

class Poll {

public static function GetPollQuestion() {

$sql = InfoDb::query(
‘SELECT qst_id, qst, ans1, ans2, ans3, ans4, opt1, opt2, opt3, opt4, votes, status FROM ‘ //the field name is case insensitive, e.g, you can put Qst_id, Qst….
.tbl_poll’ //make sure this table name is same with your table name set in database
.’ WHERE status=1′,
NULL,
NULL
);

$row = $sql->fetchRow();
return self::GetPollQuestionRowObject($row);

}
public static function GetUserVote ($qst_id,$userid) {

$sql = InfoDb::query(
‘SELECT user_id, qst_id, `option` FROM ‘ //you must use correct escape character for the word option, use this “ in stead of ‘’
.’tbl_user_vote’
.’ WHERE qst_id=? and user_id=?’, //set to ? is to prevent sql injection
array(‘integer’,‘integer‘),
array($qst_id, $userid)
);

$row = $sql->fetchRow();
return self::GetUserVoteRowObject($row);

}
public static function UserVoteOption( $userid, $qst_id, $option) {

InfoDb::dml(
“DELETE FROM tbl_user_vote”
.” WHERE qst_id=? and user_id=?”,
array(‘integer’,‘integer’),
array($qst_id,$userid)

);

return InfoDb::dml(
“INSERT INTO tbl_user_vote”
.” (user_id, qst_id, `option`)”
.” VALUES (?,?,?)”,
array(‘integer’,‘integer’,‘integer’),
array($userid, $qst_id, $option)
);

}
public static function IncreasePollAnswerCount($qst_id, $option) {

return InfoDb::dml(
“UPDATE tbl_poll”
.” SET opt$option=opt$option+1, votes=votes+1″
.” WHERE qst_id=?”,
array(‘integer’),
array($qst_id )
);

}

}

What Is SQL Injection?

SQL injection may occurs when a user field is not checked for type constraints. This could take place when a numeric field is to be used in SQL, and the programmer did not check to validate that the user input is numeric.

public static function GetPollQuestionRowObject($row) {

$i = 0;
$o->qst_id = $row[$i++];
$o->qst = $row[$i++];
$o->ans1 = $row[$i++];
$o->ans2 = $row[$i++];
$o->ans3 = $row[$i++];
$o->ans4 = $row[$i++];
$o->opt1 = $row[$i++];
$o->opt2 = $row[$i++];
$o->opt3 = $row[$i++];
$o->opt4 = $row[$i++];
$o->votes = $row[$i++];
$o->status = $row[$i++];

return $o;

}

public static function GetUserVoteRowObject($row) {

$i = 0;
$o->user_id = $row[$i++];
$o->qst_id = $row[$i++];
$o->option = $row[$i++];

return $o;

}

*Please make sure that your row object is follow the sequence of “SELECT” record that you retrieved from database. In this case qst_id come first then qst, if you did not follow, then it will not retrieve your required data and you may get MySQL error.

*The name can be different, e.g $o->qst_id, you can try to put $o->qst_your_id

Ok. Next week will going to share with you how the calculation works for the poll result which is based on percentage value. This is the most interesting part!!!