3 Steps Simple PHP MySQL CRUD Application (For Beginners)

Welcome to a tutorial and example on how to create a simple 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, not quite.

CRUD refers to the basic computer operations of:

  • Create – Add new entries.
  • Read – Get entries.
  • Update – Edit entries.
  • Delete – Remove entries.

So long as an application do all of the above, it “qualifies” as a CRUD application. For example, add user, update user, get user, remove user.

Some of you sharp code ninjas should have also figured it out instantly. CRUD is inevitable, all applications will have to deal with it in one way or another. For example – Write, read, update, delete files. Create, read, update, delete products… The list can go on forever.

So when it comes to a “CRUD application”, we are more like “making sure there are basic CRUD functions” rather than “build according to a framework”. That’s all. Read on if you need an actual example!

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

 

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, feel free to comment below. I try to answer short 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.

 

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.

 

 

PHP CRUD APPLICATION

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

 

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

 

 

STEP 2) PHP USERS CLASS (CRUD LIBRARY)

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) { exit($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);
      return true;
    } catch (Exception $ex) {
      $this->error = $ex->getMessage();
      return false;
    }
  }

  // (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 will create a PHP user 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 automatically closes the database connection.
  • (C) function query() is but a simple support function to run an SQL query.
  • (D To G) Literally the CRUD functions.
    • Create – function add()
    • Read – function getAll() and function get()
    • Update – function edit()
    • Delete – function remove()
  • (H & I) Self-explanatory.

 

 

STEP 3) HTML PAGES

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

 

(CREATE) ADD 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) EDIT USER PAGE

3c-update.php
<?php
// (A) EDIT USER ON FORM SUBMIT
f (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) REMOVE 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 crazy “master troll code ninjas” start to sing their looney tunes:

  • Personal opinion – CRUD is not 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 – Some people treat CRUD as a framework, as it does offer guidelines for designing an application. I agree with that line of argument too.
  • The above example is not the “perfect way” to build a “CRUD application”.
  • There are no fixed ways to develop applications, there is no “best way” to do it.
  • 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!

5 thoughts on “3 Steps Simple PHP MySQL CRUD Application (For Beginners)”

Leave a Comment

Your email address will not be published.