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.

The XAMPP control panel.

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:

docker run -p "80:80" -p "3306:3306" -v ${PWD}:/app mattrayner/lamp:latest-1804

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.

Using the Docker image

If there is a MySQL client installed on your system, you can also connect to the server by issuing the following command:

mysql -u <user> -p <password> -h 127.0.0.1 -P 3306

Generally, the default username and password combination should either be:

• root - root
• root - blank password

MySQL server login prompt.

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).

PHPMyAdmin new database creation

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:

CREATE DATABASE test CHARACTER SET utf8_general_ci;

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:

users

id	username	email	password
1	alex	alex@gmail.com	6f61d2058dad8ec9ef57b51f72c5b440
2	marian	marian@gmail.com	e95812effa5be780fbfb4b388add8024
3	admin	admin@blog.com	60df859fa7bc59c66a49f3bafe24fbb3

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:

PHPMyAdmin for creating a generic users table.

CREATE TABLE `test`.`users` ( `id` INT NOT NULL AUTO_INCREMENT , `username` VARCHAR(50) NOT NULL , `email` VARCHAR(255) NOT NULL , `password` VARCHAR(255) NOT NULL , PRIMARY KEY (`id`));

The posts table

And our posts table should look like:

posts

id	author	date	title	content
1	3	2023-06-21	Hello	Welcome!...
2	1	2023-06-25	First post	Hello everyone, this is my first post...
3	1	2023-06-26	Rambling	Some ramblings...

Similar to the users table, the SQL command we can use in order to create this table would be:

Creating a blog posts table using PHPMyAdmin.

CREATE TABLE `test`.`posts` ( `id` INT NOT NULL AUTO_INCREMENT , `author` INT NOT NULL , `date` DATE NOT NULL , `title` VARCHAR(255) NOT NULL , `content` VARCHAR(1000) NOT NULL , PRIMARY KEY (`id`));

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:

ALTER TABLE posts ADD CONSTRAINT author_fk FOREIGN KEY (author) REFERENCES users(id);

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":

echo -n "parolablog2023" | 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:

INSERT INTO `users` (`id`, `username`, `email`, `password`) VALUES (NULL, 'admin', 'admin@blog.com', '60df859fa7bc59c66a49f3bafe24fbb3');

Inserting posts

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

INSERT INTO `posts` (`id`, `author`, `date`, `content`) VALUES ('1', '1', '2023-06-26', 'Welcome!\nLorem ipsum dolor sit amet, consectetur adipiscing elit.')

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:

<?php
$hostname = "localhost";
$username = "root";
$password = "password";

try {
  $conn = new PDO("mysql:host=$hostname;dbname=test", $username, $password);
  echo "Connected successfully";
} catch(PDOException $e) {
  echo "Connection failed: " . $e->getMessage();
}
?>

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=<hostname>;dbname=<database_name>";
• The MySQL server username;
• The MySQL server password;

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.

Procedural alternative: mysqli_connect

<?php
$hostname = "localhost";
$username = "root";
$password = "password";

$conn = mysqli_connect($hostname, $username, $password);

if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>

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.

Displaying the blog posts

Recommended: PDO

The code snipped listed below is adding the functionality of retrieving all the blog posts found in the "posts" table.

<?php

require("conn.php");

function get_author($db_conn, $author_id){
    $stmt = $db_conn->prepare("SELECT username FROM users WHERE id=?");
    $stmt->execute([$author_id]);
    $user = $stmt->fetch();

    return $user['username'];
}


function get_all_posts($db_conn){
    $data = $db_conn->query("SELECT * FROM posts")->fetchAll();

    foreach ($data as $row) {
	    $author = get_author($db_conn, $row['author']);
	    echo "<h2>". $row['title'] . "</h2>";
	    echo "<p>Posted by " . $author . " at " . $row['date'] . "</p>";
	    echo $row['content']."<br />\n";
    }
}

get_all_posts($conn);

?>

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:

<?php

require("conn_mysqli.php");

function get_author($db_conn, $author_id){
    $query = "SELECT username FROM users WHERE id=" . $author_id;
    $result = mysqli_query($db_conn, $query);
    
    if (mysqli_num_rows($result) > 0) {
	    while($row = mysqli_fetch_assoc($result)) {
	        return $row['username'];
	    }
    } 
    else {
	    return NULL;
    }
}

function get_all_posts($db_conn){
    $query = "SELECT * FROM posts";
    $result = mysqli_query($db_conn, $query);
    
    if ($result->num_rows > 0){
	    while($row = mysqli_fetch_assoc($result)) {
     	    $author = get_author($db_conn, $row['author']);
     	    echo "<h2>". $row['title'] . "</h2>";
     	    echo "<p>Posted by " . $author . " at " . $row['date'] . "</p>";
     	    echo $row['content']."<br />\n";
 	    }
	}

    get_author($conn, '1');
}

get_all_posts($conn);

?>

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:

<!doctype html>
<html lang="en">
    <head>
        <meta charset="UTF-8"/>
        <title>Document</title>
    </head>
    <body>
	<form action="process_login.php" method="POST">
	    <table>
		<tr>
		    <td><label for="username">Username:</label></td>
		    <td><input name="username" type="text"/></td>		    
		</tr>
		<tr>
		    <td><label for="password">Password:</label></td>
		    <td><input name="password" type="password"/></td>
		</tr>
		<tr>
		    <td> <input type="submit" value="Submit"/></td>
		</tr>
	    </table>
	</form>
    </body>
</html>

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

<?php

require("conn.php");

function login($db_conn, $username, $password){
    $stmt = $db_conn->prepare("SELECT username, password FROM users WHERE username=?");
    $stmt->execute([$username]);
    $result = $stmt->fetch();

    // Check if the user inputted password + salt hashed will be the same as in the db
    // Ideally, the salt should be stored somewhere else (e.g. a config.php file)
    if(md5($password . "blog2023") == $result['password']){
	    echo "Login successful!";
	    // Continue with session management here
    }
    else {
	    echo "Invalid password!";
    }
}

login($conn, $_POST['username'], $_POST['password']);

?>

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

<?php

require("conn_mysqli.php");

function login($db_conn, $username, $password){
    $query = "SELECT username, password FROM users WHERE username='" . $username . "'";

    if ($result = mysqli_query($db_conn, $query)) {
	    $row = mysqli_fetch_row($result);
	    // Check if the user inputted password + salt hashed will be the same as in the db
	    // Ideally, the salt should be stored somewhere else (e.g. a config.php file)
	    if (md5($password . "blog2023") == $row[1]){
    	    echo "Login successful!";
	        // Continue with session management here
	    }
	    else {
	        echo "Invalid password!";
	    }
    }
}

login($conn, $_POST['username'], $_POST['password']);

?>

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:

<?php
// Check if user is authenticated here, else redirect to login
?>
<!doctype html>
<html lang="en">
    <head>
	<meta charset="UTF-8"/>
	<title>Document</title>
    </head>
    <body>
	<form action="create_post.php" method="POST">
	    <table>
		<tr>
		    <td><label for="post_title">Title:</label></td>
		    <td><input name="post_title" type="text"/></td>
		</tr>
		<tr>
		    <td><label for="content">Content:</label></td>
		    <td><textarea name="content"></textarea></td>
		</tr>
		<tr>
		    <td> <input type="submit" value="Submit"/></td>
		</tr>
	    </table>
	</form>
    </body>
</html>

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.

Recommended: PDO

<?php

require("conn.php");

function create_post($db_conn, $author_id, $title, $content){
    $stmt = $db_conn->prepare("INSERT INTO posts (author, date, title, content) VALUES(?,now(),?,?)");
    $stmt->execute([$author_id, $title, $content]);
}

// We assume that $author_id is stored in the $_SESSION.
create_post($conn, 1, $_POST['post_title'], $_POST['content']);

header('Location: index.php');

?>

Procedural alternative: mysqli_query

<?php

require("conn_mysqli.php");

function create_post($db_conn, $author_id, $title, $content) {
    $query = "INSERT INTO posts (author, date, title, content) VALUES (" . $author_id . ",now()" . " $title," . $content . ")";

    if (mysqli_query($conn, $query)) {
	    echo "Posted.";
    } else {
    	echo "Error creating post" . mysqli_error($conn);
    }
}

create_post($conn, 1, $_POST['post_title'], $_POST['content']);

header('Location: index.php');

?>

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

alexandru.munteanu@e-uvt.ro