In Part I of this series, we started the process of creating user polls for a business web site. Part I gave the layout of the data layer and began the construction of the class file.
In this part we will continue with adding methods to the class file that will enable the administrator to set the variable values and read from the database tables.
The setPollId($iPollId) method sets the value for the _iPollId variable. Since the constructor method calls the setPollId($iPollId) method, we must define that method as well:
function setPollId($iPollId) { // set the _iPollId variable for the class
if (is_int($iPollId)) {
$this->_iPollId = $iPollId;
}
}The setAnswerId($iAnswerId) method sets the value for the _iAnswerId variable.
function setAnswerId($iAnswerId) { // set the _iAnswerId variable for the class
if (is_int($iAnswerId)) {
$this->_iAnswerId = $iAnswerId;
}
}The class will also contain several methods for reading from and writing to the two database tables.
The getPollsCount method returns the number of polls in the poll table.
function getPollsCount($iStatus=false) { // get polls count for paging
// set sql filter
$iStatus ? $sFilter .= " AND status=1" : $sFilter .= "";
$sql = "SELECT
count(poll_id) AS poll_cnt
FROM
devdrive_polls
WHERE deleted=0".$sFilter;
if (DB::isError($iCnt = $this->_oConn->getOne($sql))) {
catchExc($iCnt->getMessage());
return false;
}
return $iCnt;
}The getPolls method returns all the poll data from the poll table into an associative array and sorts it by the sort key.
function getPolls($sSort, $iPage=0) {// get polls list
$sql = "SELECT
poll_id,
poll_vote_cnt,
poll_question,
status,
created_dt,
modified_dt
FROM
devdrive_polls
WHERE
deleted=0
ORDER BY
".$sSort."
LIMIT ".$iPage.", ".ROWCOUNT;
if (DB::isError($rsTmp = $this->_oConn->query($sql))) {
catchExc($rsTmp->getMessage());
return false;
}
// loop result and build return array
$i = 0;
while ($aRow = $rsTmp->fetchRow(DB_FETCHMODE_ASSOC)) {
$return[$i]["Poll Id"] = $aRow["poll_id"];
$return[$i]["Vote Count"] = $aRow["poll_vote_cnt"];
$return[$i]["Question"] = $aRow["poll_question"];
$return[$i]["Status"] = $aRow["status"];
$return[$i]["Created Date"] = strtotime($aRow["created_dt"]);
$return[$i]["Modified Date"] = strtotime($aRow["modified_dt"]);
++$i;
}
return $return;
}The getActivePolls method returns active poll data from the poll table into an associative array. This method also calls the getPollAnswers method to pull the answers for each poll from the poll answers table.
function getActivePolls($iPage=0) {
$sql = "SELECT
poll_id,
poll_vote_cnt,
poll_question
FROM
devdrive_polls
WHERE
status=1
AND deleted=0
ORDER BY
created_dt desc
LIMIT ".$iPage.", 1";
if (DB::isError($rsTmp = $this->_oConn->query($sql))) {
catchExc($rsTmp->getMessage());
return false;
}
// check for results
if ($rsTmp->numRows() > 0) {
// assign result to array
$aRow = $rsTmp->fetchRow(DB_FETCHMODE_ASSOC);
$return["Poll Id"] = $aRow["poll_id"];
$return["Vote Count"] = $aRow["poll_vote_cnt"];
$return["Question"] = $aRow["poll_question"];
$return["Answers"] = $this->getPollAnswers($aRow["poll_id"]); //get answers for this poll
return $return;
}
}The getPoll method returns the poll data for a specific poll from the poll table into an associative array. This method also calls the getPollAnswers method to pull the answers for each poll from the poll answers table.
function getPoll() {// get a specific poll
$sql = "SELECT
poll_id,
poll_vote_cnt,
poll_question,
status,
created_dt,
modified_dt
FROM
devdrive_polls
WHERE
poll_id=".$this->_iPollId;
if (DB::isError($rsTmp = $this->_oConn->query($sql))) {
catchExc($rsTmp->getMessage());
return false;
}
// assign result to array
$aRow = $rsTmp->fetchRow(DB_FETCHMODE_ASSOC);
// build return array
$return["Poll Id"] = $aRow["poll_id"];
$return["Vote Count"] = $aRow["poll_vote_cnt"];
$return["Question"] = $aRow["poll_question"];
$return["Answers"] = $this->getPollAnswers($aRow["poll_id"]);
$return["Status"] = $aRow["status"];
$return["Created Date"] = strtotime($aRow["created_dt"]);
$return["Modified Date"] = strtotime($aRow["modified_dt"]);
return $return;
}The getPollAnswers method returns the answers for a specific poll into an associative array.
function getPollAnswers($iPollId) {// get poll answers
$sql = "SELECT
poll_answer_id,
poll_answer,
poll_answer_cnt
FROM
devdrive_polls_answers
WHERE
poll_id=".$iPollId;
if (DB::isError($rsTmp = $this->_oConn->query($sql))) {
catchExc($rsTmp->getMessage());
return false;
}
// loop through result and build return array
$i = 0;
while ($aRow = $rsTmp->fetchRow(DB_FETCHMODE_ASSOC)) {
$return[$i]["Answer Id"] = $aRow["poll_answer_id"];
$return[$i]["Answer"] = $aRow["poll_answer"];
$return[$i]["Answer Count"] = $aRow["poll_answer_cnt"];
++$i;
}
return $return;
}Now that we have established the functions that read records from the poll table and the answers table, the next step is to create the methods that will write new polls to the database. We will present these methods in Part III.
