Wednesday, August 10, 2011

HTML WebForm with PHP and MySQL - Part 3

Part 3 - Creating the MySQL database
Recap: Part 1 | Part 2

In part 1 & 2 we created a webform that will submit the results to an email address. This is great unless you want it to be readable to non-computer geek people. Also there is no record of the form being submitted except for the email. To accomplish this we need to input the results into a database. I choose MySQL as my hosting provider gave this as part of my package.

Step #1 in putting the form data into a SQL database is to create the database and table where the data will reside. There are a couple of ways to accomplish this however I choose to use the built-in SQL manager. This blog article is not to debate the technology you choose to setup the database, just how to format the columns.

A SQL database is made of tables with columns and rows. A table is like an Excel document with columns and rows. The first thing that we need to do is create a column that will be unique for each row. Most everyone calls this "ID" column. Create this as an integer as well as the following options:

CANNOT BE NULL (can't be blank)
AUTO INCREMENT (increases by 1 each time something is put in the database)

You can call this row of data to pull data back out of the database. Now to create the rest of the columns.

Remember in Part 1 we defined the input values for each box in the web form. Now we need to create a column for index value. In my form I used "NAME" as the first box that users fill out, which means that I will need to create a column in the database that will receive that data. Create a TEXT column that will receive text form the input box.

Rinse and repeat for every input value you have created in your form. Once you have your table and all the columns defined, you can check your work by running this command:

DESC tablename

That command will show you all the columns in the table and what they are formatted as (i.e. Integer vs TEXT).

In Part 4 we will show you how to put that data in the database with a PHP script that we call from the <form action="SCRIPT.PHP"> that we defined in step #1 in the webform.

1 comment: