Introduction

During a recent technical assessment, I was tasked to develop a simple CRUD events management system in PHP. I planned on using Laravel as I have always done, after all, it is really easy. However, I run into a few problems and was unable to proceed beyond creating a new Laravel project. My only other option was to do the app in vanilla PHP, however, given that I had been using Laravel for a long time, I had totally forgotten how to accomplish this simple task. For anyone else out there like me, here is a simple step by step way of developing a simple CRUD application without any frameworks.

What do I hope to achieve?

Follow the principle of separation of concerns as much as possible
Create a script to handle the database connection, another script for running all database migrations.
Have a class to handle all database transactions. For this case, we will limit this to inserting, viewing, updating and deleting items from the database.
Create a dynamic web app that will allow users to perform the CRUD operations

Prerequisites
- Basic knowledge of PHP, HTML, and CSS
- A local PHP and MySQL develop environment eg MAMP, vagrant.
I will begin by taking you through what the file structure will look like. If you would like to look at the full project you can find it here

File structure

I will first show you what the file structure looks like:

Events
1. app
  - Models
  - event.php
  - Templates
  - app.php
  - create.php
  - delete.php
  - edit.php
  - update.php
  - index.php
  - View.php
2. database
  - migrations.php
3. migrations (Folder containing the migration files)
4. scripts
  - create_migration.php
  - migrate.php
5. config.php
6. Migrations_list.php

Connect to database

First, we need to create a connection to the database so we can be able to save the data. I will be using a MySQL database which I will call events.

We will do all the configurations in the config.php file.

PHP has an inbuilt class, PDO, for handling database connections.

<?php
class PDOConfig extends PDO {
 
   private $engine;
   private $host;
   private $database;
   private $user;
   private $pass;
   private $port;
   private $db;

 
   public function __construct(){
       $this->engine = 'mysql';
       $this->host = '127.0.0.1';
       $this->database = 'events';
       $this->user = 'root';
       $this->pass = 'root';
       $this->port = '8889';
       $dsn = $this->engine.':dbname='.$this->database.';host='.$this->host.';port='.$this->port;
       parent::__construct( $dsn, $this->user, $this->pass );  
   }
}
?>

It is, however, not advisable to pass in the database parameters as I did above, they should always be passed in as environment variables.

Database transactions

We will then define a class that will handle all the database transactions which means we only need to call the config.php in this one file, instead of every time

we need to interact with the database. I have defined INSERT, UPDATE, SELECT, and DELETE statements here. Notice that I make use of prepared statements to provide a level of security against SQL injection.

<?php
   require_once('../../config.php');

   class DatabaseTranscations extends PDOStatement {
       private $connection;

       public function __construct()
       {
       }

       private function connection() {
           $connection = new PDOConfig();
           if($connection === false){
               echo "ERROR: Could not connect. " . mysqli_connect_error();
           }
           return $connection;
       }

       public function insert($event_name, $description) {
           $sql = "INSERT INTO events(event_name, description) VALUES (?, ?)";
       try {
           $connection = $this->connection();
           $statement = $connection->prepare($sql);

           $statement->bindParam(1, $event_name, PDO::PARAM_STR);
           $statement->bindParam(2, $description, PDO::PARAM_STR);

           $statement->execute();
           $connection = null;
           return true;
       } catch (PDOException $e) {
           echo $e->getMessage();
           return false;
       }
       }

       public function select($id = null) {
           if (isset($id)) {
               $sql = "SELECT * FROM events WHERE id = :id";
           try {
               $connection = $this->connection();
               $statement = $connection->prepare($sql);
               $statement->bindValue(':id', $id);
               $statement->execute();
               $result = $statement->fetch(PDO::FETCH_ASSOC);
               $connection = null;
               return $result;
           } catch (PDOException $e) {
               echo $e->getMessage();
               return false;
           }
           } else {
               $sql =  "SELECT * FROM events";
               try {
                   $connection = $this->connection();
                   $statement = $connection->query($sql);
                   $result = $statement->fetchAll();
                   $connection = null;
                   return $result;
               } catch (PDOException $e) {
                   echo $e->getMessage();
                   return false;
               }
           }
       }

       public function update($event_name, $description, $id) {
           $sql = "UPDATE events set event_name = ?, description = ? WHERE id = ?";
           try {
               $connection = $this->connection();
               $statement = $connection->prepare($sql);
               $statement->bindParam(1, $event_name, PDO::PARAM_STR);
               $statement->bindParam(2, $description, PDO::PARAM_STR);
               $statement->bindParam(3, $id, PDO::PARAM_INT);
               $statement->execute();
               $connection = null;
               return true;
           } catch (PDOException $e) {
               echo $e->getMessage();
               return false;
       }
       }

       public function delete($id) {
           $sql = "DELETE FROM events WHERE id = ?";
           try {
               $connection = $this->connection();
               $statement = $connection->prepare($sql);
               $statement->bindParam(1, $id, PDO::PARAM_INT);
               $statement->execute();
               $connection = null;
               return true;
           } catch (PDOException $e) {
               echo $e->getMessage();
               return false;
           }
       }
   }

Create tables

I created a script, create_migration.php to generate the migration files. When this script is run with the table name, it generates the migration file for creating that table.

<?php

$opt = 't:';
$options = getopt($opt);
if (!$options) {
   echo "Migrations not created please provide a table name";
} else {
   $table_name = $options ? $options['t'] : "";

   $time = time();
   $date = date('Y_m_d');
   $time_stamp = $date . "_" . $time;
   $file_name = $time_stamp . "_create_" . $table_name . "_table.php";
   $table = ucwords($table_name);
   $migrate_table = "Create".$table."Table";
  
   $data =
       "
           <?php
           require_once(__DIR__.'/../config.php');\n
           class " . $migrate_table . " {\n
               private function connection() {\n
                   \$connection = new PDOConfig();\n
                   if (\$connection === false) {\n
                       echo 'ERROR: Could not connect. mysqli_connect_error()';\n
                   }\n
                   return \$connection;\n
               }
  
               public function createTable() {\n
                   \$table_name = ". "  '$table_name'  " .";
                   \$sql = 'CREATE TABLE `$table_name` (\n
                   id INT AUTO_INCREMENT PRIMARY KEY,\n
                   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)';\n
                       try {\n
                           \$connection = \$this->connection();\n
                           \$statement = \$connection->prepare(\$sql);\n
                           \$statement->execute();\n
                           \$connection = null;\n
                           return true;
                       } catch (PDOException \$e) {\n
                           echo \$e->getMessage();\n
                           return false;
                       } 
                   }
  
               public function dropTable() {\n
                   \$table_name = ". "  '$table_name'  " .";
                   \$sql = 'DROP TABLE IF EXISTS `$table_name`';\n
                   try {\n
                       \$connection = \$this->connection();\n
                       \$statement = \$connection->prepare(\$sql);\n
                       \$statement->execute();\n
                       \$connection = null;\n
                       return true;
                   } catch (PDOException \$e) {\n
                       echo \$e->getMessage();\n
                       return false;
                   } 
               }
               }
           ?>
       "
   ;
   file_put_contents(__DIR__."/../migrations"."/".$file_name, $data);
}
?>

Migrations

To run my migrations, I run the script below, migrate.php. If I want to run all migrations, I run it without any flags otherwise, I run it with the file name of the migration to run.

<?php
require __DIR__."/../database/migrations.php";
require __DIR__."../../migrations/2020_01_28_1580251805_create_venues_table.php";
require __DIR__."../../migrations/2020_01_29_1580256901_create_books_table.php";
require __DIR__."../../migrations/2020_01_28_1580250049_create_users_table.php";
require __DIR__."../../migrations/2020_01_29_1580262222_create_houses_table.php";
require __DIR__."../../migrations/2020_01_30_1580402897_create_teachers_table.php";
require __DIR__."../../migrations/2020_01_30_1580402811_create_subject_table.php";
require __DIR__."../../migrations/2020_01_30_1580418760_create_marks_table.php";

$opt = 'm:';
$options = getopt($opt);
$file_name = $options ? $options['m'] : "";
$migrations = [];
$hashed_migration = new HashMigrations();

if (!$options) {
   $migrations_files = array_diff(scandir((__DIR__."/../migrations")), ['..', '.']);
   foreach ($migrations_files as $migration_file) {
       $migration_file_path = __DIR__."../../migrations"."/".$migration_file;

       $file1 = preg_replace('/[0-9]/','',$migration_file);
       $file2 = explode("_", $file1);
       $file3 = implode(" ", $file2);
       $file4 = ucwords($file3);
       $file5 = str_replace(" ", "", $file4);
       $file6 = str_replace("php", "", $file5);
       $file7 = str_replace(".", "", $file6);
       $class_name = $file7;
       $class = new $class_name;
      
       if($hashed_migration->migrationExists($migration_file) && $hashed_migration->compareFileContents($migration_file)){
           continue;
       }
       elseif ($hashed_migration->migrationExists($migration_file) && !$hashed_migration->compareFileContents($migration_file)) {
           $created = $class->createTable();
           $hashed_migration->UpdateFileContentsHash($migration_file);
           echo "Migration ".$migration_file." has been run.";
           continue;
       }
       else{
           $created = $class->createTable();
           if ($created) {
               $hashed_migration->storeHash($migration_file_path);
               echo "Migration ".$migration_file." has been run.";
           } else {
               echo "Couldnot run migration". $migration_file ."";
           }
           continue;
       }
   }
} else {
   $migration_file = $file_name;
   $migration_file = __DIR__."../../migrations"."/".$migration_file;

   $file1 = preg_replace('/[0-9]/','',$migration_file);
   $file2 = explode("_", $file1);
   $file3 = implode(" ", $file2);
   $file4 = ucwords($file3);
   $file5 = str_replace(" ", "", $file4);
   $file6 = str_replace("php", "", $file5);
   $file7 = str_replace(".", "", $file6);
   $class_name = $file7;
   $class = new $class_name;

   if($hashed_migration->migrationExists($migration_file) && $hashed_migration->compareFileContents($migration_file)){
       return;
   }

   elseif ($hashed_migration->migrationExists($migration_file) && !$hashed_migration->compareFileContents($migration_file)) {
       $created = $class->createTable();
       $hashed_migration->UpdateFileContentsHash($migration_file);
       echo "Migration ".$migration_file." has been run.";
       return true;
   }

   else{
       $created = $class->createTable();
       if ($created) {
           $hashed_migration->storeHash($migration_file_path);
           echo "Migration ".$migration_file." has been run.";
           return true;
       } else {
           echo "Couldnot run migration". $migration_file ."";
       }
       return true;
   }
}

$hashed_migration->checkForUpdate();

A little a bit about this script, I have used the concept of the hash table to find which migrations have been run or have changed and which haven’t been run. Only the migrations that have either changed or not been run will run. To help with making this check and also running the migrations, I use the HashMigrations class in the hash_migrations.php file.

Within this class, I have methods which:

- Create the hash for both the migrations file name and contents of the migrations file
- Save the hashed migrations file contents in the associative array with the hashed migrations file name as the key. This array contains all the migrations that have been run.
- Update the hashed migrations file contents
- Check if the migrations file contents have changed so that the associative array can be updated according

Check if a migration has been run before.

<?php
require __DIR__."/migrations_list.php";
class HashMigrations {
   public $migrations_array;
   private $stored_migrations_array;

   private function getMigrationsArray() {
       $file_contents = file_get_contents(__DIR__."/../migrations_list.json");
       return json_decode($file_contents, true);
   }

   public function __construct() {
       $this->stored_migrations_array = $this->getMigrationsArray();
       $this->migrations_array = $this->getMigrationsArray();

   }

   private function updateMigrationsArray() {
       $migration_array = json_encode($this->migrations_array);
       $migrations = file_put_contents(__DIR__."/../migrations_list.json", $migration_array);
       if ($migrations) {
           return true;
       } else {
           return false;
       }
   }

   public function checkForUpdate() {
       if ($this->stored_migrations_array === $this->migrations_array) {
           return;
       } else {
           return $this->updateMigrationsArray();
       }
   }

   private function hashFileContents($file) {
       $hash = hash_file("sha1", $file);
       if (!$hash) {
           throw new \ErrorException("File does not exist");
       }
       return $hash;
   }

   private function hashFileName($name) {
       $hash = hash("sha1", $name);
       if (!$hash) {
           throw new \ErrorException("File does not exist");
       }
       return $hash;
   }

   public function storeHash($file) {
       $hash_file_name = $this->hashFileName($file);
       $hash_content = $this->hashFileContents($file);
       $this->migrations_array[$hash_file_name] = $hash_content;
   }

   public function migrationExists($hash) {
       if (isset($this->migrations_array[$hash])) {
           return true;
       } else {
           return false;
       }
   }

   public function compareFileContents ($file) {
       $hash = $this->hashFileName($file);

       if (!$this->migrationExists($hash)) {
           throw new \ErrorException("Migration does not exist");
       }
       $new_file_content_hash = $this->hashFileContents($file);
       $old_file_content_hash = $this->migrations_array[$hash];
       if ($new_file_content_hash == $old_file_content_hash) {
           return true;
       } else {
           return false;
       }
   }

   public function UpdateFileContentsHash($file) {
       $hashed_file_name = $this->hashFileName($file);
       $hash_content = $this->hashFileContents($file);
       $this->migrations_array[$hashed_file_name] = $hash_content;
       return true;
   }
}

When the migrations are run, we check if a particular migration has been run before, if yes we check if the contents of the migrations file have changed, if yes we run the migrations otherwise we skip that migration. If the migration has not been run before we run the migration, hash it and add it to the migrations_list.

Add the CRUD

Next, we will add the functionality for the CRUD. I have done this in the event.php.

<?php

require '../../database.php';

class Event {
   public $event_name;
   public $description;
   private $db;
   public function __construct() {
   }

   public function addEvent($event_name, $description) {
       $event_name = filter_var($event_name, FILTER_SANITIZE_STRING);
       $description = filter_var($description, FILTER_SANITIZE_STRING);

       $db = new DatabaseTranscations();
       $inserted = $db->insert($event_name, $description);
       if ($inserted) {
           return "Successfully inserted";
       } else {
           return "Something went wrong insertion didnot happen";
       }
   }

   public function viewEvents() {
       $db = new DatabaseTranscations();
       $result = $db->select();
       if ($result) {
           return $result;
       } else {
           return "No results returned";
       }
   }

   public function viewEvent($id) {
       $id = filter_var($id, FILTER_SANITIZE_NUMBER_INT);

       $db = new DatabaseTranscations();
       $result = $db->select($id);
       if ($result) {
           return $result;
       } else {
           return "No results returned";
       }
   }

   public function editEvent($id, $event_name, $description) {
       $event_name = filter_var($event_name, FILTER_SANITIZE_STRING);
       $description = filter_var($description, FILTER_SANITIZE_STRING);
       $id = filter_var($id, FILTER_SANITIZE_NUMBER_INT);

       $db = new DatabaseTranscations();
       $result = $db->update($event_name, $description, $id);
       if ($result) {
           return true;
       } else {
           return false;
       }
   }

   public function deleteEvent($id) {
       $id = filter_var($id, FILTER_SANITIZE_NUMBER_INT);
       $db = new DatabaseTranscations();
       $result = $db->delete($id);
       if ($result) {
           return "deleted";
       } else {
           return "Something happened event not deleted";
       }
   }
}
?>

Within this file, I have defined methods for creating, updating, viewing and deleting an event.

Templates

Landing page

This will be the index page. This page will display the events, and have a button for creating a new event.

<?php
require "../models/event.php";
?>
<body>
   <?php require "app.php"; ?>
   <div class="jumbotron">
       <?php
       $s = new Event();
       $result = $s->viewEvents();
       foreach ($result as $row) :
       ?>
           <div class="list-group">
               <a href="view.php?id=<?php echo $row['id']; ?>" class="list-group-item list-group-item-action">
                   <?php echo $row['event_name']; ?> <br />
               </a>
           </div>
       <?php endforeach; ?>
   </div>
</body>

Below is what the landing page would look like. I have made use of bootstrap in this project. Check out this link for more on bootstrap.

Each event item is clickable and when clicked, it should redirect to a page that shows the details of the event. To enable this, we pass the id of the event to the URL.

<a href="view.php?id=<?php echo $row['id']; ?>" class="list-group-item list-group-item-action">

View details of a single event

We will create a page that will display the details of a single event. When an event is clicked, it will redirect to this page.

if (isset($_GET['id'])) {
       $event = new Event();
       $result = $event->viewEvent($_GET['id']);
   }

The above line of code picks up the id which is passed in the URL and passes it along to the viewEvent method which then returns details of the selected event.

The page also has edit and delete buttons. The edit button when clicked also passes the id of the event to the URL.

<button class="card-link btn btn-primary" onclick="window.location.href = 'edit.php?id=<?php echo $result['id']; ?>'">Edit</button>

The delete button loads a popup a modal which asks the user to confirm if they would like to delete the selected event. The pop up looks like this

<?php require "../models/event.php";
require "app.php"; ?>

<body>
   <?php
   if (isset($_GET['id'])) {
       $event = new Event();
       $result = $event->viewEvent($_GET['id']);
   } else {
       echo "Something went wrong!";
       exit;
   } ?>
   <h1>Showing details for <?php echo $result['event_name']; ?> </h1>
   <div class="jumbotron text-center">
       <p>
           <strong>Event:</strong> <?php echo $result['event_name']; ?><br>

           <strong>Description:</strong> <?php echo $result['description']; ?><br>
       </p>
       <button class="btn btn-primary" onclick="window.location.href = 'edit.php?id=<?php echo $result['id']; ?>'">Edit</button>
       <!-- Button trigger modal -->
       <button type="button" class="btn btn-primary" data-toggle="modal" data-target="#exampleModalCenter">
           Delete
       </button>
   </div>
   <!-- Modal -->
   <div class="modal fade" id="exampleModalCenter" tabindex="-1" role="dialog" aria-labelledby="exampleModalCenterTitle" aria-hidden="true">
       <div class="modal-dialog modal-dialog-centered" role="document">
           <div class="modal-content">
               <div class="modal-header">
                   <h5 class="modal-title" id="exampleModalLongTitle">Delete event</h5>
                   <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                       <span aria-hidden="true">×</span>
                   </button>
               </div>
               <div class="modal-body">
                   Are you sure you want to delete the event <?php echo $result['event_name']; ?>?
               </div>
               <div class="modal-footer">
                   <button type="button" class="btn btn-secondary" data-dismiss="modal">No</button>
                   <button onclick="window.location.href = 'delete.php?id=<?php echo $result['id']; ?>'" class="btn btn-primary">Yes</button>
               </div>
           </div>
       </div>
   </div>
  
</body>

Create and Edit an event

The landing page has a link that allows a user to add an event. When clicked, the user is redirected to a page that loads a form so a user can enter details of the event to be created.

The user fills in the details in the form above when done, they hit the submit button, which submits the data.

<?php
require "../models/event.php";
session_start();
if (!isset($_SESSION['count'])) {
   $_SESSION['count'] = 0;
} else {
   $_SESSION['count']++;
}
if (isset($_POST['submit'])) {
   $event_name = $_POST['eventName'];
   $description = $_POST['description'];
   $insert = new Event();
   $insert->addEvent($event_name, $description);
   $_SESSION["flash"] = ["type" => "success", "message" => "Event successfully created"];
   header("Location:" . "index.php");
}
?>

This section of the code runs after the form is submitted. The $_POST is a super PHP global variable that is used to collect form data.

<input name="eventName" type="text" class="form-control" id="eventName" placeholder="Enter event name">
               <input name="description" type="text" class="form-control" id="description" placeholder="Enter a description for your event">

As you can see the input elements have a name attribute, this attribute becomes the key of the associative $_POST array with the form input value becoming the value.

As you can see, once the entered values are picked from the array, they are passed to the addEvent method which then handles the data insert.

The edit works more or less the same as the create except that in the case of edit, the form is pre-populated.

Delete

When a user selects Yes from the modal that pops up when the delete button is clicked.

The id of the event is passed to the URL and delete.php is called where the id is picked from the URL and passed to the method deleteEvent which handles the delete. Upon delete, the user is redirected to the landing page where a success message is displayed.

This is achieved through the use of PHP’s super global variable $_SESSION. We add the message to be displayed to the session.

 $_SESSION["delete"] = ["type" => "danger", "message" => "Event successfully deleted"];

The above line of code is setting the message to display in the PHP $_SESSION global variable.

We then pick this message from the session variable and display on the landing page

if (isset($_SESSION["flash"])) {
       vprintf("<p class='flash %s btn btn-success'>%s</p>", $_SESSION["flash"]);
       unset($_SESSION["flash"]);
   }

   elseif (isset($_SESSION["delete"])) {
       vprintf("<p class='flash %s btn btn-danger'>%s</p>", $_SESSION["delete"]);
       unset($_SESSION["delete"]);
   }

The above code handles the display of the flash messages from the session variable.

Conclusion

And that marks the end of our very simple CRUD application in vanilla PHP without using any framework. I hope it has been helpful. If you have any comments or questions please do feel free to ask.

Insights

How to Build a CRUD App in Vanilla PHP

February 17, 2020

Introduction

What do I hope to achieve?

Prerequisites

File structure

Connect to database

Database transactions

Create tables

Migrations

Add the CRUD

Templates

Landing page

View details of a single event

Create and Edit an event

Delete

Conclusion

Phiona Basemera

More Insights

February 17, 2020

How to Build a CRUD App in Vanilla PHP

Phiona Basemera

Introduction

What do I hope to achieve?

Prerequisites

File structure

Connect to database

Database transactions

Create tables

Migrations

Add the CRUD

Templates

Landing page

View details of a single event

Create and Edit an event

Delete

Conclusion

Phiona Basemera

Thanks for subscribing!

More Insights

Partners

Developers

Hire Developers

Thank you for your interest