PHP is a widely used server scripting language. It’s vast capabilities and lightweight nature makes it ideal for web frontend developing, and while it is possible to create advanced structures in it, it’s basic usage also easy to learn, which makes it a good starting point for beginners also. The data recorded, processed, sent or displayed by the application we build is need to be stored somewhere, however; our webpage is stateless without this data. We can store our data in many ways or even discard it after use, but the most standard way is storing it in a database designed for the sole purpose of storing data in a reliable way, and present it if needed, as fast as possible.
In this tutorial we will build a simple webpage to record and display user data. We will use a PostgreSQL RDBMS as backend, and develop our application in PHP that will run on an Apache Webserver. This way we can access our web application from any common browser to view or add to our user database. It is the nature of the web application that many users/operators can work with it simultaneously, all they need is a browser and network access to our application.
In this tutorial you will learn:
- How to install needed packages for PostgreSQL connection in PHP
- How to create the database side of the application
- How to code the example application
- How to operate and verify the example application
Software Requirements and Conventions Used
| Category | Requirements, Conventions or Software Version Used |
|---|---|
| System | Ubuntu 18.04.2 |
| Software | PostgreSQL 10.9, php 7.2.19 |
| Other | Privileged access to your Linux system as root or via the sudo command. |
| Conventions |
# – requires given linux commands to be executed with root privileges either directly as a root user or by use of sudo command$ – requires given linux commands to be executed as a regular non-privileged user |
Notes on software components
We set up our application on Ubuntu, but basically this process can be ported to any distribution. Differences are in package managers and package names, as well as in some directory path. Also note that while we’ll use the versions of the software components mentioned above, this application can be built on various versions of the components with small adjustments. Some examples of other distributions will be included for comparison.
You may notice that the application is minimalist: that is on purpose. The goal of this guide is to show the basic principles of developing an application that can communicate with a database, as simple as possible. Minimal code, minimal components. Do not use this guide to build production applications, as it lacks any security measurements, reuseability or optimization for that matter.
Installing needed packages
After installing PostgreSQL database, all we need is adding php support. For that, we need the php-pgsql package. We’ll use apt to install it:
# apt-get install php-pgsql
Some distros may call it php7-pgsql and the like, but all package managers provide search function, so it’s easy to find what we need. For example, this is how you install PostgreSQL on RHEL8 / centOS 8 Linux systems.
We also need to install php, a webserver, and support of php on the webserver. That’s two packages away:
# apt-get install apache2 libapache2-mod-php
We can start both services so they are available in the next steps. We’ll use systemd for that.
# systemctl start postgresql # systemclt start apache2
Creating the database and the needed objects
The database side of our application will consist of a database, a single table and a sequence to support automatic primary key assignment.
- We’ll switch to the
postgresuser:# su - postgres
- Log into the database:
$ psql psql (10.9 (Ubuntu 10.9-0ubuntu0.18.04.1)) Type "help" for help. postgres=#
- Create a new user:
postgres=# create role exampleuser with login password 'ExamplePass';
And a database with the owner set.
postgres=# create database exampledb owner exampleuser encoding 'UTF-8';
- After exiting the
psqltool, we create a text file calledexample_table.sqlwith the following content:create sequence userid_seq start with 0 increment by 1 no maxvalue minvalue 0 cache 1; alter sequence userid_seq owner to exampleuser; create table users ( userid numeric primary key default nextval('userid_seq'::regclass), username text not null, email text not null, registration_date timestamp default now() ); alter table users owner to exampleuser;Here we create a sequence at line 1 that will provide unique primary key to every record in the table, than set its ownership. At line 3 we create a table that will hold the user data we’ll record and display with the application. We set default values for the
useridandregistration_date, and will leave it to the database to fill those fields for us. - We load the above SQL file into the new database:
$ psql -d exampledb < example_table.sql CREATE SEQUENCE ALTER SEQUENCE CREATE TABLE ALTER TABLE
With this our database is set up and ready to serve. PostgreSQL 10.9 on Ubuntu accepts password-protected logins on the loopback device by default. If you are using other versions, you may need to modify the pg_hba.conf to enable access to the database. There is an example in our Redmine installation guide on how to set that up.
Building the application code
While there are certainly many great tools out there to help produce working source code, we’ll not cover those here, it would add to this guide’s complexity. We’ll create a textfile called recording.php, in the default DOCUMENT_ROOT directory on Ubuntu 18, /var/www/html/. On other distributions this could be /srv/www/htdocs/, or something else. Refer to the webserver’s configuration if you are not sure.
PHP is a scripting language, which means we don’t need to compile our code, just present it to the webserver, which will interpret it runtime.
We can use a command line text editor, such as vi or nano to fill the mentioned php file with the following source code:
<!-- section 1 -->
<!DOCTYPE html>
<html>
<head>
<title>User recording page</title>
</head>
<body>
<p>Please fill out the below form and press submit to record the data.</p>
<!-- section 2 -->
<form name="record" action="recording.php" method="post">
<input type="text" name="username"><br>
<input type="text" name="email"><br>
<input type="submit" value="Submit"><br>
</form>
<hr>
<!--section 3 -->
<?php
/* section 4 */
$dbconn = pg_connect("host=127.0.0.1 port=5432 dbname=exampledb user=exampleuser password=ExamplePass");
if(!$dbconn) {
die('Error connecting to database: ' . pg_last_error());
}
/* section 5 */
if(isset($_REQUEST["username"]) && isset($_REQUEST["email"])) {
$un = pg_escape_string($_REQUEST["username"]);
$mail = pg_escape_string($_REQUEST["email"]);
$insert_res = pg_query("insert into users (userid, username, email, registration_date) values (default,'$un','$mail',default)");
if(!$insert_res) {
echo ("Error while inserting data: " . pg_last_error());
} else {
echo ("Recoring successful");
}
}
/* section 6 */
$result = pg_query("SELECT * from users order by registration_date");
echo "<table border='1'>\n<tr><th>#</th><th>Username</th><th>E-mail address</th><th>Registration date</th></tr>\n";
while($row = pg_fetch_row($result)) {
echo "<tr><td>" . $row[0] . "</td><td>" . $row[1] . "</td><td>" . $row[2] . "</td><td>" . $row[3] . "</td></tr>\n";
}
echo "</table>\n";
/* section 7 */
pg_close($dbconn);
?>
<!-- section 8 -->
</body>
</html>We start our code as a simple static HTML page at section 1. We create a form at section 2 that provides the user the ability to record data into the application. We’ll only ask for a username and an e-mail address. At section 3 we embed PHP code into the page. Section 4 creates the database connection using the connection information we set up at the database step. If the connection can’t be built, the application dies with an error message.
At section 5 we handle any data that is sent for processing with the form we created at section 2. If both “username” and “email” is posted, we’ll check if they are not empty, and if they are not, we’ll insert them into the database using our database connection.
Regardless of receiving any posted data, we’ll query the database table for already recorded user data at section 6. We’ll generate the necessary HTML code to provide a table filled with the data that is returned from the database.
At setion 7 we close our database connection (always clean up objects that are not needed anymore!), and at section 8 we leave the PHP code, and finish our page with simple HTML tags. And with this, our application is complete, we can start using it.
Using the application
We’ll point a browser to http://localhost/recording.php. Our example application’s only page will show up.
We can fill the form’s fields with random data to test the application. When text added to both fields, we can submit the data.
After submit, the page shows the same form where we can record our next user, and also the one already recorded, that is queried from the database after insertion. We have completed our goal of a user data recording application.
We can also verify the data is stored successfully in the database using psql:
$ psql -d exampledb
exampledb=# select * from users;
userid | username | email | registration_date
--------+----------+------------------------+----------------------------
0 | foobar | foo.bar@maildomain.org | 2019-06-25 20:28:18.224375
(1 row)
Final thoughts
We created an example application using knowledge from different fields, database, webserver, the HTTP protocol in general, and of course PHP development. This guide is intend to show how easy it is to create something useful with the union of these fields. My hope is that this guide can help beginners to understand some of the aspects of development, and can demonstrate that it isn’t space science – we only added a few lines of code here and there, and the whole thing starts to work together.
While this example application does serve its demonstration purpose, the code, and the whole solution of course cries for improvement. Just to name a few…
- Nothing prevents an operator of recording the exact username and/or e-mail twice. That would result in redundant records. This could be handled both on database (unique constraint) and application (verify data before insertion) side.
- Where does the recorded data come from? What protects the application from a bad actor? At the moment nothing, a single shell script would be enough to overload it, only the webserver’s default defenses are in place – but that does not prevent me from posting the full Lord of the Rings trilogy in one of the fields 1000 times per minute (only bandwidth). In this state of the application it is easy to fill up the disks under the database with random data – user input verification comes to play here again, but also authentication and authorization.
- The web design is perfectly missing at this point. Some
css, some more text that helps the work of the operator, useful error messages… User friendliness is also an important viewpoint. - Naturally recording a user’s username and associated e-mail is just an example, we could record first and last name, phone number maybe, more than one e-mail per user… Anything else that is useful to store.
The list could go on and on. Its up to you, the reader, to improve your own application. If this guide helped you step on the path of developing, either as a hobby or by aiming to be a professional, then it served its purpose well.
