Creating a PHP User Survey: Database Tables and Class File Constructor

User surveys can be an extremely effective marketing tool for any business web site. Visitors can make their voices heard on what products, services and information they would like the site to provide. Site owners can then adjust their marketing plans and promotions around the results of these polls.

In order to build a basic user poll, we must first create the database tables that will hold the poll question and its various responses.

Create the MySQL Polls Table

First, we must build a database table to hold the information on each specific poll. The table will contain:

• a primary key ID number,
• the number of total votes in the poll,
• the poll question,
• the poll’s status (active/inactive),
• whether the poll was deleted,
• the date/time a poll was created,
• the date/time a poll was modified, and
• the date/time a poll was deleted.

Now, run the following MySQL script to create the polls table:

CREATE TABLE devdrive_polls (

poll_id int(10) NOT NULL auto_increment,

poll_vote_cnt int(10) NOT NULL default '0',

poll_question text NOT NULL,

status int(1) NOT NULL default '1',

deleted int(1) NOT NULL default '0',

deleted_dt datetime NULL default NULL,

created_dt datetime NOT NULL default '0000-00-00 00:00:00',

modified_dt datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY  (poll_id)

) TYPE=MyISAM;

Create the MySQL Poll Answers Table

You must also build a table to hold the answers for each poll. Most user polls offer between two and five answers. The choices can vary from a simple “yes/no” to a variety of flavors, colors or genres from which users can select.

The poll answers table contains:

• a primary key ID,
• a foreign key ID (tied to the poll table primary key),
• the poll answer, and
• the number of total votes for that answer

The following MySQL script should be run to create the poll answers table:

CREATE TABLE devdrive_polls_answers (

poll_answer_id int(10) NOT NULL auto_increment,

poll_id int(10) NOT NULL default '0',

poll_answer text NOT NULL,

poll_answer_cnt int(10) NOT NULL default '0',

PRIMARY KEY  (poll_answer_id),

KEY poll_id_rel (poll_id)

) TYPE=MyISAM;

Create the PHP Polls Class

The class.polls.php file will act as the business layer between the information in the database tables and the results displayed to the presentation layer.

The use of the class will also reduce much of the traffic between the displayed web page and the database, which will allow the application to run faster.

The class will contain three variables:

• the poll ID,
• the poll answer ID, and
• the database connection string.

Here is the sample code to build the class and instantiate the variables:

<?php

require_once("DB.php"); //import the PEAR::DB class

class polls { // open the class definition

var $_iPollId; // unique identifier for a poll

var $_iAnswerId; // unique identifier for a poll answer

var $_oConn; //database connection object

Now we instantiate the class constructor method:

function polls($iPollId = '') {//class constructor uses poll ID as optional parameter

$this->_oConn =& DB::connect(DSN); // implement db object

if (DB::isError($this->_oConn) ) {//check database connection

catchExc($this->_oConn->getMessage());

}

// set unique identifier

if (is_int($iPollId)) {

$this->setPollId($iPollId);

}

}

Now that we have established the class, the next step is to create the methods that will read records from the database tables which we will see in the next tutorial, Variable Values and Reading from Tables.