Connecting PHP application to MySQL database

Prerequisites
In order to understand and complete the presented laboratory, one is expected to have the following:


 * A basic understanding of database management systems (DBMS) and their general usage;
 * Basic understanding of the PHP language and running PHP scripts in the context of an HTTP server;
 * Basic understanding of SQL;
 * Having a instance of the MySQL/MariaDB server either by downloading the XAMPP environment or by using this Docker LAMP image;

If you have not acquired all the prerequisites, please refer to Downloading and installing XAMPP for an easy, multi-platform way of installing an HTTP and MySQL server.

Additionally, the w3schools website is a good resource for learning the basics of the PHP language and the basics of SQL.

Step 1. Ensuring the MySQL server is up and running
When installed and started, a MySQL/MariaDB server will by default listen for connections on the TCP port 3306. We present two different routes to get a MySQL server up and running: By using the XAMPP environment, or by using a Docker image that provides a HTTP and MySQL server for us.

If you are familiar with Docker, it is recommended you pick this way. If not, then just stick to the XAMPP part for this step.

XAMPP
After you have installed XAMPP, simply open the software package and press the "Start" buttons in front of the Apache HTTP and MySQL servers.

The logs should provide insight wether these services were started.

By pressing the "Explore" button, a file manager will be opened at the location where we are supposed to write the source code to. In XAMPP, this directory is named "htdocs".

The Docker way
In order to start a container using the provided image, one could issue a command such as the following: This command will:


 * Start a Ubuntu 18.04 Docker container based on the mattrayner/lamp:latest-1804 image.
 * The container will be mapping the container's 80 (HTTP) and 3306 (MySQL) ports to the hosts port, so that we have access from outside the container.
 * Create and mount the current working directory (where your PHP application shall reside) to the container /app directory as a volume. This is where Apache is configured to find the application to be served.

If there is a MySQL client installed on your system, you can also connect to the server by issuing the following command: Generally, the default username and password combination should either be:


 * root - root
 * root - blank password

Or in the case of the Docker variant, it will provide the username admin and a randomly generated password which can be found in the logs.

Step 2. Creating a database using MySQL
In order to create a database using the MySQL, we can:


 * Use the PHPMyAdmin web intefrace, that is available with both the instalation of the XAMPP environment and the Docker image. This can be done by accesing the  http://localhost/phpmyadmin  URL.
 * Use the Command Line Interface (CLI) with the mysql client, shown in the previous step.

PHPMyAdmin
Once logged in to PHPMyAdmin (using the username and password combination found in the previous section). On the left-hand side of the interface there will be a "New Database" button which we will have to press in order to create our database. This is shown in the figure next to this text.

Please create a database named "test" with the "utf8_general_ci" character set chosen from the dropdown menu.

MySQL CLI client
If you chose to use the MySQL CLI client, the equivalent of using the interface to create the new database would be equivalent to the SQL command: If you have taken either of the two steps, it now means we have a database called "test" that we can use in the current laboratory.

Step 3. Creating tables.
In order to store information within our newly created database, we must also create tables.

Choosing the tables to create in the database highly depends on what problem is to be modeled.

For the purpose of today's laboratory, we will model a blog page.

This means that our application will require:


 * A table for users.
 * A table for blog posts.

The users table
We can design this table to store the vital information required for users such as (username, email and a password).

Our users table should look similar to: Creating the users table can be performed with the PHPMyAdmin user interface (as shown in the figure to the right of the text) or by using the CREATE TABLE SQL command in the CLI, like so:

The posts table
And our posts table should look like: Similar to the users table, the SQL command we can use in order to create this table would be: There is also one more thing we should add to the "posts" table, which is a foreign key to the users.username in order to have a "post created by an author" relationship between the two tables.

This can be achieved by using the ALTER TABLE command to add a foreign key constraint that references a user id from the users table:

Step 3. Populating our recently created tables
Now that we have created the required tables, we should populate these tables with some entries, so that we have something to work with while developing our PHP application.

Generating passwords
As mentioned in the previous section, it is not at all recommended to store passwords in plain text.

Using hashing functions such as Message Digest 5/6 (MD5/6) or Secure Hash Function (SHA) is a good method of covering what the initial password chosen by a user is.

However, even if hashing functions work one way by taking a string and generating a unique hash for the given string, the MD5 hash of a common password such as "password" or "123456" will always be "5f4dcc3b5aa765d61d8327deb882cf99" or "e10adc3949ba59abbe56e057f20f883e"

This means that if somebody we're to get access to the "users" table, they could use a class of attacks called "rainbow tables" that store common passwords and their hash, in order to do a fast lookup and find what the initial string used for generating that hash.

It is recommended to use a predetermined string that will either be prepended or appended to the password (or any other way of deterministically manipulating the initial password string) before hashing and storing it to the database. This technique is called "salting the password".

As a simple example for today, we will be appending the string "blog2023" to each of our passwords before generating a MD5 hash of our password.

This example is using the UNIX program called "md5sum": We obtain a password hash: 60df859fa7bc59c66a49f3bafe24fbb3

Inserting users
We will rely on the SQL command line from now on (either by using the client, or the one available in the PHPMyAdmin web interface).

One can use the following command in order to add a new entry to the users table:

Inserting posts
Similar to the previous example, we can add new entries into the posts table:

Step 4. Connecting PHP to the database we created
When it comes to interacting with DBMS using PHP, there are three particular ways we can approach this problem:


 * Using the recommended method: PHP Data Objects (PDO).
 * Using the mysqli extension (both procedural and object oriented approaches).

There are three reasons why using PHP Data Objects is recommended:


 * PDO has drivers implemented for multiple DBMS using different paradigms, not just MySQL/MariaDB.
 * PDO implements prepared statements, which can be effectively used for mitigating SQL injections.
 * PDO has a performance advantage when using large prepared statements that have to be executed multiple times.

Recommended: the usage of PHP Data Objects (PDO)
The most recommended way of connecting PHP to a MySQL database would be through the use of PHP Data Objects (PDO).

Please inspect the following source code: In order to connect to our MySQL server we must instantiate the PDO class with the following parameters:


 * A connection string parameter that specifies what the driver to be used (in our case MySQL), together with the hostname and database name under the format "driver:host= ;dbname= Save this code snippet as conn.php inside the htdocs directory (or the app directory, if you are using the Docker container). We will include this script within others as needed.

You should now be able to access http://localhost/app/conn.php and see a "Connected sucessfully" message.

Step 5. Querying the database
Now that we are able to connect to our database using the "conn.php" script, we can start developing the functionality expected for application.

Since we are modeling a basic blog page, we should be able to:


 * Display all the blog posts from the database.


 * Login as an author.
 * Create new blog posts.

Recommended: PDO
The code snipped listed below is adding the functionality of retrieving all the blog posts found in the "posts" table. '''Save this code snippet as show_blog_posts.php inside the htdocs directory (or the app directory, if you are using the Docker container). We will include this script within others as needed.'''

Procedural alternative: mysqli_query, mysqli_fetch_assoc, mysqli_num_rows:
'''Save this code snippet as show_blog_posts.php inside the htdocs directory (or the app directory, if you are using the Docker container). We will include this script within others as needed.'''

Login functionality
In order to implement the functionality, we will first need to create a HTML form, providing the required fields: Save this code snippet as login.html inside the htdocs directory (or the app directory, if you are using the Docker container).

Pay attention to the action pointing to the PHP script for processing the login action, as well as the HTTP method, in our case POST.

Recommended: PDO
Save this code snippet as process_login.php inside the htdocs directory (or the app directory, if you are using the Docker container).

Procedural alternative: mysqli_query, mysqli_fetch_row
Save this code snippet as process_login.php inside the htdocs directory (or the app directory, if you are using the Docker container).

Blog post creation
Similar to the example before, we will also need to create a form for creating blog posts: Pay attention to the script path found in the action attribute, as well as the HTTP method in use.

Save this code snippet as write_post.php inside the htdocs directory (or the app directory, if you are using the Docker container).

Next, we will need to define the script handling the actual creation of the blog post.

Conclusions

 * The laboratory presented today shows how to use XAMPP or Docker containers in order to get a MySQL/MariaDB server running.
 * Additionally, we show how to create a database, and model a minimal working example of a blog website.
 * We see both procedural and object-oriented methods of connecting and querying MySQL databases using PHP and the difference between those two methods.
 * We have seen how to safely store passwords within a database.

Alexandru Munteanu

[mailto:alexandru.munteanu@e-uvt.ro alexandru.munteanu@e-uvt.ro]