In previous articles I’ve covered the creation of a MySQL database, and how to select information from it. In this article, I’ll cover insertion of user data into a table.

Insertion of data is usually done via a web form. There are many reasons we might want to do this: the obvious cause is to record input from a user, such as a comment in a blog, or an order for a product. Here, we’ll create a simple version of the former.

First, we’ll create a simple HTML5 form on a PHP page:

1. <form action="<?=$_SERVER[‘PHP_SELF’]?>" method="post" role="form">
2. <fieldset>
3. <legend>Please enter your comment</legend>
4. <label for="name" accesskey="n">Your name</label>
5. <input type="text" size="38" maxlength="36" name="name" id=name" />
6. <label for="email" accesskey="e">Your eMail address</label>
7. <input type=”email" size="68" maxlength="66" name="email" id=email" />
8. <label for="comment" accesskey="c">Comment</label>
9. <textarea name="comment" id=comment" rows="5" columns="66"></textarea>
10. <input type="submit" value="Post" name="submit" id=submit" />
11. </fieldset>
12. </form>

This form submits to itself; unlike previous examples, we’ll make every action take place on this page, rather than splitting it across several pages (a form.html and formhandler.php, for example).

Note that we have provided our submit input with a name. This is one way of testing that the form has been submitted: as a named input, the submit button will be converted into a variable, one that we can test. (Obviously, the variable will not exist if the form has not been submitted).

At the top of the form page we’ll write a connection to a database, and connect to an existing comments table within it.

1. <?php $mysql_connection = mysql_connect("server", "user", "password")
2. or trigger_error(mysql_error(),E_USER_ERROR);
3. mysql_select_db("comments", $mysql_connection);?>

The comments table has fields that reflect those of the form (name and email as VARCHAR fields with the same character limits as the maxlength values of the matching form fields, plus two more: an INT field named comment no that will automatically record the comment number via auto increment, and act as a primary key for the table, and a commentate field set as TIMESTAMP that will record the moment that the comment is made, named join date.)

We’ll test that the form has been submitted, convert the variables within it, help make them safe with mysql_real_escape_string, and insert the data:

1. if (isset($_POST['submit'])) {
2. $name = mysql_real_escape_string($_POST['name']);
3. $comment = mysql_real_escape_string($_POST['comment']);
4. $email = mysql_real_escape_string($_POST['email']);
5. $insert = "INSERT INTO comments (name, email, comment)
6. VALUES ( '$name', '$email', '$comment') ";
7. mysql_query($insert);
8. }

Note that we match the data we are going to insert to its appropriate database table field. (For simplicity’s sake I am ignoring any validation that should take place.)

We’ll show the comments recorded in the database next; because of the order in which we execute this page, it should also show the comment that has just been inserted:

1. <? $query = "SELECT * FROM comments ORDER BY commentate";
2. $comments = mysql_query($query, $mysql_connection) or die(mysql_error());
3. $comment = mysql_fetch_assoc($comments);
4. do { ?>
5. <p>Comment by <?=$comment['name']?> on
6. <?=$comment['date']?> </p>
7. <p><?=$comment['comment']?></p>
8. <?php } while ($comment = mysql_fetch_assoc($comments)); ?>

The form will go just below this, so that the user can see the most recent comments and reply to them.