Creating an Online Survey – Part 2

Last time we set up our form, took the data from that form and emailed it to an email address of our choosing. This time we’re going to take the same form data, put it in a database, read the total values and display them on a page. If you haven’t read the first part, you can do so by clicking here.

Storing Data In A Database

Now that we can collect the data from the form, we want to put it in a database for storage and compilation. So first things first, we need to set up a new database to store the data. Here is the mySQL dump of the table structure:

CREATE TABLE onlinesurvey (
`id` int(11) NOT NULL auto_increment,
`gender` int(11) NOT NULL default '0',
`age` int(11) NOT NULL default '0',
`suptype` int(11) NOT NULL default '0',
`knowledge` int(11) NOT NULL default '0',
`overall` int(11) NOT NULL default '0',
`speed` int(11) NOT NULL default '0',
`repknowledge` int(11) NOT NULL default '0',
`results` int(11) NOT NULL default '0',
`personability` int(11) NOT NULL default '0',
`recommend` int(11) NOT NULL default '0',
PRIMARY KEY  (id)
);

CREATE TABLE `onlinesurvey_totals` (
`male` INT NOT NULL ,
`female` INT NOT NULL ,
`0_8` INT NOT NULL ,
`9_16` INT NOT NULL ,
`17_24` INT NOT NULL ,
`25_32` INT NOT NULL ,
`33_40` INT NOT NULL ,
`41_x` INT NOT NULL ,
`email` INT NOT NULL ,
`phone` INT NOT NULL ,
`little` INT NOT NULL ,
`some` INT NOT NULL ,
`a_lot` INT NOT NULL ,
`expert` INT NOT NULL ,
`1_vd` INT NOT NULL ,
`1_d` INT NOT NULL ,
`1_s` INT NOT NULL ,
`1_vs` INT NOT NULL ,
`1_n` INT NOT NULL ,
`2_vd` INT NOT NULL ,
`2_d` INT NOT NULL ,
`2_s` INT NOT NULL ,
`2_vs` INT NOT NULL ,
`2_n` INT NOT NULL ,
`3_vd` INT NOT NULL ,
`3_d` INT NOT NULL ,
`3_s` INT NOT NULL ,
`3_vs` INT NOT NULL ,
`3_n` INT NOT NULL ,
`4_yes` INT NOT NULL ,
`4_no` INT NOT NULL ,
`5_vd` INT NOT NULL ,
`5_d` INT NOT NULL ,
`5_s` INT NOT NULL ,
`5_vs` INT NOT NULL ,
`5_n` INT NOT NULL ,
`6_nvp` INT NOT NULL ,
`6_sp` INT NOT NULL ,
`6_p` INT NOT NULL ,
`6_vp` INT NOT NULL ,
`6_ep` INT NOT NULL
);

-- Insert the default totals (0)
INSERT INTO `onlinesurvey_totals` 
( `male` , `female` , `0_8` , `9_16` , `17_24` , `25_32` , `33_40` , `41_x` , 
`email` , `phone` , `little` , `some` , `a_lot` , `expert` , `1_vd` , `1_d` , 
`1_s` , `1_vs` , `1_n` , `2_vd` , `2_d` , `2_s` , `2_vs` , `2_n` , `3_vd` , 
`3_d` , `3_s` , `3_vs` , `3_n` , `4_yes` , `4_no` , `5_vd` , `5_d` , `5_s` , 
`5_vs` , `5_n` , `6_nvp` , `6_sp` , `6_p` , `6_vp` , `6_ep`) 
VALUES ('0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', 
'0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', 
'0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0');

Okay, we’ve created our tables. Now we need to connect to them and populate the `onlinesurvey` table with the results, and update the `onlinesurvey_totals` table with the additions. I am using MySQLi, so where you see “mysqli_” replace it with “mysql_” if you are not using mysqli. Let’s connect to the database.

$dbc = mysqli_connect("localhost", "username", "password", "db_name");
if(!$dbc)
{
    die('<span style="color: #F00; font-weight: bold;">Error!</span><br />
	There was error connecting.  mySQL returned the following:<br /><br />'.
	mysqli_connect_errno().'<br />'.mysqli_connect_error());
}

Since we’re now connected, we need to define some values so that “male”, “female”, “email” and “phone” aren’t literal values, but rather numbers that represent the values. We’ll do that by setting up a couple of arrays to reference.

$__g = Array('male' => '0', 'female' => '1');
$__a = Array('0_8' => '0', '9_16' => '1', '17_24' => '2', '25_32' => '3', '33_40' => 
'4', '41_x' => '5');
$__st = Array('email' => '0', 'phone' => '1');
$__k = Array('little' => '0', 'some' => '1', 'lots' => '2', 'expert' => '3');
$__r = Array('yes' => '0', 'no' => '1');

Now that we’ve got that out of the way, let’s insert the values into the table that collects all the data. This is done via a simple INSERT statement:

$sql = "INSERT INTO `onlinesurvey` 
(gender, age, suptype, knowledge, speed, repknowledge, results, personability, recommend) 
VALUES ('".$__g[$_POST['gender']]."', '".$__a[$_POST['age']]."', '".$__st[$_POST['suptype']].
"', '".$__k[$_POST['knowledge']]."', '".$_POST['speed']."', '".$_POST['repknowledge']."',
'".$_POST['results']."', '".$_POST['personability']."', '".$__r[$_POST['recommend']]."')";

$rslt = mysqli_query($dbc, $sql);

if(!$rslt)
{
    die('<span style="color: #F00; font-weight: bold;">Error!</span><br />
There was an error processing your request.  mySQL returned the following:<br />
<br />'.mysqli_errno($dbc).'<br />'.mysqli_error($dbc));
}
else
{

At this point we’re ready for the long part. It’s not hard, just extensive. First we need to set up variables to reference in the sql statement. We will create 41 variables, and give them a value of 1 or 0. I’ll use the ternary operator to decide the value. If you don’t know what the ternary operator is, it’s a very short way of saying “if this, than x, else y”. Here’s how it is set up:
if (expression)?true:false;
Let’s set up our variables:

$male = ($__g[$_POST['gender']] == '0')?'1':'0';
$female = ($__g[$_POST['gender']] == '1')?'1':'0';
$_8 = ($__a[$_POST['age']] == '0')?'1':'0';
$_16 = ($__a[$_POST['age']] == '1')?'1':'0';
$_24 = ($__a[$_POST['age']] == '2')?'1':'0';
$_32 = ($__a[$_POST['age']] == '3')?'1':'0';
$_40 = ($__a[$_POST['age']] == '4')?'1':'0';
$_41 = ($__a[$_POST['age']] == '5')?'1':'0';
$email = ($__st[$_POST['suptype']] == '0')?'1':'0';
$phone = ($__st[$_POST['suptype']] == '1')?'1':'0';
$little = ($__k[$_POST['knowledge']] == '0')?'1':'0';
$some = ($__k[$_POST['knowledge']] == '1')?'1':'0';
$lots = ($__k[$_POST['knowledge']] == '2')?'1':'0';
$exprt = ($__k[$_POST['knowledge']] == '3')?'1':'0';
$_1vd = ($_POST['speed'] == '0')?'1':'0';
$_1d = ($_POST['speed'] == '1')?'1':'0';
$_1s = ($_POST['speed'] == '2')?'1':'0';
$_1vs = ($_POST['speed'] == '3')?'1':'0';
$_1n = ($_POST['speed'] == '4')?'1':'0';
$_2vd = ($_POST['speed'] == '0')?'1':'0';
$_2d = ($_POST['speed'] == '1')?'1':'0';
$_2s = ($_POST['speed'] == '2')?'1':'0';
$_2vs = ($_POST['speed'] == '3')?'1':'0';
$_2n = ($_POST['speed'] == '4')?'1':'0';
$_3vd = ($_POST['repknowledge'] == '0')?'1':'0';
$_3d = ($_POST['repknowledge'] == '1')?'1':'0';
$_3s = ($_POST['repknowledge'] == '2')?'1':'0';
$_3vs = ($_POST['repknowledge'] == '3')?'1':'0';
$_3n = ($_POST['repknowledge'] == '4')?'1':'0';
$_4y = ($_POST['recommend'] == 'yes')?'1':'0';
$_4n = ($_POST['recommend'] == 'no')?'1':'0';
$_5vd = ($_POST['result'] == '0')?'1':'0';
$_5d = ($_POST['result'] == '1')?'1':'0';
$_5s = ($_POST['result'] == '2')?'1':'0';
$_5vs = ($_POST['result'] == '3')?'1':'0';
$_5n = ($_POST['result'] == '4')?'1':'0';
$_6nvp = ($_POST['overall'] == '0')?'1':'0';
$_6sp = ($_POST['overall'] == '1')?'1':'0';
$_6p = ($_POST['overall'] == '2')?'1':'0';
$_6vp = ($_POST['overall'] == '3')?'1':'0';
$_6ep = ($_POST['overall'] == '4')?'1':'0';

We can execute a huge SQL statement that adds 1 or 0 to the value of the column, thus incrementing it, (or not based upon what the user submitted). The incrementation is determined via the lines above. Let’s look at the SQL statement:

$sql2 = "UPDATE `onlinesurvey_totals` 
SET male=male+".$male.", female=female+".$female.", 0_8=0_8+".$_8.", 9_16=9_16+".$_16.",
 17_24=17_24+".$_24.", 25_32=25_32+".$_32.", 33_40=33_40+".$_40.", 41_x=41_x+".$_41.",
 email=email+".$email.", phone=phone+".$phone.", little=little+".$little.", some=some+".
$some.",a_lot=a_lot+".$lots.", expert=expert+".$exprt.", 1_vd=1_vd+".$_1vd.", 1_d=1_d+".$_1d.", 
1_s=1_s+".$_1s.", 1_vs=1_vs+".$_1vs.", 1_n=1_n=".$_1n.", 2_vd=2_vd+".$_2vd.", 2_d=2_d+".$_2d.",
 2_s=2_s+".$_2s.", 2_vs=2_vs+".$_2vs.", 2_n=2_n=".$_2n.", 3_vd=3_vd+".$_3vd.", 3_d=3_d+".$_3d.", 
3_s=3_s+".$_3s.", 3_vs=3_vs+".$_3vs.", 3_n=3_n=".$_3n.", 4_yes=4_yes+".$_4y.", 4_no=4_no+".$_4n.",
 5_vd=5_vd+".$_5vd.", 5_d=5_d+".$_5d.", 5_s=5_s+".$_5s.", 5_vs=5_vs+".$_5vs.", 5_n=5_n=".$_5n.",
 6_nvp=6_nvp+".$_6nvp.", 6_sp=6_sp+".$_6sp.", 6_p=6_p+".$_6p.", 6_vp=6_vp+".$_6vp.", 
6_ep=6_ep+".$_6ep;

$rslt2=mysqli_query($dbc, $sql2);
if(!$rslt2)
{
	die('<span style="color: #F00; font-weight: bold;">Error!</span><br />
	There was an error processing your request.  mySQL returned the following:<br /><br />'.
	mysqli_errno($dbc).'<br />'.mysqli_error($dbc));
}
else
{

With that step done we’ve got everything updated! The way the SQL statement works is to first update the table and set each column to be equal to the current column value plus the value of our variable. If someone had responded “male”, then $male would be equal to 1, while $female is 0, thus incrementing the “male” column, and not the female. It’s pretty simple.

Now that we’ve got our databases updated, let’s show the user what the results are!

[ Next ]