HTML Form Data to SQL Database

This section won’t make sense until you’ve tried these first:

PHP with HTML Form
SQL Databases
Connect PHP to SQL with mysqli

Now we’ll combine everything to make a webpage that can save HTML form data into a database.

Favorite Colors Database

This example database will let people input their favorite color and why they like it.

This project will have three files. You can download the zipped files:

colors database source files

Part 1: HTML Form

We will let the user input a color and a reason why they like the color.

  • The <form> is in a separate HTML file
  • Put <input type=”submit”> inside the <form>. This is the submit button.
  • Give <form> an action=”whatever.php” attribute. This is the php file that will run when the user submits the form data.
  • Give the <form> a method=”get” attribute.
  • Give each <input> tag a name=”whatever” attribute. This is required to make the PHP steps work.

Go back and learn HTML Forms with PHP if those details make no sense.

Part 2a: PHP file to save data to SQL

  • Your PHP page will receive GET data from the HTML form.
  • The “name” attributes in your HTML form have to match the GET indexes in your PHP file.
  • Save these GET values into PHP variables.
  • Create a string containing an SQL INSERT command, and include the variable data in this command so that the user’s data will be inserted into the database.
  • Open a mysqli database connection and run the query.

If that doesn’t make any sense, you need to do PHP with HTML Form, SQL Databases, and Connecting PHP to SQL.

Part 2b: Create SQL Database

The example files will only work if you create an SQL database to match the files. See SQL Databases.

The example database is called “color” and it has a table called “faves.”

The faves table has a “color” column (VARCHAR data type) and a “why” column (also VARCHAR).

I also included an additional auto increment field.

Part 3: PHP page listing the colors

To make your website display the colors from the database, you need to use PHP to retrieve that data. The “getcolors.php” file in the source files shows how to do that.

An explanation for this type of thing is at the end of the section on Databases with PHP.

Debugging / Troubleshooting Tips

Since a lot of things can go wrong in an application like this, you will want to figure out ways to test one piece at a time. This allows you to narrow down the causes of your bugs.

Examples:

  • Test if your HTML form is visiting the right PHP page when it is submitted. You can start with a simple PHP page that only says “Hello World” to check if it reaches that page.
  • Test if the PHP page (“save.php” in the example files) is properly receiving GET data. You can test that by passing URL parameters; this is explained in PHP with HTML form. The key here is to test only the PHP page in isolation from the HTML form or the SQL database to see if that piece is working properly. Instead of sending the GET data to the SQL database, initially set it up so it simply echoes the values.
  • Test if your PHP is creating a working SQL “INSERT” command. If it’s not, the PHP page won’t work. You can test that by trying the SQL command in mysql command line (see SQL databases, bottom section).
  • You can echo your SQL command to see exactly what command the PHP file is creating. Sometimes this will reveal whether the GET data is getting properly combined into an SQL command string. Test this echoed text in mysql.
  • Go into phpmyadmin or mysql command line to check if a new record was created after you run your PHP page.
  • Turn on full PHP error reporting (see PHP page) to get more descriptive PHP error messages.

Overall, you are likely to have something wrong when you first try this. Use these tools to figure out where the problem is!

Debugging is part of being a programmer! When you encounter a problem, ask yourself what possible causes exist and how you can test for each cause.