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.