How to Develop a Web Application With PHP MySQL – Simple Example

Welcome to a quick tutorial on how to develop a simple web application with PHP and MySQL. When it comes to building a full-stack web application, some beginners go blank and do not know where to get started.

Web applications are developed in stages, in a step-by-step manner.

  1. Gather the requirements – Know what the users want, what the application should do, what the goals are.
  2. Design the structure of the database.
  3. Build the core PHP scripts and libraries.
  4. Design the interface, build the pages with HTML, CSS, Javascript.
  5. Finally, test and deploy the web application.

Yes, some of you may have already heard of it, this is called the software development life cycle (SDLC). One does not tackle a project without any plans but takes it apart piece by piece. Let us walk through the development cycle of a simple example to-do list in this guide – 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.

 

 

TABLE OF CONTENTS

Download & Notes Project Planning The Database
PHP Library Clent-Side Pages 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-todo-lib.php to your own.
  • Launch 3a-todo.php in your browser.
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.

 

 

PART 1) PROJECT PLANNING

Big or small projects, it all starts with “boring planning”. Yes, even for the smallest personal projects, we need to have a simple plan on “what to build” and “how to build”.

 

GATHERING THE REQUIREMENTS

This is the most important “step 1” that some people miss out on, thinking that it’s useless and a waste of time. Well, no. Even for the smallest personal projects, there have to be requirements on what needs to be done. Projects that start with “everything and anything” will most likely end with “nothing”.

So start by asking a lot of questions. Understand the problem, know what the users want, have a general idea of what needs to be done. For this example project:

  • We need to create a simple to-do list.
  • The tasks list should be stored in a database.
  • There should be flags to indicate if a task is still “pending”, “done”, or “canceled”.
  • Have a page to manage (add, update, delete) the tasks.

That simple.

 

 

PART 2) BUILDING A DATABASE

Now that we are done with the planning and have a rough idea of the system, let’s move on to deal with the foundation of the project – The database tables.

 

TO DO DATABASE TABLE

1-database.sql
CREATE TABLE `todo` (
  `todo_id` int(11) NOT NULL,
  `todo_task` text NOT NULL,
  `todo_status` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `todo`
  ADD PRIMARY KEY (`todo_id`);

ALTER TABLE `todo`
  MODIFY `todo_id` int(11) NOT NULL AUTO_INCREMENT;

This should be straightforward enough:

  • todo_id The task ID, primary key.
  • todo_task Description of the task itself.
  • todo_status The current status of the task, 0 for pending, 1 for done, 2 for canceled.

Yep, that should be sufficient to cover the requirements of “keeping the tasks and track their status”.

 

 

PART 3) PHP LIBRARY

Moving on, let us create the PHP library to manage the to-do tasks.

 

TO-DO TASKS PHP CLASS

2-todo-lib.php
<?php
class ToDo {
  // (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) SAVE TO-DO TASK
  function save ($task, $status, $id=null) {
    // (D1) ADD NEW TASK
    if ($id===null) {
      return $this->query(
        "INSERT INTO `todo` (`todo_task`, `todo_status`) VALUES (?,?)", 
        [$task, $status]
      );
    }
    // (D2) UPDATE TASK
    else {
      return $this->query(
        "UPDATE `todo` SET `todo_task`=?, `todo_status`=? WHERE `todo_id`=?", 
        [$task, $status, $id]
      );
    }
  }

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

  // (F) DELETE TASK
  function del ($id) {
    return $this->query(
      "DELETE FROM `todo` WHERE `task_id`=?", [$id]
    );
  }
}

// (G) 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", "");

// (H) NEW TO-DO OBJECT
$TODO = new ToDo();

 

 

Yikes! This looks complicated, but keep calm and study closely:

  • When the $TODO = new ToDo() object is created, the constructor will connect to the database; The destructor automatically closes the connection when done.
  • function query() is only a simple helper function that runs SQL queries.
  • To cover the “manage to-do tasks”, there are only 3 essential functions!
    • function save() Add or update a task.
    • function getAll() Get all to-do tasks.
    • function del() Delete a task.

 

PART 4) CLIENT-SIDE PAGES

Now that all the foundations are ready, the final step is to create a page to manage the to-do tasks (user interface).

 

TO-DO MANAGE TASKS PAGE

3a-todo.php
<?php
// (A) ADD/UPDATE/DELETE TASK IF FORM SUBMITTED
require "2-todo-lib.php";
if (isset($_POST["action"])) {
  // (A1) SAVE TASK
  if ($_POST["action"]=="save") {
    $pass = $TODO->save(
      $_POST["task"], $_POST["status"], (isset($_POST["id"])?$_POST["id"]:null)
    );
  }
 
  // (A2) DELETE TASK
  else { $pass = $TODO->del($_POST["id"]); }
 
  // (A3) SHOW RESULT
  echo "<div class='notify'>";
  echo $pass ? "OK" : $TODO->error ;
  echo "</div>";
}
?>
 
<!-- (B) NINJA DELETE FORM -->
<form id="ninForm" method="post">
  <input type="hidden" name="action" value="del"/>
  <input type="hidden" name="id" id="ninID"/>
</form>
 
<div id="tasks">
  <!-- (C) ADD NEW TASK -->
  <form method="post">
    <input type="hidden" name="action" value="save"/>
    <input type="text" id="taskadd" name="task" placeholder="Task" required/>
    <select name="status">
      <option value="0">Pending</option>
      <option value="1">Done</option>
      <option value="2">Canceled</option>
    </select>
    <input type="submit" value="Add"/>
  </form>
 
  <!-- (D) LIST TASKS -->
  <?php
  $tasks = $TODO->getAll();
  if (count($tasks)!=0) { foreach ($tasks as $t) { ?>
    <form method="post">
      <input type="hidden" name="action" value="save"/>
      <input type="text" id="taskadd" name="task" placeholder="Task" required/>
      <select name="status">
        <option value="0">Pending</option>
        <option value="1">Done</option>
        <option value="2">Canceled</option>
      </select>
      <input type="submit" value="Add"/>
    </form>
  <?php }} ?>
</div>

The above may look rather confusing at first, but just take some time to go through – We are pretty much only using the library to generate the necessary HTML list. For the sake of simplicity, I have also directly implemented the HTML forms and PHP processing all into a single page.

But usually, I will split the processing into a separate “PHP AJAX handler script”; It is best to keep this as a “pure interface page”, much easier to update when users complain “this does not look good” to a technical code ninja who has zero education in design.

 

 

THE CSS

3b-todo.css
html, body { font-family: arial, sans-serif; }
.notify {
  background: #ecefff;
  border: 1px solid #bfc4ff;
  padding: 5px;
  margin-bottom: 10px;
}
#tasks { max-width: 500px; }
#tasks form {
  display: grid;
  grid-template-columns: 10% 60% 20% 10%;
  padding: 10px;
  background: #f7f7f7;
  border: 1px solid #ccc;
  margin-bottom: 10px;
}
#tasks input, #tasks select {
  box-sizing: border-box;
  padding: 10px;
  cursor: pointer;
  border: 0;
}
#tasks input[type=button], #tasks input[type=submit] { color: #fff; }
#tasks input[type=button] { background: #a72020; }
#tasks input[type=submit] { background: #577ed8; }
#taskadd { grid-column: span 2; }

Well, just some cosmetics here.

 

THE JAVASCRIPT

3c-todo.js
function deltask (id) { if (confirm("Delete task?")) {
  document.getElementById("ninID").value = id;
  document.getElementById("ninForm").submit();
}}

Finally, a small snippet of necessary Javascript.

 

USEFUL BITS & LINKS

That’s all for this example, and here is a small section on some extras and links that may be useful to you.

 

SOFTWARE DEVELOPMENT NEVER ENDS

One last thing to place here as a “disclaimer”, software development never actually ends. For example, we might want to improve on the to-do list in the future:

  • Requirement – Add a timestamp to track when the task is last updated.
  • Database – Add a new new “updated” timestamp column.
  • PHP library – Update the functions to also include the timestamp.
  • To-do page – Show the timestamp somewhere.

Yes, that is running through the development cycle once again; It never ends and will continue to loop when new requirements pop up.

 

 

LINKS & REFERENCES

The above example pretty much… scratched the surface. Here are a couple more links if you want to learn more.

 

INFOGRAPHIC CHEAT SHEET

How to Develop Web Application With PHP MySQL (Click to Enlarge)

 

THE END

Thank you for reading, and we have come to the end of this guide. I hope that it has helped you to better understand the stages of developing a web application. It may not be easy to develop a full-stack application at first, but as with the steps above – Break the project down into smaller parts, and you will be able to conquer them piece by piece. If you have anything to share, please feel free to comment below. Good luck and happy coding!

2 thoughts on “How to Develop a Web Application With PHP MySQL – Simple Example”

  1. Thanks for this tutorial.
    Is there a specific reason you set “tinyint(1)” – instead of simply “tinyint” – for “todo_status”, since you don’t use zerofill?

    1. No particular reason actually, I would say it’s more for “cosmetics”. An “uncapped” TINYINT can go up to 127, but a simple status flag wouldn’t need that many… TINYINT(1) does well enough to serve that purpose.

Leave a Comment

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