The following is a basic overview of how to use PHP to add data into a MySQL database from a HTML form. The topics covered briefly include:
- How to insert data into a MySQL table using PHP.
- How to convert dates into a format that MySQL database.
- How to create HTML forms and submit data to a PHP file (with multiple GUI elements like text, date pickers, tick boxes and numbers).
- How to retieved data posted from a HTML form in PHP.
- How to open a database connection using the PDO class.
- How to retrieve the ID of a previously submitted query so we can use it in another query. This is important for database tables with relationships.
Importantly information from the HTML form is safely inserted into our database. A basic understanding of database terminology and concepts is required. The following is a crash course in some of those terms:
A database is a collection of records, with each record storing information on a single object. Each record is stored in a table and a table might contain, for example, the names and ages of everyone in a soccer club. In the example database table below, called PLAYERS, the data stored includes names, date of birth and addresses. Example data could include:
| id | name | dob | address |
|---|---|---|---|
| 0034 | ANTHONY | 23/5/1998 | 4 APPLEBY LN, …… |
| 0035 | SARAH | 24/2/1999 | ……. |
| 0035 | PATRICK | 1/11/1994 | ….. |
Each row of the table above is a record and each column of information (eg name) is a field. Each of the fields contain one type of data and we need to tell our database what that data will be, for example DOB will be “date” and Address will be “text”.
The ID field above is very useful in relational databases[1] because it is used as a unique identifier and a link between other related tables. Unique identifiers like this are called primary keys and cannot be empty (not null).
An example of how a primary key might be used to link tables is given here; Imagine that the same soccer team of the PLAYERS table previous also want to keep a record of who scored in each game. A new table could be created called GOALS that contains the date of the game, who scored and how many times they scored. Rather than write out each player’s name, address and DOB for each goal we just link their goal record to the personal details in the players table.
So if Anthony was to score one goal on 2/1/2018 we could record:
0034 (PLAYER_ID), 2/1/2018 (Game_Date), 1(Number_of_Goals) in a GOALS table.
Example GOALS table (including the information on Anthony’s goal):
| id | player_id | game_date | number_of_goals |
|---|---|---|---|
| 0001 | 0034 | 2/1/2018 | 1 |
| 0002 | 0034 | 9/1/2018 | 2 |
| 0003 | 0035 | 2/1/2018 | 1 |
Linking tables in this way reduces repeated[2] data and therefore reduces the chance of errors.
Queries
Of course having information in a database is useless if we never retrieve it. This is where queries come in. A query is a way of asking a question of the database. There is a standard way of asking databases for information called SQL. SQL, or Structured Query Language, is a programming language that uses simple keywords to manipulate our database. For example if we wanted only the players name and their date of birth then we could write:
SELECT name, dob FROM people;
| name | dob |
|---|---|
| ANTHONY | 23/5/1998 |
| SARAH | 24/2/1999 |
| PATRICK | 1/11/1994 |
If we would like to query our goals table and find everyone who scored two goals we could write:
SELECT player_id, game_date FROM goals WHERE number_of_goals = 1;
This would result in the following data being returned:
| player_id | game_date |
|---|---|
| 0034 | 2/1/2018 |
| 0035 | 2/1/2018 |
Notice how we can choose to narrow down the fields returned to only two by only asking (SELECT) for player_id and game_date and we can also filter the records by using the WHERE keyword and supplying a condition.
Other keywords include INSERT for adding records and UPDATE for changing a current record. It is worth checking out the Khan Academy course on SQL for a primer on SQL before continuing.
Pre-requisites
For this project to work you will need to have a working copy of MAMP or XAMP (or access to some other MySQL database server). Make sure this is working now before continuing.
Setting up our database
We are going to create a simple web page that when the user clicks submit will update two database tables that are linked. For this there needs to be a database and two tables created with the following characteristics:
- While it is beyond the scope of this tutorial to describe how to create a MySQL database the basic steps would be: download MAMP, open the start page, load PHPMyAdmin and start clicking around 🙂Database name: test_database
- At least two tables called: expenses and users (shown below)
- The users table should have 4 fields (id, firstname, surname, dob).
- The expenses table should have 5 fields (id, userid, amount, pickup, day)
Building the HTML web page
Once our database has been made, create a new PHP project in an IDE like Netbeans and edit the default index.php file. In this file we will design a GUI similar to the screenshot on the right. The HTML form elements are:
- Text boxes for First name and Surname
- Date Picker for Date of Birth.
- A drop-down menu (the HTML select tag) for purchase day.
- Text box for numbers, Amount.
- A checkbox for Customer Pickup.
- and a submit button for our form to send the data to a PHP called “add_record.php”.
The HTML code of index.php should look like:
<html>
<head>
<title>TODO supply a title</title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
</head>
<body>
<div style="background-color: lightgray; width:300px;padding:20px;padding-top:0px;">
<h1>Enter Details:</h1>
<form action="add_record.php" method="post">
First name: <input type="text" name="fname">
Last name: <input type="text" name="sname">
Date of Birth: <input type="date" name="dob">
Purchase Day:
<select name="day">
<option value="Monday">Monday</option>
<option value="Tuesday">Tuesday</option>
<option value="Wednesday">Wednesday</option>
<option value="Thursday">Thursday</option>
<option value="Friday">Friday</option>
</select>
Amount: <input type="number" name="amount">
<input type="checkbox" name="pickup" value="true">Customer Pickup
<input type="submit" value="Submit">
</form>
</div>
</body>
</html>
Building the PHP database code
If you test your HTML code now, nothing will happen when the user clicks submit because we haven’t creating our “add_record.php” file. The add_record.php file is responsible for getting our HTML form values and saving them into our database. Remember to make sure the file is located in the same folder as index.php or if it isn’t you will need to add the directory path to our form’s action=”add_record.php” parameter. The following steps outline what to put in our add_record.php file.
Step 1: Getting the posted data in PHP
In PHP we can retrieve data posted from a HTML form using the superglobal variable $_POST and the name parameter of the form element. Add the following code to our add_record.php file to get all of our form data:
<?php $firstname = $_POST["fname"]; $surname = $_POST["sname"]; $dob = $_POST["dob"]; $day = $_POST["day"]; $amount = $_POST["amount"]; $pickup = $_POST["pickup"];
If you want to check that this is working then add some debugging output statements after this like:
echo $firstname; echo $surname; echo $dob;
If you added the echo commands then you can run the project and enter some dummy data. When the user clicks submit the form data will be outputted to the screen.
Importance of the correct database field format
One important aspect of databases is that they expect data to be submitted to them in the correct format. For example in our example if you added the echo statement for $dob you would see that when the submit button is clicked that output for the date is 2018-01-15. This is not the format that MySQL expects to receive it’s date format, so we will need to convert our input.
Fortunately PHP has a date function that can convert data into different formats. In this case MySQL needs the data in the format Year-month-day hour:minute:second. The following command date(“Y-m-d H:i:s”, “2018-01-15”) would take a date of 2018-01-15 and return 2018-01-15 00:00:00.
Add this line to the end of our PHP code to ensure the date is in a MySQL friendly format:
$sqlDOB = date("Y-m-d H:i:s", strtotime($dob));
There are two methods for sending data from a HTML form. Which one to use depends on what you want to do as each has its advantages and disadvantages. You can see from the HTML code previous that the method is currently set in the FORM tag <form action=”add_record.php” method=”post”
GET Method
You may have seen URL links ending with www.somewebsite.com?age=15. This is because the GET method has been used for submitting data. Data sent in this way is added to the URL address. In this example the information after the “?” is being sent to somewebsite.com, this can be useful because a user can bookmark the link and come back to the same information at a later date.
One disadvantage of using GET is that there are size limits on how much data you can send.
POST Method
The POST method hides the data sent so it cannot be seen in the URL like GET. It does not have any size limit for what it can send but cannot be bookmarked.
Overview
A general rule is to use GET if you are retrieveing data and you are happy for users to be able to bookmark the page but use POST when you want to save data.
For example Moodle uses GET for the courses search form but uses POST for the user login form.
Step 2: Set up our database connection
Make sure you know the settings for your database connection, the following are the default settings for a MySQL database using MAMP. Add these lines to the end of our PHP file:
$servername = "localhost"; $username = "root"; $password = "root";
We will use these settings to open a connection to our database using the PDO database access method. One advantage PDO has over some other PHP database access methods, like MySQLi, is that it is database independent, so code can be easily modified for other databases such as PostgreSQL, Microsoft SQL Server or IBM DB2.
Add the following connection code to the PHP file:
try {
$conn=new PDO("mysql:host=$servername;dbname=test_database",$username,$password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//SQL code to execute will be added here
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
Just to emphasise, don’t send error messages to the screen on a production site! It’s ugly and it could be a security risk if sensitive data is outputted (think usernames and passwords!) 🙁
Notice that this code will TRY to execute our database code and if there is an error it will output a message to the screen. Note also that we set some attributes that will make sure that detailed debugging information is outputted, this should not used in production code for security reasons.
See http://php.net/manual/en/pdo.error-handling.php for more information.
Try running your code now, if there is a problem with your database connection you should be presented with an error message in your browser like:
Connection failed: SQLSTATE[HY000] [2002] No such file or directory
If there is no error then we can proceed and add our SQL code.
In cases where there is a possibility that the activity could fail it is typical to use a try { } catch ( ) { } block for handling errors. See the PHP web site, w3schools or the code above for the common syntax.
Step 3: Create and execute an SQL insert statement
The SQL statement for inserting data into a table follows the following syntax: INSERT into tablename (field1, field2, field3) VALUES (value1, value2, value3);
For our database a sample insert query might look like:
INSERT into users (firstname, surname, dob) VALUES (Gavin, Neale, “2018-01-15 00:00:00”);
The easy (and unsafe) way to dynamically create this SQL statement would be to insert our $firstname, $surname and $dob variables straight into a SQL string. But this is dangerous! Doing this might allow someone to insert some really nasty SQL code into our SQL string and then execute it without us knowing. This is called an SQL Injection Attack.
Fortunately there is a better way to insert posted data into a SQL string using PDO’s built in bindParam() function. This ensures that any malicious SQL code is stripped out of the variables before they are inserted into the string. Add the following code to our Try/Catch block, it will create our SQL statement and execute it safely:
try {
$conn = new PDO("mysql:host=$servername;dbname=test_database", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare("INSERT INTO users (firstname, surname, dob) VALUES (:firstname, :surname, :dob)");
$stmt->bindParam(':firstname', $firstname);
$stmt->bindParam(':surname', $surname);
$stmt->bindParam(':dob', $sqlDOB);
$stmt->execute();
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
Run your PHP project. After you click submit, jump into your database and check that the firstname, surname and dob fields have been populated with the form data.
INSERT
$stmt = $conn->prepare("INSERT INTO someTable (name, age) VALUES (:name, :age)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':age', $age);
$stmt->execute();
$stmt->close();
UPDATE
$stmt = $conn->prepare("UPDATE someTable SET name = :name, age=:age WHERE id=:id");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':age', $age);
$stmt->bindParam(':id', $userid);
$stmt->execute();
$stmt->close();
DELETE
$stmt = $conn->prepare("DELETE FROM someTable WHERE id=:id");
$stmt->bindParam(':id', $id);
$stmt->execute();
$stmt->close();
SELECT
You can see that each of these have the same basic syntax. When it comes to the SELECT statement where you need to retrieve many rows of data you need to fetch the rows (either all at once or one at a time) and then loop through the results.
$stmt = $conn->prepare("SELECT name FROM someTable WHERE age=:age");
$stmt->bindParam(':age', $age);
$stmt->execute();
$result = $stmt->fetchAll();
foreach ($result as $row) {
echo $row['name'];
}
$stmt->close();
Step 4: Update a second table using the ID value of the previous INSERT SQL
It can sometimes be important when inserting data into a table to know the ID value of the previous inserted record. PDO keeps track of the previously inserted ID using the command: $conn->lastInsertId();
We can store this ID value in a variable and insert it into the next SQL statement to create a relationship link between database records.
Add this code to our Try/Catch block, it will take the previously entered ID and insert it into our database record along with our other HTML form variables:
try {
$conn = new PDO("mysql:host=$servername;dbname=test_database", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare("INSERT INTO users (firstname, surname, dob) VALUES (:firstname, :surname, :dob)");
$stmt->bindParam(':firstname', $firstname);
$stmt->bindParam(':surname', $surname);
$stmt->bindParam(':dob', $sqlDOB);
$stmt->execute();
$userID = $conn->lastInsertId();
$stmt = $conn->prepare("INSERT INTO expenses (userid, amount, day, pickup) VALUES (:userid, :amount,:day, :pickup)");
$stmt->bindParam(':userid', $userID);
$stmt->bindParam(':amount', $amount);
$stmt->bindParam(':day', $day);
$stmt->bindParam(':pickup', $pickup);
$stmt->execute();
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
When you run your code the result should be two records created; one in the users table and another in the expenses table. The first record holds the user details and the the second holds the expenses record with a matching ID linking the two records. Confirm that now, making sure that the ID from the last record in users table has the same value as the USERID value in the last expenses record.
[1] Relational Databases are databases made up of multiple linked tables. In contrast, a flat-file database is a simple single table database where all information is stored in one table with many fields. Flat-file databases can be useful for simple database solutions like an address book but become very inefficient as more complex data needs to be stored.
[2] Repeated data that can be found in another table is also called data redundancy.