How To Create A CRUD Application With PHP MySQL – Simple Example

Welcome to a tutorial and example on how to create a CRUD application with PHP and MySQL. So you may have stumbled on this “CRUD thing” somewhere, and wonder if it is some sort of a standard framework to develop applications. Well, no, not quite.

CRUD refers to the 4 basic computer operations of create, read, update, and delete. To create a CRUD application, we have to offer the 4 respective functions. For example, we need to have the following to manage users:

  • Create/add users.
  • Read/get users.
  • Update/edit users.
  • Delete/remove users.

Yes, some of you sharp code ninjas should have already figured it out. No matter what kind of application you are building, CRUD operations are inevitable; CRUD itself is a set of basic operations that will exist regardless, it is not a framework per se.

So when it comes to “CRUD applications”, we are more like “making sure there are basic CRUD functions” rather than “build according to a framework”. That’s all. This should cover the basics, but if you need an actual simple example – Read on!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

TABLE OF CONTENTS

Download & Notes CRUD Application Useful Bits & Links
The End

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

EXAMPLE CODE DOWNLOAD

Click here to download the source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

QUICK NOTES

  • Create a database and import the 1-database.sql file.
  • Change the database settings in 2-users-lib.php to your own.
  • That’s all. Launch 3X-XX.php for the CRUD demo.

If you spot a bug, please feel free to comment below. I try to answer questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

 

PHP CRUD APPLICATION

All right, let us now go into the details on how to develop a simple PHP MySQL CRUD application.

 

1) DATABASE TABLE

1-database.sql
CREATE TABLE `users` (
  `user_id` int(11) NOT NULL,
  `user_email` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `user_email` (`user_email`);
 
ALTER TABLE `users`
  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT;

The first step is to lay the foundations, create the necessary database tables. In this example, we will be using this simple users table with only 2 fields:

  • user_id The primary key.
  • user_email User’s email address, unique field.

 

 

2) CRUD PHP USERS CLASS

2-users-lib.php
<?php
class Users {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo = null;
  private $stmt = null;
  public $error = "";
  function __construct () {
    try {
      $this->pdo = new PDO(
        "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET, 
        DB_USER, DB_PASSWORD, [
          PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
          PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ]
      );
    } catch (Exception $ex) { die($ex->getMessage()); }
  }

  // (B) DESTRUCTOR - CLOSE DATABASE CONNECTION
  function __destruct () {
    if ($this->stmt!==null) { $this->stmt = null; }
    if ($this->pdo!==null) { $this->pdo = null; }
  }
  
  // (C) SUPPORT FUNCTION - SQL QUERY
  function query ($sql, $data) {
    try {
      $this->stmt = $this->pdo->prepare($sql);
      $this->stmt->execute($data);
    } catch (Exception $ex) {
      $this->error = $ex->getMessage();
      return false;
    }
    return true;
  }

  // (D) CREATE - ADD NEW USER
  function add ($email) {
    return $this->query(
      "INSERT INTO `users` (`user_email`) VALUES (?)", [$email]
    );
  }

  // (E) READ - GET USER / GET ALL USERS
  function getAll () {
    if ($this->query(
      "SELECT * FROM `users`", null
    )) { return $this->stmt->fetchAll(); }
    return false;
  }

  function get ($id) {
    if ($this->query(
      "SELECT * FROM `users` WHERE `user_".(is_numeric($id)?"id":"email")."`=?", [$id]
    )) { return $this->stmt->fetch(); }
    return false;
  }
  
  // (F) UPDATE - EDIT USER
  function edit ($email, $id) {
    return $this->query(
      "UPDATE `users` SET `user_email`=? WHERE `user_id`=?", [$email, $id]
    );
  }
  
  // (G) DELETE - REMOVE USER
  function remove ($id) {
    return $this->query(
      "DELETE FROM `users` WHERE `user_id`=?", [$id]
    );
  }
}

// (H) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define('DB_HOST', 'localhost');
define('DB_NAME', 'test');
define('DB_CHARSET', 'utf8');
define('DB_USER', 'root');
define('DB_PASSWORD', '');

// (I) NEW USER OBJECT
$USR = new Users();

Next, we create the PHP library with the corresponding CRUD functions. This may look intimidating at first, but keep calm and study carefully:

  • A & B – When the $USR = new Users() object is created, the constructor will connect to the database; The destructor will automatically close the database connection when done.
  • C – function query() is but a simple support function to run an SQL query.
  • D To G – The rest is literally the CRUD functions, running only SQL statements!
    • Create – function add()
    • Read – function getAll() and function get()
    • Update – function edit()
    • Delete – function remove()

 

 

3) HTML PAGES

Lastly, all that’s left is to create the user interface, the HTML pages.

 

CREATE USER PAGE

3a-create.php
 <?php
// (A) CREATE USER ON FORM SUBMIT
if (isset($_POST['email'])) {
  require "2-users-lib.php";
  echo $USR->add($_POST['email']) 
    ? "<div>OK</div>" : "<div>{$USR->error}</div>" ;
}
?>
 
<!-- (B) CREATE USER FORM -->
<form method="post">
  <input type="email" name="email" placeholder="email" required/>
  <input type="submit" value="Go!"/>
</form>

 

(READ) GET AND SHOW USERS 

3b-read.php
 <!-- GET + LIST USERS -->
<ul><?php
require "2-users-lib.php";
$users = $USR->getAll();
if (count($users)>0) { foreach ($users as $u) {
  echo "<li>{$u['user_email']}</li>";
}}
?></ul>

 

 

UPDATE USER PAGE

3c-update.php
<?php
// (A) EDIT USER ON FORM SUBMIT
if (isset($_POST['email'])) {
  require "2-users-lib.php";
  echo $USR->edit($_POST['email'], $_POST['id']) 
    ? "<div>OK</div>" : "<div>{$USR->error}</div>" ;
}
?>
 
<!-- (B) UPDATE USER FORM -->
<form method="post">
  <input type="number" name="id" placeholder="user id" required/>
  <input type="email" name="email" placeholder="email" required/>
  <input type="submit" value="Go!"/>
</form>

 

DELETE USER PAGE

3d-delete.php
<?php
// (A) DELETE USER ON FORM SUBMIT
if (isset($_POST['id'])) {
  require "2-users-lib.php";
  echo $USR->remove($_POST['id']) 
    ? "<div>OK</div>" : "<div>{$USR->error}</div>" ;
}
?>

<!-- (B) DELETE USER FORM -->
<form method="post">
  <input type="number" name="id" placeholder="user id" required/>
  <input type="submit" value="Go!"/>
</form>

 

 

USEFUL BITS & LINKS

That’s all for the simple example, and here are a few more extras that may be useful.

 

CRUD IS NOT A FRAMEWORK

Before the crazy “master troll code ninjas” start to sing their looney tunes…

  • Personally, I don’t consider CRUD to be a framework. Simply because it is more accurately “basic operations” (it’s there no matter what you do), rather than a “framework” (a design pattern). Feel free to disagree.
  • The above example is not the only “perfect way” to build a “CRUD application”.
  • There are no fixed ways to do it, there is no “best way” to do it… Since CRUD is a basic operation – So long as an application does CRUD, it can be called a “CRUD application”. No need to think too deep.

 

LINKS & REFERENCES

 

THE END

Thank you for reading, and we have come to the end of this guide. I hope this has helped you to better understand. If you have anything to add to this guide, please feel free to comment below. Good luck and happy coding!

3 thoughts on “How To Create A CRUD Application With PHP MySQL – Simple Example”

Leave a Comment

Your email address will not be published. Required fields are marked *