Creating a PHP User Survey: Writing to Database Tables

Nov 22, 2011
PHP
By

In the first two parts of this series, we created the data layer that will hold the polling data and established methods for setting the variable values and reading from the database tables.

In this part, we will build the methods that will write new polls and answers to the tables.

The addPoll method adds a new record to the devdrive_polls table and returns a Boolean value that signals if the record was added successfully.

    function addPoll($aArgs) { // add a poll record

        $sql = "LOCK TABLES devdrive_polls WRITE";  // lock tables to capture unique identifier

        if (DB::isError($rsTmp = $this->_oConn->query($sql))) {

            catchExc($rsTmp->getMessage());
            return false;
        }

        $sql = "INSERT INTO devdrive_polls (        // add new record
                    poll_question,
                    status,
                    created_dt,
                    modified_dt
                ) values (
                    '".$aArgs["Question"]."',
                    1,
                    (NOW()),
                    (NOW())
                )";

        if (DB::isError($rsTmp = $this->_oConn->query($sql))) {

            catchExc($rsTmp->getMessage());
            return false;
        }

        $sql = "SELECT MAX(poll_id) FROM devdrive_ polls";  // get last unique identifier from entry

        if (DB::isError($iPollId = $this->_oConn->getOne($sql))) {

            catchExc($iPollId->getMessage());
            return false;
        }

        $sql = "UNLOCK TABLES";

        if (DB::isError($rsTmp = $this->_oConn->query($sql))) {

            catchExc($rsTmp->getMessage());
            return false;
        }

        settype($iPollId, "integer");   // set unique identifier member variable
        $this->setPollId($iPollId);

        // loop through answers and add records to devdrive_polls_answers table
        $i = 0;
        while (list($key, $val) = each($aArgs["Answers"])) {

            if (strcmp("", $val)) {

                // add records
                $sql = "INSERT INTO devdrive_polls_answers (
                            poll_id,
                            poll_answer
                        ) values (
                            ".$this->_iPollId.",
                            '".$aArgs["Answers"][$i]."'
                        )";

                if (DB::isError($rsTmp = $this->_oConn->query($sql))) {

                    catchExc($rsTmp->getMessage());
                    return false;
                }
            }
            ++$i;
        }

        return true;
    }

The editPoll method updates the polls table and the answers table. The function returns a Boolean that signals if the record was updated successfully.

function editPoll($aArgs) { //edit a poll record

$sql = "UPDATE devdrive_polls SET
poll_question='".$aArgs["Question"]."',
modified_dt=(NOW())
WHERE
poll_id=".$this->_iPollId;

if (DB::isError($rsTmp = $this->_oConn->query($sql))) {

catchExc($rsTmp->getMessage());
return false;
}

// loop through answers and update records
$i = 0;
while (list($key, $val) = each($aArgs["Answers"])) {

if (strcmp("", $val)) {

$sql = "UPDATE devdrive_polls_answers SET
poll_answer='".$val."'
WHERE
poll_answer_id=".$key;

if (DB::isError($rsTmp = $this->_oConn->query($sql))) {

catchExc($rsTmp->getMessage());
return false;
}
}
}
return true;
}

The addVote method adds to the vote count in the polls table and the answers table. The function returns a Boolean that signals if the records were updated successfully and writes a cookie to the user’s browser with the poll’s primary key and the date the vote was submitted.

function addVote() { //add a poll vote

// increment poll count
$sql = "UPDATE devdrive_polls SET
poll_vote_cnt=poll_vote_cnt+1
WHERE
poll_id=".$this->_iPollId;

if (DB::isError($rsTmp = $this->_oConn->query($sql))) {

catchExc($rsTmp->getMessage());
return false;
}

// increment poll answer count
$sql = "UPDATE devdrive_polls_answers SET
poll_answer_cnt=poll_answer_cnt+1
WHERE
poll_answer_id=".$this->_iAnswerId;

if (DB::isError($rsTmp = $this->_oConn->query($sql))) {

catchExc($rsTmp->getMessage());
return false;
}

// set poll vote cookie
setcookie("cPOLL", $this->_iPollId, time()+3600*24*56, "/", "", "");
}

The deletePoll method sets the “deleted” status to true and adds the date the record was deleted. The function returns a Boolean that signals if the record was updated successfully. Please note that the function does not delete the record from the table.

function deletePoll() { // “delete” a poll record

$sql = "UPDATE devdrive_polls SET
deleted=1,
deleted_dt=(NOW())
WHERE
poll_id=".$this->_iPollId;

if (DB::isError($rsTmp = $this->_oConn->query($sql))) {

catchExc($rsTmp->getMessage());
return false;
}

$this->deactivatePoll();
return true;
}

The activatePoll method sets the “active” status to true. The function returns a Boolean that signals if the record was updated successfully.

function activatePoll() { // activate a poll record

$sql = "UPDATE devdrive_polls SET
status=1
WHERE
poll_id=".$this->_iPollId;

if (DB::isError($rsTmp = $this->_oConn->query($sql))) {

catchExc($rsTmp->getMessage());
return false;
}
}

The deactivatePoll method sets the “active” status to false. The function returns a Boolean that signals if the record was updated successfully. After this function, we close the class definition page.

function deactivatePoll() { // deactivate a poll record

$sql = "UPDATE devdrive_polls SET
status=0
WHERE
poll_id=".$this->_iPollId;

if (DB::isError($rsTmp = $this->_oConn->query($sql))) {

catchExc($rsTmp->getMessage());
return false;
}
}

} // close the class definition

?>

Now that we have established the functions that will add and update records to the poll table and the answers table, the next step is to create the forms in the presentation layer that will call these methods. We will present these forms in the next part of the series.

Author: Gerald Hanks
Gerald Hanks has been involved in web development applications since 1996. He has designed applications with JavaScript, ASP.NET and PHP, as well as building databases in MS SQL Server and MySQL. He lives in Houston, Texas.
  • Xenno

    Hey ! Can I use this codes ?

    • Jeff

      By all means, they are all here for our readers to try out!

    • Jeff

      By all means, they are all here for our readers to try out!

  • http://www.dotcominfoway.com/ Custom Software Development

    Thanks for giving the exact codes for the database tables. It will be really helpful for the web developers.

  • http://www.logoguru.co.uk/pages/logo-design-contest.html Logo Design Contest

    Excellent tips, using database is much tough to be organized according to its content but the coding provided makes it easy to stable it. Many Thanks.