Create a Simple CRUD Database App in HTML with MySQL Database and PHP

Some of you might be owing a website and would certainly be editing and updating it frequently. Through this article I will make know how you could develop a working website with a very little knowledge and could even host it using your Linux box.

A quick example of a CRUD application would be a database of employees for a company. From the control panel, an admin would be about to add a new employee (create), view a list of employees (read), change an employee’s salary (update) or remove a fired employee from the system (delete).crud-MySQL

Prerequisites

  • Linux Box ( We have used Amazon Linux 2 AMI here)
  • A basic knowledge of HTML
  • A local PHP and MySQL environment (having a prompt knowledge of any other SQL, you can use it but examples in the article will be using MySQL.)

Step 1 : Install Apache Webserver

Apache is a web server program. It comes installed and configured on most of the System.

# yum install -y  httpd
# systemctl start httpd

crud1

On the next step start Apache service using systemd init script and open RHEL/CentOS 7.0 Firewall rules using firewall-cmd, which is the default command to manage iptables through firewalld daemon.

# firewall-cmd# firewall-cmd --permanent --add-service=http
# systemctl restart firewalld

To verify Apache functionality open a remote browser and type your server IP Address using HTTP protocol on URL (http://server_IP), and a default page should appear like in the screenshot below.

apche1

For now, Apache document Root path it’s set to /var/www/html system path, which by default doesn’t provide any index file. If you want to see a directory list of your document Root path open Apache welcome configuration file and set Indexes statement from – to + on <LocationMach> directive, using the below screenshot as an example.

# vi /etc/httpd/conf.d/welcome.conf

crud2

Close the file, restart Apache service to reflect changes and reload your browser page to see the final result.

# systemctl restart httpd

https://portal.accenture.com/#/notifications

Step 2 : Install PHP5 Support for Apache

Depending on what type of applications you want to use, install the required PHP modules from the above list, but for a basic MariaDB support in PHP and PhpMyAdmin you need to install the following modules.

# yum -y install php php-mysql php-pdo php-gd php-mbstring

To get a full information list on PHP from your browser, create a info.php file on Apache Document Root using the following command from root account, restart httpd service and direct your browser to the http://server_IP/info.php address.

# echo "<?php phpinfo(); ?>" > /var/www/html/info.php
# systemctl restart httpd

crud5

If you get an error on PHP Date and Timezone, open php.ini configuration file, search and uncomment date.timezone statement, append your physical location and restart Apache daemon.

Step 3 : Building Front end Application

Let’s create a directory called public/ in the root of our project. This is where we are going to put all my client-facing code, or what would be pages accessible from the internet.

Our main/home page will be located at index.php, so create that file in your public/ directory.

public/index.php

# vi /var/www/html/public/index.php

 

<!doctype html>
<html lang="en">

<head>
         <meta charset="utf-8">
         <meta http-equiv="x-ua-compatible" content="ie=edge">
         <meta name="viewport" content="width=device-width, initial-scale=1">

         <title>dinfratechsource Simple Database Application </title>
         <link rel="stylesheet" href="css/style.css">
</head>
<body bgcolor="#ffffcc">
         <h1><center>dinfratechsource Simple Database Application</center></h1>
         <ul>
     
         <li><a href="create.php"><strong>Create</strong></a> - Add a user</li>
         <li><a href="read.php"><strong>Read</strong></a> - Find a user</li>
        </ul>
</body>
</html>

 

Now we have sample index webpage that will link to create and read pages.

crud6

Create a templates/ directory in public, and make a header.php and footer.php. You’ll take everything from the <h1> tag and up and put it in the header.

public/templates/header.php

<!doctype html>
<html lang="en">

<head>
         <meta charset="utf-8">
         <meta http-equiv="x-ua-compatible" content="ie=edge">
         <meta name="viewport" content="width=device-width, initial-scale=1">

         <title>dinfratechsource Simple Database Application </title>
         <link rel="stylesheet" href="css/style.css">
</head>
<body bgcolor="#ffffcc">
         <h1><center>dinfratechsource Simple Database Application</center></h1>

 

And here’s the footer.

public/templates/footer.php

</body>
</html>

All that remains in index.php at this point are the links to our two other pages.

We want to include the header and footer code in all our front end pages, so we’ll be using a PHP include function to pull that code in.

Edit public/index.php as below

<?php include "templates/header.php"; ?>

    <ul>
    <li><a href="create.php"><strong>Create</strong></a> - Add a user</li>
    <li><a href="read.php"><strong>Read</strong></a> - Find a user</li>
   </ul>

<?php include "templates/footer.php"; ?>

We now have the reusable layout code that we can use in our other pages.

Step 4 : Create  a new user page

Now we’re going to make a file called create.php back in our public/ directory. This will be the page we use to add a new user to the database. We’ll start the file with our header and footer loaded in.

I’m going to create a simple form here that gathers the first name, last name, email address, age, and location of a new user.

public/create.php

<?php include "templates/header.php"; ?><h2>Add a User</h2>
<form method="post">
	<label for="firstname">First Name</label>
	<input type="text" name="firstname" id="firstname">
	<label for="lastname">Last Name</label>
	<input type="text" name="lastname" id="lastname">
	<label for="email">Email Address</label>
	<input type="text" name="email" id="email">
	<label for="age">Age</label>
	<input type="text" name="age" id="age">
	<label for="location">Location</label>
	<input type="text" name="location" id="location">
	<input type="submit" name="submit" value="Submit">
</form>
<a href="index.php">Back to home</a>
<?php include "templates/footer.php"; ?>

You’ll notice that for each entry, we have a <label>, and each <input> has a name and id attribute.

Before we display the front end of the create.php code, let’s quickly create a css/ folder and make style.css.

# mkdir /var/www/html/public/css

public/css/style.css

label {
display: block;
margin: 5px 0;
}

crud8

Step 5 : Create a Read User Page

Now we will create our read.php file, which will query the list of users by a parameter (in this case, location) and print out the results.

public/read.php

<?php include "templates/header.php"; ?>

<h2>Find user based on Location</h2>

<form method="post">
  <label for="location">Location</label>
  <input type="text" id="location" name="location">
  <input type="submit" name="submit" value="View Results">
</form>

<a href="index.php">Back to home</a>

<?php include "templates/footer.php"; ?>

crus9

Here is the layout

public/
|-- css/
|   |-- style.css
|-- templates/
|   |-- header.php
|   |-- footer.php
|-- index.php
|-- create.php
|-- read.php

crud10.JPG

Step 6 : Install and Configure MySQL Database 

Please refer the below link for MySQL Installation

https://dinfratechsource.com/2018/11/10/how-to-install-latest-mysql-5-7-21-on-rhel-centos-7/

To test database functionality login to MySQL DB using its root account and exit using quit statement.

Create a database user with all privileges to the database ‘public’ which we are going to create.

CREATE USER 'formrw'@'localhost' IDENTIFIED BY 'XXXXXXXX';
GRANT ALL PRIVILEGES ON public.* TO 'formrw'@'localhost';
FLUSH PRIVILEGES;

Create a directory called data/ and create a file called init.sql. This will be our database initializing code.

/public/data/init.sql

CREATE DATABASE public;

use public;

CREATE TABLE users (
	id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
	firstname VARCHAR(30) NOT NULL,
	lastname VARCHAR(30) NOT NULL,
	email VARCHAR(50) NOT NULL,
	age INT(3),
	location VARCHAR(50),
	date TIMESTAMP
);

We’re going to use PDO (PHP Data Objects) to connect to the database.

The PDO object will ask for four parameters:

  • DSN (data source name), which includes type of database, host name, database name (optional)
  • Username to connect to host
  • Password to connect to host
  • Additional options

We’ll create a config.php file that contains all the variables.

var/www/html/config.php

<?php

/**
 * Configuration for database connection
 *
 */
$host       = "localhost";
$username   = "formrw";
$password   = "XXXXXXX";
$dbname     = "public"; 
$dsn        = "mysql:host=$host;dbname=$dbname"; 
$options    = array(
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
              );

Create a file called install.php in the root of your directory.

public/install.php

 <?php

/**
 * Open a connection via PDO to create a
 * new database and table with structure.
 *
 */

require "config.php";

try {
	$connection = new PDO("mysql:host=$host", $username, $password, $options);
	$sql = file_get_contents("data/init.sql");
	$connection->exec($sql);
	
	echo "Database and table users created successfully.";
} catch(PDOException $error) {
	echo $sql . "<br>" . $error->getMessage();
}

To run the install, just navigate to your install.php file in browser

crud11.JPG

This will create the database and table user

Step 7 : Adding a new User 

We’re going to go back to the public/create.php file. Right now, it’s just a form with a header and footer being pulled in. The new code we write will be added to the top of the file.

Since in this case we’re going to print out a  variable to the HTML, we need to properly convert the HTML characters, which will aid in preventing XSS attacks.

Let’s create a new file called common.php in the root of your project. This is a file that can be used to store functions for later use.

/var/www/html/common.php

<?php

/**
 * Escapes HTML for output
 *
 */

function escape($html) {
        return htmlspecialchars($html, ENT_QUOTES | ENT_SUBSTITUTE, "UTF-8");
}

Here’s the final code to add a new user

<?php

/**
 * Use an HTML form to create a new entry in the
 * users table.
 *
 */

if (isset($_POST['submit'])) {
    require "../config.php";
    require "../common.php";

try  {
 $connection = new PDO($dsn, $username, $password, $options);
 $new_user = array(
            "firstname" => $_POST['firstname'],
            "lastname"  => $_POST['lastname'],
            "email"     => $_POST['email'],
            "age"       => $_POST['age'],
            "location"  => $_POST['location']
        );
  $sql = sprintf(
  "INSERT INTO %s (%s) values (%s)",
  "users",
  implode(", ", array_keys($new_user)),
  ":" . implode(", :", array_keys($new_user))
  );

  $statement = $connection->prepare($sql);
        $statement->execute($new_user);
    } catch(PDOException $error) {
        echo $sql . "<br>" . $error->getMessage();
    }
}
?>
<?php require "templates/header.php"; ?>
<?php if (isset($_POST['submit']) && $statement) { ?>
    <blockquote><?php echo $_POST['firstname']; ?> successfully added.</blockquote>
<?php } ?>

<h2>Add a user</h2>
<form method="post">
    <label for="firstname">First Name</label>
    <input type="text" name="firstname" id="firstname">
    <label for="lastname">Last Name</label>
    <input type="text" name="lastname" id="lastname">
    <label for="email">Email Address</label>
    <input type="text" name="email" id="email">
    <label for="age">Age</label>
    <input type="text" name="age" id="age">
    <label for="location">Location</label>
    <input type="text" name="location" id="location">
    <input type="submit" name="submit" value="Submit">
</form>
<a href="index.php">Back to home</a>
<?php require "templates/footer.php"; ?>

crud12

crud13

Verifying the user entry from database level

crud14.JPG

Step 8 : Viewing and Filtering Users 

Add a small amount of CSS to our public/css/style.css file to make the tables legible once we create them.

table {
        border-collapse: collapse;
        border-spacing: 0;
}

td,
th {
        padding: 5px;
        border-bottom: 1px solid #aaa;
}

Now we’ll write a SELECT SQL query. We’re going to select all (*) from the users table, and filter by location.

Here is the final code

public/read.php 

<?php

/**
 * Function to query information based on 
 * a parameter: in this case, location.
 *
 */

if (isset($_POST['submit'])) {
    try  {
        
        require "../config.php";
        require "../common.php";

       $connection = new PDO($dsn, $username, $password, $options);
       $sql = "SELECT * 
       FROM users
       WHERE location = :location";
       $location = $_POST['location'];
       $statement = $connection->prepare($sql);
       $statement->bindParam(':location', $location, PDO::PARAM_STR);
       $statement->execute();
       $result = $statement->fetchAll();
} catch(PDOException $error) {
echo $sql . "<br>" . $error->getMessage();
}
}
?>
<?php require "templates/header.php"; ?>

<?php  
if (isset($_POST['submit'])) {
    if ($result && $statement->rowCount() > 0) { ?>
        <h2>Results</h2>
     <table>
           <thead>
              <tr>
                   <th>#</th>
                   <th>First Name</th>
                    <th>Last Name</th>
                    <th>Email Address</th>
                    <th>Age</th>
                    <th>Location</th>
                    <th>Date</th>
                </tr>
            </thead>
            <tbody>
        <?php foreach ($result as $row) { ?>
            <tr>
                <td><?php echo escape($row["id"]); ?></td>
                <td><?php echo escape($row["firstname"]); ?></td>
                <td><?php echo escape($row["lastname"]); ?></td>
                <td><?php echo escape($row["email"]); ?></td>
                <td><?php echo escape($row["age"]); ?></td>
                <td><?php echo escape($row["location"]); ?></td>
                <td><?php echo escape($row["date"]); ?> </td>
            </tr>
        <?php } ?>
        </tbody>
    </table>
    <?php } else { ?>
        <blockquote>No results found for <?php echo escape($_POST['location']); ?>.</blockquote>
    <?php } 
} ?> 

<h2>Find user based on location</h2>
<form method="post">
    <label for="location">Location</label>
    <input type="text" id="location" name="location">
    <input type="submit" name="submit" value="View Results">
</form>

<a href="index.php">Back to home</a>

<?php require "templates/footer.php"; ?>

 

crud15.JPG

Step 9 : Updating list of Users 

In index.php, add a link to update.php.

public/index.php

<?php include "templates/header.php"; ?>

<ul>
<li><a href="create.php"><strong>Create</strong></a> - Add a user</li>
<li><a href="read.php"><strong>Read</strong></a> - Find a user</li>
<li><a href="update.php"><strong>Update</strong></a> - Edit a user</li>
</ul>

<?php include "templates/footer.php"; ?>

 

crud16.JPG

Now that the main view of our app links to the update.php, let’s create the file.

The purpose of this file is to list all users in the database, and show an “Edit” link next to each user, which we’ll be able to click on and edit each user individually.

public/update.php

<?php

/**
 * List all users with a link to edit
 */

require "../config.php";
require "../common.php";

try {
  $connection = new PDO($dsn, $username, $password, $options);

  $sql = "SELECT * FROM users";

  $statement = $connection->prepare($sql);
  $statement->execute();

  $result = $statement->fetchAll();
} catch(PDOException $error) {
  echo $sql . "<br>" . $error->getMessage();
}
?>
<?php require "templates/header.php"; ?>
        
<h2>Update users</h2>

<table>
    <thead>
        <tr>
            <th>#</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Email Address</th>
            <th>Age</th>
            <th>Location</th>
            <th>Date</th>
            <th>Edit</th>
        </tr>
    </thead>
    <tbody>
    <?php foreach ($result as $row) : ?>
        <tr>
            <td><?php echo escape($row["id"]); ?></td>
            <td><?php echo escape($row["firstname"]); ?></td>
            <td><?php echo escape($row["lastname"]); ?></td>
            <td><?php echo escape($row["email"]); ?></td>
            <td><?php echo escape($row["age"]); ?></td>
            <td><?php echo escape($row["location"]); ?></td>
            <td><?php echo escape($row["date"]); ?> </td>
            <td><a href="update-single.php?id=<?php echo escape($row["id"]); ?>">Edit</a></td>
        </tr>
    <?php endforeach; ?>
    </tbody>
</table>

<a href="index.php">Back to home</a>

<?php require "templates/footer.php"; ?>

In update.php, we created edit links that detect the id of the users and create a unique URL for each user. In update-single.php, we have to figure out which id is being loaded in, because all the edit pages will load and be routed through this single page.

Let’s create update-single.php, load in the required files, and make an if/else statement. We’ll check if id is found in the URL, otherwise we’ll just show a brief error message and close the script.

public/update-single.php

<?php
/**
 * Use an HTML form to edit an entry in the
 * users table.
 *
 */
require "../config.php";
require "../common.php";
if (isset($_POST['submit'])) {
  try {
    $connection = new PDO($dsn, $username, $password, $options);
    $user =[
      "id"        => $_POST['id'],
      "firstname" => $_POST['firstname'],
      "lastname"  => $_POST['lastname'],
      "email"     => $_POST['email'],
      "age"       => $_POST['age'],
      "location"  => $_POST['location'],
      "date"      => $_POST['date']
    ];

    $sql = "UPDATE users 
            SET id = :id, 
              firstname = :firstname, 
              lastname = :lastname, 
              email = :email, 
              age = :age, 
              location = :location, 
              date = :date 
            WHERE id = :id";
  
  $statement = $connection->prepare($sql);
  $statement->execute($user);
  } catch(PDOException $error) {
      echo $sql . "<br>" . $error->getMessage();
  }
}
  
if (isset($_GET['id'])) {
  try {
    $connection = new PDO($dsn, $username, $password, $options);
    $id = $_GET['id'];
    $sql = "SELECT * FROM users WHERE id = :id";
    $statement = $connection->prepare($sql);
    $statement->bindValue(':id', $id);
    $statement->execute();
    
    $user = $statement->fetch(PDO::FETCH_ASSOC);
  } catch(PDOException $error) {
      echo $sql . "<br>" . $error->getMessage();
  }
} else {
    echo "Something went wrong!";
    exit;
}
?>

<?php require "templates/header.php"; ?>

<?php if (isset($_POST['submit']) && $statement) : ?>
	<blockquote><?php echo escape($_POST['firstname']); ?> successfully updated.</blockquote>
<?php endif; ?>

<h2>Edit a user</h2>

<form method="post">
    <?php foreach ($user as $key => $value) : ?>
      <label for="<?php echo $key; ?>"><?php echo ucfirst($key); ?></label>
	    <input type="text" name="<?php echo $key; ?>" id="<?php echo $key; ?>" value="<?php echo escape($value); ?>" <?php echo ($key === 'id' ? 'readonly' : null); ?>>
    <?php endforeach; ?> 
    <input type="submit" name="submit" value="Submit">
</form>

<a href="index.php">Back to home</a>

<?php require "templates/footer.php"; ?>

 

crud17.jpg

As a test we will update the age of the user from 27 to 28  with id = 4

crud18

Step 10 : Deleting the Users 

Back in index.php, let’s add an entry for delete.

<?php include "templates/header.php"; ?>

<ul>
   <li><a href="create.php"><strong>Create</strong></a> - Add a user</li>
   <li><a href="read.php"><strong>Read</strong></a> - Find a user</li>
   <li><a href="update.php"><strong>Update</strong></a> - Edit a user</li>
   <li><a href="delete.php"><strong>Delete</strong></a> - Delete a user</li>
</ul>

<?php include "templates/footer.php"; ?>

The DELETE statement is just like SELECT, and we’ll check for the $_GET superglobal again. If the proper id is loaded into the URL, PHP will delete that user.

public/delete.php

<?php

/**
 * Delete a user
 */

require "../config.php";
require "../common.php";

if (isset($_GET["id"])) {
  try {
    $connection = new PDO($dsn, $username, $password, $options);
  
    $id = $_GET["id"];

    $sql = "DELETE FROM users WHERE id = :id";

    $statement = $connection->prepare($sql);
    $statement->bindValue(':id', $id);
    $statement->execute();

    $success = "User successfully deleted";
  } catch(PDOException $error) {
    echo $sql . "<br>" . $error->getMessage();
  }
}

try {
  $connection = new PDO($dsn, $username, $password, $options);

  $sql = "SELECT * FROM users";

  $statement = $connection->prepare($sql);
  $statement->execute();

  $result = $statement->fetchAll();
} catch(PDOException $error) {
  echo $sql . "<br>" . $error->getMessage();
}
?>
<?php require "templates/header.php"; ?>
        
<h2>Delete users</h2>

<?php if ($success) echo $success; ?>

<table>
  <thead>
    <tr>
      <th>#</th>
      <th>First Name</th>
      <th>Last Name</th>
      <th>Email Address</th>
      <th>Age</th>
      <th>Location</th>
      <th>Date</th>
      <th>Delete</th>
    </tr>
  </thead>
  <tbody>
  <?php foreach ($result as $row) : ?>
    <tr>
      <td><?php echo escape($row["id"]); ?></td>
      <td><?php echo escape($row["firstname"]); ?></td>
      <td><?php echo escape($row["lastname"]); ?></td>
      <td><?php echo escape($row["email"]); ?></td>
      <td><?php echo escape($row["age"]); ?></td>
      <td><?php echo escape($row["location"]); ?></td>
      <td><?php echo escape($row["date"]); ?> </td>
      <td><a href="delete.php?id=<?php echo escape($row["id"]); ?>">Delete</a></td>
    </tr>
  <?php endforeach; ?>
  </tbody>
</table>

<a href="index.php">Back to home</a>

<?php require "templates/footer.php"; ?>

crud19.JPG

Step 11 : Adding CSRF Protection

CSRF stands for Cross-Site Request Forgery, and is a way an attacker can trick a browser into executing a malicious action.

To add this in, we’ll go back to common.php, generate the CSRF and assign it to $_SESSION[‘csrf’]

<?php
session_start();
if (empty($_SESSION['csrf'])) {
	if (function_exists('random_bytes')) {
		$_SESSION['csrf'] = bin2hex(random_bytes(32));
	} else if (function_exists('mcrypt_create_iv')) {
		$_SESSION['csrf'] = bin2hex(mcrypt_create_iv(32, MCRYPT_DEV_URANDOM));
	} else {
		$_SESSION['csrf'] = bin2hex(openssl_random_pseudo_bytes(32));
	}
}
/**
 * Escapes HTML for output
 *
 */
function escape($html) {
    return htmlspecialchars($html, ENT_QUOTES | ENT_SUBSTITUTE, "UTF-8");
}

One thought on “Create a Simple CRUD Database App in HTML with MySQL Database and PHP

Add yours

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Powered by WordPress.com.

Up ↑

%d bloggers like this: