I've created a tutorial to show you how you might handle a simple survey using pear's DB class. This doesn't have any validation, and I'll create another tutorial going into validation. I hope this helps get an understanding of php and how easy it can be to create and handle forms and working with databases.
This has three pages form.php, form_insert.php and connect.php. form.php is the page that just simply has the html form that the user will use. form_insert.php is the page that will handle the form and insert the values into the database. connect.php is the page that handles the connection to the database and will be included in the form_insert.php page.
The database create table statement.
form.php
connect.php
form_insert.php
This has three pages form.php, form_insert.php and connect.php. form.php is the page that just simply has the html form that the user will use. form_insert.php is the page that will handle the form and insert the values into the database. connect.php is the page that handles the connection to the database and will be included in the form_insert.php page.
The database create table statement.
PHP:
create table survey (
surveyID int not null primary key,
knowledge int,
pear int,
surveys int
)form.php
PHP:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Survey</title>
</head>
<body>
<form method="post" action="form_insert.php">
<table align="center">
<tr>
<td>Rate your knowledge of PHP:</td>
<td>
<select name="knowledge">
<option value=''>Please Select</option>
<option value='1'>1</option>
<option value='2'>2</option>
<option value='3'>3</option>
<option value='4'>4</option>
<option value='5'>5</option>
</select>
</td>
</tr>
<tr>
<td>Have you ever used pear DB?</td>
<td>
<select name="pear">
<option value=''>Please Select</option>
<option value='1'>Yes</option>
<option value='0'>No</option>
</select>
</td>
</tr>
<tr>
<td>Do you make alot of surveys?</td>
<td>
<select name="surveys">
<option value=''>Please Select</option>
<option value='1'>Yes</option>
<option value='0'>No</option>
</select>
</td>
</tr>
<tr>
<td><input type='submit' name='Submit'></td>
</tr>
</table>
</form>
</body>
</html>connect.php
PHP:
<?php
/*
include DB.php is part of the pear DB class. This is not a file that you will find
in its current working directory, its in the php include path. You need to have
php configured with pear (--with-pear) and then go into your shell and install DB.
To install pear DB you would simply run the following line in a shell.
pear install DB
*/
include "DB.php";
/*
This is our database connection and we are putting it into a database handle
called $dbh. $dbh is what we will use throughout our code to work with the
database.
you will want to change the following to fit the values associated with your
database.
*/
$dbh = DB::connect('mysql://username:password@host/database');
?>form_insert.php
PHP:
<?php
// include the connection to the database
require_once "connect.php";
/*
Loop through all the post variables assigning the name of the field to the
variable $n and the value of the field to the variable $v.
Because in this example we are only expecting numeric values, we check to see if
the value is numeric. If it is, then we assign the name of the associative array
to the name of the field, which is and in this example has to be the same name
as the field in the database. Then we assign the value to that element in the array.
*/
foreach ($_POST as $n=>$v) {
if (is_numeric($v)) {
$insert[$n] = $v;
}
}
/*
We are using pear DB class to work with our database, and because pear DB is used
for multiple databases, we want to use sequencing. Not all databases allow auto
sequencing like (mysql's auto_increment) or (mssql's identity), so we need to
set up a table to handle these sequences. The database user will need to have
create table permissions for this to work, because pear will create this table
for you the first time it is ran.
$dbh is our database handle, and we are getting the nextID out of our survey
sequencing table. We have named our sequencing table the same as the table
we are associating it to, however when this is ran, it creates a table called
survey_seq.
*/
$insert['surveyID'] = $dbh->nextID('survey');
/*
The example below is the main reason I love the pear DB class. This next statement
is automatically creating my insert statment and executing it for me.
$dbh again is our database handle
autoExecute is going to prepare and execute the query.
"survey" is the table name in the database
$insert is our insert array which will look like this.
Array
(
[knowledge] => 3
[pear] => 1
[surveys] => 0
[surveyID] => 3
)
DB_AUTOQUERY_INSERT is telling autoExecute that we want to
create and execute an insert statement. If you wanted to do an
update it would look something like the following.
$dbh->autoExecute("survey", $insert, DB_AUTOQUERY_UPDATE, "surveyID = $surveyID");
"surveyID = $surveyID" is the where clause, and of course your array would have
to change to not include surveyID in it.
*/
$dbh->autoExecute("survey", $insert, DB_AUTOQUERY_INSERT);
// here we are unsetting the $insert array to free up some memory.
unset($insert);
/*
This is how you might redirect your user to a thank you page or
anywhere else you might want to take them next.
*/
header ("Location: thank_you.php");
?>