Using the MySQL functions discussed in the previous section along with the Structured Query Language (SQL) it is possible to add records to a database table. Records are added to a database table through a form presenting input areas for entering the fields of information. A button then calls a PHP script to write the new information to the table with an SQL INSERT command. A typical input form for adding a new record to the Survey table is shown below.

The form fields are named accordingly:

Before diving into the code, it is helpful to review the syntax for the SQL INSERT statement:

For more a more in depth look at the INSERT Statement see section 2 in the PHP Reference.

The following code is used to process the VisitorSurvey.php form data:

VisitorSurvey.php


<?php

if ($_POST['submit'] == "Submit") 
{

//Collect form data and assign to scalar variables


$Name = $_POST['Name'];
$Email = $_POST['Email'];
$Connection = $_POST['Connection'];
$Residence = $_POST['Residence'];
$Age = $_POST['Age'];
$Gender = $_POST['Gender'];
$Comments = $_POST['Comments'];

//Establish a connection to the Database

$conn = mysql_connect('localhost','root','xyxyxy');

//Select the MySQL database

$db = mysql_select_db('Membership', $conn);

//SQL Statement

$sql = "INSERT INTO Survey (Name,Email,Connection,
Residence,Age,Gender,Comments) VALUES ('$Name', '$Email', 
'$Connection', '$Residence', '$Age', '$Gender', '$Comments')";
	
//Execute SQL Statement and store results as a recordset
	
$rs = mysql_query($sql,$conn);

mysql_close($conn);

}

?>

After the submit button is clicked, a $_POST superglobal array is created containing the form values. The array values are assigned to scalar variables. This simplifes coding of the SQL statement. Next, the mysql_connect() statement is executed. The statement requires three parameters - MySQL server host name, a user name, and password. Here a DSN-less connection string is used to connect to an Access database. A reference to the connection is stored in the variable $conn. This is an example of a PHP reference variable. Unlike scalar and array variables, reference variables are not directly used within a program, but are often used as parameters for other functions. After connecting to the MySQL database server, the next step is to select the database. Since an instance of MySQL server may contain a large number of databases, we use the mysql_select_db function to choose the one used in our application. This function requires two parameters - the name of the database and a reference to the MySQL connection. Following the database selection, an SQL INSERT statement is issued and assigned to the variable $sql. Next, the mysql_query() function executes the SQL statement creating a recordset (set of database records). The recordset is assigned to $rs, another PHP reference variable. Note that the mysql_query() function requires two parameters - $sql (a refernce to the current SQL statement), and $conn (a reference to the current database connection). Finally, the mysql_close() function is called to close the current database connection.

With this type of application, it is generally a good idea peform data validation prior to inserting the data into the database table. This should be done prior to establishing the database connection using the techniques discussed in section 6-2.

In the event of a coding error, the data is not inserted into the database table and PHP will display a cryptic warning or error message. In this type of situation, it is helpful to supress these cryptic messages, add code to manually check for errors, and generate more user-friendly output. This can be done immediately following the mysql_query() statement by checking the status of the newly created recordset - $rs.

Suppose in the previous code that the mysql_query() function contained the parameter $sqlString instead of $sql. PHP would immediately halt execution of the page.

The PHP error can be suppressed using the error control operator "@". With the error suppressed, we can add code to generate a more user-friendly response. This technique is shown below:

VisitorSurvey.php


<?php

if ($_POST['submit'] == "Submit") 
{

//Collect form data and assign to scalar variables


$Name = $_POST['Name'];
$Email = $_POST['Email'];
$Connection = $_POST['Connection'];
$Residence = $_POST['Residence'];
$Age = $_POST['Age'];
$Gender = $_POST['Gender'];
$Comments = $_POST['Comments'];

//Establish a connection to the Database

$conn = @mysql_connect('localhost','root','xyxyxy');

//Select the MySQL database

$db = @mysql_select_db('Membership', $conn);

//SQL Statement

$sql = "INSERT INTO Survey (Name,Email,Connection,
Residence,Age,Gender,Comments) VALUES ('$Name', 
'$Email', '$Connection', '$Residence', '$Age', 
'$Gender', '$Comments')";
	
//Execute SQL Statement and store results as a recordset
	
$rs = @mysql_query($sqlstring,$conn);

//Verify that the record is inserted

if (mysql_affected_rows($conn) == -1)

{

echo "An error has occured. Record insert failed";

}

else

{

echo "Record inserted successfully";

}

mysql_close($conn);

}

?>

Following the mysql_query() function, the mysql_affected_rows() function is used to verify that the record was inserted successfully. The mysql_affected_rows() function is used to return the number of affected rows by the last SQL INSERT statement. It takes the connection id, in this case $conn, as a parameter. The function returns a value of -1 if an error occurs. If the function returns a value of -1, an error message is displayed.