Forms with PHP & MySQL

January 12, 2012

---

Every once in a while, there’s a need to deploy a simple application to capture information from users. The best approach is to create a form which reads and writes from a database. It’s more secure, scalable, and can provide access to multiple stakeholders should there be a need to share the data collected across an organization.

Build the Database

Based on the form handler below, we’ll have to build the database with the corresponding names of the tables and fields. There are plenty of guides online, but here are the top-level basics:

1. Create a new database; usually an icon from within the control panel of your webserver
2. Create or assign a user to the database



3. Launch phpMyAdmin, and click on the database name – you should be prompted to create a new table; assign 4 new fields



4. Each field represents the data you want to collect
5. Ensure you assign a primary key, and make the ID field auto-increment.

HTML Form

I’ll limit my examples to capturing 3 data points: First Name, Last Name, and Phone Number. Here’s the HTML Form:

<form name="contact" method="post" action="process.php" enctype="multipart/form-data">
    <input type="text" name="nameFirst"/>
    <input type="text" name="nameLast"/>
    <input type="text" name="phone"/>
</form>

Notice that the form will send its values to “process.php” – this is the PHP script that we’ll create in the next step.

PHP Form Handler

The PHP script is fairly straight forward, you’ll need to connect to the database, take the values from the form, and write those values into the database. First, let’s declare your variables – for making a connection to your database, as well as variables for the data you want to capture:

(note: addslashes() is a PHP function that handles apostrohes, quotes, and other special charcters to be more database friendly)

<?php
$username="your_db_username";                 // database username
$password="your_db_password";                 // database password
$database="your_db_name";                     // name of the database
$table="your_db_table";                       // name of the database table
$host="your_db_server";                       // hostname or url of the database server
$fname = addslashes($_POST[nameFirst]);       // value of "nameFirst" from form
$lname = addslashes($_POST[nameLast]);        // value of "nameLast" from form
$phone = $_POST[phone];                       // value of "phone" from form


Now we’ll make the actual connection, with some error handling (useful for troubleshooting):

$connect = mysql_connect($host, $username, $password);
if (!$connect) {
     die('Could not connect: ' . mysql_error());
}
@mysql_select_db($database) or die( "Could not load database");


Now that the script has the data, and the connection to the database, let’s put that data in there!

$db_write="INSERT INTO $table
(first,last,phone)                            // database fields/columns
VALUES
('$fname','$lname','$phone')";                 // values from the form


Let’s make sure the data was successfully recorded – more error handling:

if (!mysql_query($db_write,$connect)) {
     die('Error: ' . mysql_error());
}
echo "Entry successful, Thanks!";              // optional message


and finally, we need to close the connection and end the PHP script:

mysql_close($connect);
?>


Still with me? Good – because you’re done. That’s all there is to it. An interface (HTML Form) to collect data from a user, a script (PHP Form Handler) to take action with the data collected, and a database to store the information. In the next installation, I’ll go over how to pull the data out so that it can be displayed without having to log into phpMyAdmin.