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:
- id: an auto-increment column usually used to identify a specific and/or to do sorts etc.
- fname (VARCHAR): first name of the person in the phonebook entry
- lname (VARCHAR): last name of the person in the phonebook entry
- phonenumber (VARCHAR): the phone number
- email (VARCHAR): the email address
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!