Skip to main content

How to Create an HTML Form Linked with MySQL Database in PHP

If you're looking for example code and detailed discussion on how to create an HTML form that stores data in a MySQL database using PHP then this post might be what you're looking for. I assume that you're familiar with basic HTML, CSS, PHP coding, and  MySQL.

I am going to divide this small project into two parts:

The HTML form itself that takes input from the user and the PHP script that saves it into the database

A table that displays the user-added data that has been saved in the database.

We'll be dealing with the first part in this tutorial.

Again I'd like to break this problem into a few parts so that it's easier for you to understand and probably gives you an insight into how breaking up a problem into smaller chunks can help make things clearer in your mind.

Let's think about it, there is an HTML form (that is HTML code), then there is the PHP code that deals with the user-input data, and the MySQL database itself.

For this tutorial, we'll be creating a simple phonebook app.

MySQL Database and Table

There are a lot of ways to create a database and it'd depend on where you're running your script. I'm assuming you're running this script on your local server and you already know how to create a database and add a MySQL user full access to it. Please create a database phonebook and grant a user of your choice full access.

For this example, we'll be creating a table that contains the following fields/columns:

  1. id: an auto-increment column usually used to identify a specific and/or to do sorts etc.
  2. fname (VARCHAR): first name of the person in the phonebook entry
  3. lname (VARCHAR): last name of the person in the phonebook entry
  4. phonenumber (VARCHAR): the phone number
  5. email (VARCHAR): the email address
Please see MySQL data types for more information. We're using using VARCHAR for all fields for simplicity.

Since the table will be created on the fly in the PHP code you do not need to create it.

HTML Form (index.php)

<?php

// If this script is accessed in any way other than by pressing the save button
if (!isset($_POST['save'])) {
    exit;
}

define('DB', 'phonebook');
define('DB_HOST', 'localhost');
define('DB_USER', 'db_user');
define('DB_PASS', 'db_pass');
define('DB_TABLE', 'table1');

$db = new mysqli(DB_HOST, DB_USER, DB_PASS, DB);

// Check connection
if ($db->connect_errno) {
    echo "Failed to connect to MySQL: " . $mysqli->connect_error;
    exit;
}

// Connection is successfull
// Create table if it doesn't exists
$table_exists = $db->query('SELECT 1 from `' . DB_TABLE . '` LIMIT 1');

if ($table_exists === FALSE) {
    $db->query('CREATE TABLE IF NOT EXISTS `' . DB_TABLE . '` (  
    id int NOT NULL AUTO_INCREMENT,
    fname varchar(20) NOT NULL,
    lname varchar(20) NOT NULL,
    phonenumber varchar(20) NOT NULL,
    email varchar(40) NOT NULL,
    PRIMARY KEY (id)
    );');
}

// Catch form data
$f_name = sanitize($_POST['fname']);
$l_name = sanitize($_POST['lname']);
$phonenumber = sanitize($_POST['phonenumber']);
$email = sanitize($_POST['email']);

// Validate so that empty values do not get saved
if ($f_name == '' || $l_name == '' || $phonenumber == '' || $email == '') {
    echo 'here';
    // Redirect back to the HTML form page
    // Notice we're sending some data over to the page 
    // which we'll be using to show the error message
    header('Location: index.php?saved=false');
    exit;
}

// Using MySQL prepared statements - which is the best practise
// "?" is the placeholders that we will be binding variables to
$stmt = $db->prepare('INSERT INTO `' . DB_TABLE . '` (fname, lname, phonenumber, email) VALUES (?, ?, ?, ?)');

// For the 4 "?" we have 4 "s" which tells PHP that the they are strings
$stmt->bind_param("ssss", $f_name, $l_name, $phonenumber, $email); 
$stmt->execute();

// Close connections
$stmt->close();
$db->close();

// Redirect back to the HTML form page
header('Location: index.php?saved=true');

// Simple data sanitizer
function sanitize($string) {
    return htmlspecialchars(stripslashes(trim($string)), ENT_QUOTES, 'UTF-8', false);
}

The method="post" is the method the browser uses to send the data over to the PHP script /save.php. There are a couple of common methods: GET and POST but for forms like these, POST is usually the right choice.

The type="submit" button upon clicking submits the form, in other words, sends the entered data over to the script to process and save.

PHP Script (save.php)

Again there are several ways to access MySQL with PHP but I'll be using mysqli which is the most common. Please make sure you have the extension installed and enabled by creating a PHP file (say info.php) with the following code:

<?php

phpinfo();

When you access this file you'll see a page similar to the following:



Scroll down or search for "mysqli" and you should see a section similar to the following if the extension is enabled:



Now, time for the main PHP script that does all the database related stuff:

<?php

// If this script is accessed in any way other than by pressing the save button
if (!isset($_POST['save'])) {
    exit;
}

define('DB', 'phonebook');
define('DB_HOST', 'localhost');
define('DB_USER', 'db_user');
define('DB_PASS', 'db_pass');
define('DB_TABLE', 'table1');

$db = new mysqli(DB_HOST, DB_USER, DB_PASS, DB);

// Check connection
if ($db->connect_errno) {
    echo "Failed to connect to MySQL: " . $mysqli->connect_error;
    exit;
}

// Connection is successfull
// Create table if it doesn't exists
$table_exists = $db->query('SELECT 1 from `' . DB_TABLE . '` LIMIT 1');

if ($table_exists === FALSE) {
    $db->query('CREATE TABLE IF NOT EXISTS `' . DB_TABLE . '` (  
    id int NOT NULL AUTO_INCREMENT,
    fname varchar(20) NOT NULL,
    lname varchar(20) NOT NULL,
    phonenumber varchar(20) NOT NULL,
    email varchar(40) NOT NULL,
    PRIMARY KEY (id)
    );');
}

// Catch form data
$f_name = sanitize($_POST['fname']);
$l_name = sanitize($_POST['lname']);
$phonenumber = sanitize($_POST['phonenumber']);
$email = sanitize($_POST['email']);

// Validate so that empty values do not get saved
if ($f_name == '' || $l_name == '' || $phonenumber == '' || $email == '') {
    echo 'here';
    // Redirect back to the HTML form page
    // Notice we're sending some data over to the page 
    // which we'll be using to show the error message
    header('Location: index.php?saved=false');
    exit;
}

// Using prepared statements which is the best practise
$stmt = $db->prepare('INSERT INTO `' . DB_TABLE . '` (fname, lname, phonenumber, email) VALUES (?, ?, ?, ?)');
$stmt->bind_param("ssss", $f_name, $l_name, $phonenumber, $email); // 's' stands for string, 4 s's for 4 strings
$stmt->execute();

// Close connections
$stmt->close();
$db->close();

// Redirect back to the HTML form page
header('Location: index.php?saved=true');

// Simple data sanitizer
function sanitize($string) {
    return htmlspecialchars(stripslashes(trim($string)), ENT_QUOTES, 'UTF-8', false);
}

Please read the PHP comments for details on what specific lines do. Notice how we are doing some simple data sanitization and data validation.

Time to test the script:



Everything is working as we thought and the data entered by the user is in fact getting saved in the database. We can't see the data yet but you can use PhpMyAdmin or the MySQL command line to make sure it is working on your end as well. 

In the next part of this tutorial, we'll be creating a new PHP script to display the data in the form of a table. Stay tuned!

Labels:

Popular posts from this blog

The Best Way(s) to Comment out PHP/HTML Code

PHP supports various styles of comments. Please check the following example: <?php // Single line comment code (); # Single line Comment code2 (); /* Multi Line comment code(); The code inside doesn't run */ // /* This doesn NOT start a multi-line comment block /* Multi line comment block The following line still ends the multi-line comment block //*/ The " # " comment style, though, is rarely used. Do note, in the example, that anything (even a multi-block comment /* ) after a " // " or " # " is a comment, and /* */ around any single-line comment overrides it. This information will come in handy when we learn about some neat tricks next. Comment out PHP Code Blocks Check the following code <?php //* Toggle line if ( 1 ) {      // } else {   ...
Read more

Fix For Toshiba Satellite "RTC Battery is Low" Error (with Pictures)

RTC Battery is Low Error on a Toshiba Satellite laptop "RTC Battery is Low..." An error message flashing while you try to boot your laptop is enough to panic many people. But worry not! "RTC Battery" stands for Real-Time Clock battery which almost all laptops and PCs have on their motherboard to power the clock and sometimes to also keep the CMOS settings from getting erased while the system is switched off.  It is not uncommon for these batteries to last for years before requiring a replacement as the clock consumes very less power. And contrary to what some people tell you - they are not rechargeable or getting charged while your computer or laptop is running. In this article, we'll learn everything about RTC batteries and how to fix the error on your Toshiba Satellite laptop. What is an RTC Battery? RTC or CMOS batteries are small coin-shaped lithium batteries with a 3-volts output. Most laptops use ...
Read more