Create a Dynamic PHP Web Application using MySQL Database

Introduction

In this guide, you will learn how to create a web application using PHP. The application is actually a basic content management system (CMS) that allow users to manage and display dynamic content, store data in a MySQL database, and handle file uploads and downloads.

It will implement various features, including:

Database Interaction: Connecting to a MySQL database to store and retrieve content dynamically

File Management: Zipping and unzipping files for easy uploads and downloads

API Integration: Making HTTP requests to external APIs using cURL to fetch data

Localization Support: Formatting data according to the user’s locale using the Intl extension

Character Encoding: Ensuring that the application handles multibyte strings, which is crucial for supporting multiple languages

By the end of this guide, we will have a fully functional PHP web application.

Prerequisites

Before starting, ensure you have the following installed:

A web server (like Apache or Nginx)

PHP (with required extensions)

MySQL database server

A composer installed

Step 1: Create Project Directory

Create a new directory for our PHP web application. We’re going to make it at ~


cd ~
mkdir php-web-app
cd php-web-app

Step 2: Set Up Compose

So we our application needs several dependencies and we want to manage that easily. So we will set up Compose as a package manager for PHP application.

Initialize Composer


composer init

Follows the prompts to set up our composer.json .

Require necessary PHP extensions, add the following lines to our composer.json file:


{
    "name": "kevinrm/php-web-app",
    "description": "A basic PHP web application with MySQL, Nginx, and essential extensions for dynamic content management.",
    "type": "project",
    "license": "MIT",
    "authors": [
        {
            "name": "Kevin RM",
            "email": "kevinrizkimohammad@gmail.com"
        }
    ],
    "minimum-stability": "stable",
    "require": {
        "php": "^7.2",
        "ext-mysql": "*",
        "ext-json": "*",
        "ext-mbstring": "*",
        "ext-curl": "*",
        "ext-intl": "*",
        "ext-zip": "*"
    }
}

Update Missing PHP Extensions


# Update package
sudo apt update

# Install missing dependencies
sudo apt install php-mysql php-mbstring php-curl php-intl php-zip php8.1-mysqli php8.1-pdo php8.1-mysql -y

# Open php.ini
sudo vim /etc/php/8.1/cli/php.ini
sudo vim /etc/php/8.1/fpm/php.ini

# Uncomment this lines
extension=mysqli
extension=pdo_mysql

# Restart PHP-FPM and Nginx
sudo systemctl restart php8.1-fpm
sudo systemctl restart nginx

# Verify Installation
php -m | grep -E 'mysql|mbstring|curl|intl|zip'

# Run composer again
composer install

Install dependencies


composer install --ignore-platform-req=ext-mysql

Step 3: Setup Database

Create Database for Web


sudo mysql -u root -p

Create a new database


CREATE DATABASE webapp_db;

Create a MySQL User for the Application


CREATE USER 'webapp_user'@'localhost' IDENTIFIED BY '<your strong password>';

Grant Permissions to the User


GRANT ALL PRIVILEGES ON webapp_db.* TO 'webapp_user'@'localhost';

Flush the privileges to apply changes


FLUSH PRIVILEGES;

Create a configuration file to store database credentials

File: config.php


<?php
$db_host = 'localhost';
$db_name = 'webapp_db';
$db_user = 'webapp_user';
$db_pass = ';3H9q4HuGAel';

try {
    $pdo = new PDO("mysql:host=$db_host;dbname=$db_name", $db_user, $db_pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Database connection successful!";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Test connection

Create a file named db_test.php on our web server’s root directory (/var/www/html/db_test.php)


<?php
// Database credentials
$db_host = 'localhost';
$db_name = 'webapp_db';
$db_user = 'webapp_user';
$db_pass = ';3H9q4HuGAel';

try {
    // Create a new PDO instance
    $pdo = new PDO("mysql:host=$db_host;dbname=$db_name", $db_user, $db_pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // If connection is successful
    echo "Database connection successful!";
} catch (PDOException $e) {
    // If there is an error in the connection
    echo "Database connection failed: " . $e->getMessage();
}
?>

Run The Script

Open a browser and navigate to the script by entering your server’s IP address followed by /db_test.php

If there’s an error, the script will output the error message:

Step 4: Establish Database Connection

Create a file to handle the database connection

File: db.php


<?php

class FileHandler
{
    // Zip files into an archive
    public static function createZipArchive($files, $outputFile)
    {
        $zip = new ZipArchive();
        if ($zip->open($outputFile, ZipArchive::CREATE) !== true) {
            return false;
        }

        foreach ($files as $file) {
            $zip->addFile($file, basename($file));
        }

        return $zip->close();
    }

    // Unzip an archive to a specified directory
    public static function extractZipArchive($zipFile, $extractTo)
    {
        $zip = new ZipArchive();
        if ($zip->open($zipFile) === true) {
            $zip->extractTo($extractTo);
            $zip->close();
            return true;
        }
        return false;
    }
}
?>

Step 5: Create File Handle Class

Create a class for handling file operations, including zipping and unzipping files.

File: classes/FileHandler.php


<?php
require 'db.php';
require 'classes/FileHandler.php';

// Locale-based message using Intl
$locale = 'en_US';
$fmt = new NumberFormatter($locale, NumberFormatter::CURRENCY);
echo "Formatted currency: " . $fmt->formatCurrency(1234567.89, "USD") . "<br>";

// JSON handling example
$jsonData = json_encode(['status' => 'success', 'message' => 'Hello, World!']);
echo "JSON Data: " . $jsonData . "<br>";

// Multibyte string handling
$multibyteStr = "こんにちは世界"; // "Hello World" in Japanese
echo "String Length (mbstring): " . mb_strlen($multibyteStr) . "<br>";

// cURL example for fetching an API endpoint
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, "https://api.github.com/");
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_USERAGENT, "Sample PHP App");
$response = curl_exec($ch);
curl_close($ch);
echo "cURL Response: " . htmlspecialchars($response) . "<br>";

// Zip file handling
$files = ['path/to/file1.txt', 'path/to/file2.txt'];
$outputFile = 'files.zip';
if (FileHandler::createZipArchive($files, $outputFile)) {
    echo "Files zipped successfully!<br>";
}

// Unzip a file
$extractPath = 'path/to/extracted/';
if (FileHandler::extractZipArchive($outputFile, $extractPath)) {
    echo "Files unzipped successfully!<br>";
}

// Git version check (run this command if `git` is available)
$gitVersion = shell_exec('git --version');
echo "Git Version: " . htmlspecialchars($gitVersion) . "<br>";
?>

Step 6: Create Main Application File

Create the main file that will serve as the entry point for our application.

File: index.php


<?php
require 'db.php';
require 'classes/FileHandler.php';

// Locale-based message using Intl
$locale = 'en_US';
$fmt = new NumberFormatter($locale, NumberFormatter::CURRENCY);
echo "Formatted currency: " . $fmt->formatCurrency(1234567.89, "USD") . "<br>";

// JSON handling example
$jsonData = json_encode(['status' => 'success', 'message' => 'Hello, World!']);
echo "JSON Data: " . $jsonData . "<br>";

// Multibyte string handling
$multibyteStr = "こんにちは世界"; // "Hello World" in Japanese
echo "String Length (mbstring): " . mb_strlen($multibyteStr) . "<br>";

// cURL example for fetching an API endpoint
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, "https://api.github.com/");
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_USERAGENT, "Sample PHP App");
$response = curl_exec($ch);
curl_close($ch);
echo "cURL Response: " . htmlspecialchars($response) . "<br>";

// Zip file handling
$files = ['path/to/file1.txt', 'path/to/file2.txt'];
$outputFile = 'files.zip';
if (FileHandler::createZipArchive($files, $outputFile)) {
    echo "Files zipped successfully!<br>";
}

// Unzip a file
$extractPath = 'path/to/extracted/';
if (FileHandler::extractZipArchive($outputFile, $extractPath)) {
    echo "Files unzipped successfully!<br>";
}

// Git version check (run this command if `git` is available)
$gitVersion = shell_exec('git --version');
echo "Git Version: " . htmlspecialchars($gitVersion) . "<br>";
?>