Inserting Records

This page contains the minimal code required to process form information for insertion into a MySQL database. The following SQL defines a simple table for experimenting. If you place this in the test database, you will match the location used for the scripts on this page.

CREATE TABLE `test`.`person` (
`personid` INT( 4 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`surname` VARCHAR( 25 ) NOT NULL ,
`forename` VARCHAR( 25 ) NOT NULL ,
`description` VARCHAR( 255 ) NOT NULL
) ENGINE = MYISAM ;

Making A Form

Your first job is to set up the page with a form ready for the user to input their information. The primary key of this table is auto-incrementing. The user will not be choosing a primary key value when entering a record, the database will automatically add the next available integer to each record.

The following script needs to be placed inside the HTML tags required to make a basic page.

<?php
echo "<h1>Add A Person</h1>
<form action='insert.php' method='POST'>
<table>
<tr>
<td>Surname:</td><td><input type='text' name='sname' maxlength='25'></td>
</tr>
<tr>
<td>Forename:</td><td><input type='text' name='fname' maxlength='25'></td>
</tr>
<tr>
<td>Description:</td><td><textarea name='desc' maxlength='255'></textarea></td>
</tr>
<tr>
<td><input type='reset' value='Clear Form'></td><td><input type='submit' value='Add Record'></td>
</tr>
</table>
</form>";
?>

The form has been placed in a table. Notice that the left edges of the form elements line up making the form more readable. The textarea element has been used for the description.

Receving The Form Data

We adapt the script with an IF clause to determine whether or not the form has been submitted.

<?php
echo "<h1>Add A Person</h1>";
if (isset($_POST['sname']))
{
   // connect to database
   $h = "localhost";
   $u = "root";
   $p = "";
   $connection = mysql_connect($h,$u,$p);
   if (!$connection)
   {
      die("Could not connect to the database");
   }
   mysql_select_db("test");
   // user has submitted the form
   $fsurname = $_POST['sname'];
   $fforename = $_POST['fname'];
   $fdescription = $_POST['desc'];
   // Validate prior to submission
   
   // add to database
   $query = "INSERT INTO person (personid, surname, forename, description) VALUES (NULL, '$fsurname', '$fforename', '$fdescription')";
   $result =mysql_query($query);
   // assuming the query was ok
   echo "<p>Record inserted, <a href='insert.php'>Add Another</a>.</p>";
}
else
{
   // show the form
   echo "<form action='insert.php' method='POST'>
   <table>
   <tr>
   <td>Surname:</td><td><input type='text' name='sname' maxlength='25'></td>
   </tr>
   <tr>
   <td>Forename:</td><td><input type='text' name='fname' maxlength='25'></td>
   </tr>
   <tr>
   <td>Description:</td><td><textarea name='desc' maxlength='255'></textarea></td>
   </tr>
   <tr>
   <td><input type='reset' value='Clear Form'></td><td><input type='submit' value='Add Record'></td>
   </tr>
   </table>
   </form>";
}
?>

There are some considerations to make here. The comment reading 'validate prior to submission' indicates the part of the script where you need to add validation code. Remember that we are using these values for a query. If your script contains all of the validation rules it needs, the query has no reason to fail. You can use MySQL functions in PHP to examine the outcome of the query (numbers of records inserted or returned, errors, primary key allocated etc.). When you have filled in the form and submitted it, check the database in PHPMyAdmin to see if it was added correctly.

Notice in this script how the submission of a NULL value is used for auto-incrementing fields.