 | Kirby Lee Kian Theng <kirby.kl05@gmail.com> |
| [Loanstreet] Task Assignment - Kian Theng (Feb 6, 2014 - Feb 12, 2014) |
List of Contents
Introduction
Step 1: Creating a sample Database table
Step 2: Connecting to Database
Step 3: Create an "Index" page
Step 4: Adding "Create" button and "Read" button
Step 5: Creating a "Create" page
Step 6: Creating a "Read" page
Step 7: Adding "Update" button and "Delete" button
Step 8: Creating a "Update" page
Step 9: Creating a "Delete" page
Final and Source Code
Introduction
Creating CRUD grid is a very common task in web development (CRUD stands for Create/Read/Update/Delete). If you are a senior web developer, you must have created plenty of CRUD grids already. They maybe exist in a content management system, an inventory management system, or accounting software. If you just started web development, you are certainly going to experience lots of CRUD grids' creation work in your later career.
The main purpose of a CRUD grid is that enables users create/read/update/delete data. Normally data is stored in MySQL Database.PHP will be the server-side language that manipulates MySQL Database tables to give front-end users power to perform CRUD actions.
In this tutorial series, we will go through steps of a creating PHP CRUD grid. We want to demonstrate how PHP as a server side language, communicates with backend MySQL, and meanwhile renders front-end HTML. We hope you can learn something from this tutorial.
Step 1: Creating a sample Database table
In this tutorial, we will work on a simple Database table as below. After this tutorial, you should use the idea here to create CRUD grid on your own Database tables.
Import the Database table below to your own MySQL Database.
CREATE TABLE Books( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR(100), author VARCHAR(100), date_pub TIMESTAMP, notes VARCHAR(200) );
As you can see, this is a very simple table for tracking books' information (title, author, date published and notes). And we prefer an auto incremental primary key (id).
Step 2: Connecting to Database
Create a PHP file "database.php"; this file contains a PHP class named "Database". Throughout this application, Database handles all the stuff related to database connections, such as connecting and disconnecting.
<?php
class Database{
private static $dbName = "CRUD_Books";
private static $dbHost = "localhost";
private static $dbUsername = "root";
private static $dbUserPassword = "";
private static $cont = null;
public function __construct(){
die("Init function is not allowed");
}
public static function connect(){
// One connection through whole application
if(null == self::$cont){
try{
self::$cont = new PDO("mysql:host=".self::$dbHost.";"."dbname=".self::$dbName,self::$dbUsername,self::$dbUserPassword);
}
catch(PDOException $e){
die($e->getMessage());
}
}
return self::$cont;
}
public static function disconnect(){
self::$cont = null;
}
};
?>
As you can see, we are using PDO for database access. There are a lot of benefits of using PDO. One of the most significant benefits is that it provides a uniform method of access to multiple databases.
To use this class, you will need to supply correct values for $dbName, $dbHost, $dbUsername, $dbUserPassword.
- $dbName: Database name which you use to store 'customers' table.
- $dbHost: Database host, this is normally "localhost".
- $dbUsername: Database username.
- $dbUserPassword: Database user's password.
Let us take a look at three functions of this class:
- __construct(): This is the constructor of class Database. Since it is a static class, initialization of this class is not allowed. To prevent misuse of the class, we use a "die" function to remind users.
- connect: This is the main function of this class. It uses singleton pattern to make sure only one PDO connection exist across the whole application. Since it is a static method. We use Database::connect() to create a connection.
- disconnect: Disconnect from database. It simply sets connection to NULL. We need to call this function to close connection.
Step 3: Create an "Index" page
Here comes the grid without CRUD capabilities. Because CRUD operation can only be performed when there is a grid. We firstly need to build a grid. From there we can subsequently add "Create" page, "Read" page, "Update" page and finally "Delete" page.
After that, create a PHP file "index.php" and copy the content below:
<!DOCTYPE html>
<html>
<head>
<h1>Listing books</h1>
</head>
<body>
<table>
<thead>
<tr>
<th>Title</th>
<th>Author</th>
<th>Published at</th>
<th>Notes</th>
</tr>
</thead>
<tbody>
<?php
include "database.php";
$pdo = Database::connect();
$sql = "SELECT * FROM Books ORDER BY id ASC";
foreach($pdo->query($sql) as $row){
echo "<tr>";
echo "<td>".$row['title']."</td>";
echo "<td>".$row['author']."</td>";
echo "<td>".$row['date_pub']." UTC</td>";
echo "<td>".$row['notes']."</td>";
echo "</tr>";
}
Database::disconnect();
?>
</tbody>
</table>
</body>
</html>
This is the main part of the file. It is where we retrieve data from database and show it on the grid. Let dig deep into each lines carefully.
<table>
<thead>
<tr>
<th>Title</th>
<th>Author</th>
<th>Published at</th>
<th>Notes</th>
</tr>
</thead>
<tbody>
<?php
include "database.php";
$pdo = Database::connect();
$sql = "SELECT * FROM Books ORDER BY id ASC";
foreach($pdo->query($sql) as $row){
echo "<tr>";
echo "<td>".$row['title']."</td>";
echo "<td>".$row['author']."</td>";
echo "<td>".$row['date_pub']." UTC</td>";
echo "<td>".$row['notes']."</td>";
echo "</tr>";
}
Database::disconnect();
?>
</tbody>
</table>
We firstly create a table with headers corresponding to database table "Books"'s fields. Which includes "Title", "Author", "Date Published", "Notes":
<thead>
<tr>
<th>Title</th>
<th>Author</th>
<th>Published at</th>
<th>Notes</th>
</tr>
</thead>
Then we include "database.php", create a PDO connection to database, and use a general "SELECT" statement to retrieve data. Lastly, we loop through each row to print content. Do not forget to close the connection as we mentioned at the beginning.
<tbody>
<?php
include "database.php";
$pdo = Database::connect();
$sql = "SELECT * FROM Books ORDER BY id ASC";
foreach($pdo->query($sql) as $row){
echo "<tr>";
echo "<td>".$row['title']."</td>";
echo "<td>".$row['author']."</td>";
echo "<td>".$row['date_pub']." UTC</td>";
echo "<td>".$row['notes']."</td>";
echo "</tr>";
}
Database::disconnect();
?>
</tbody>
If you have followed correctly, you should have an empty grid as below if you navigate to "index.php" from browser:
The grid is empty because there is no actual data inside "Books" table, to test if it is actually working, you can manually insert data into "Books" table. It should show them on the grid.
Step 4: Adding "Create" button and "Read" button
To start, we firstly need a navigation button which leads to "Create" page and also a button to read a record. Open "index.php" file we created in step 3 of this tutorial. And add a "Create" button at the bottom of the table, and also "Read" buttons for each row of the table.
Now the "index.php" file's code should look like below, the highlighted codes are what we have added:
<!DOCTYPE html>
<html>
<head>
<h1>Listing books</h1>
</head>
<body>
<table>
<thead>
<tr>
<th>Title</th>
<th>Author</th>
<th>Published at</th>
<th>Notes</th>
<th></th>
</tr>
</thead>
<tbody>
<?php
include "database.php";
$pdo = Database::connect();
$sql = "SELECT * FROM Books ORDER BY id ASC";
foreach($pdo->query($sql) as $row){
echo "<tr>";
echo "<td>".$row['title']."</td>";
echo "<td>".$row['author']."</td>";
echo "<td>".$row['date_pub']." UTC</td>";
echo "<td>".$row['notes']."</td>";
echo "<td><a href='read.php?id=".$row['id']."'>Show</a></td>";
echo "</tr>";
}
Database::disconnect();
?>
</tbody>
</table>
<p>
<a href="create.php">New Book</a>
</p>
</body>
</html>
Now if you navigate to "index.php page". You should notice a "Create" button. However since its linked page is not created, it will redirect to an error page if you click the button. We will fix that next step:
Step 5: Creating a "Create" page
Create a PHP file "create.php"; This file contains an html form and it is the "Create" part of CRUD grid.
We will go through this file as two parts.
First part of the codes is an html form. It is pretty straightforward; all it does is to create an html form. As we need to provide validation for form entries. Hence there is a PHP variable for holding validation error for each field. Copy the code below to "create.php" file:
<!DOCTYPE html>
<html>
<head>
<h1>New book</h1>
</head>
<body>
<form action="create.php" method="post">
Title<br>
<input type="text" name="title" id="title"><br>
Author<br>
<input type="text" name="author" id="author"><br>
Published at<br>
<select name="year">
<option value="2009" <?php if($year == 2009){echo("selected");}?>>2009</option>
<option value="2010" <?php if($year == 2010){echo("selected");}?>>2010</option>
<option value="2011" <?php if($year == 2011){echo("selected");}?>>2011</option>
<option value="2012" <?php if($year == 2012){echo("selected");}?>>2012</option>
<option value="2013" <?php if($year == 2013){echo("selected");}?>>2013</option>
<option value="2014" <?php if($year == 2014){echo("selected");}?>>2014</option>
<option value="2015" <?php if($year == 2015){echo("selected");}?>>2015</option>
<option value="2016" <?php if($year == 2016){echo("selected");}?>>2016</option>
<option value="2017" <?php if($year == 2017){echo("selected");}?>>2017</option>
<option value="2018" <?php if($year == 2018){echo("selected");}?>>2018</option>
<option value="2019" <?php if($year == 2019){echo("selected");}?>>2019</option>
</select>
<select name="month">
<option value="01" <?php if($month == 1){echo("selected");}?>>January</option>
<option value="02" <?php if($month == 2){echo("selected");}?>>February</option>
<option value="03" <?php if($month == 3){echo("selected");}?>>March</option>
<option value="04" <?php if($month == 4){echo("selected");}?>>April</option>
<option value="05" <?php if($month == 5){echo("selected");}?>>May</option>
<option value="06" <?php if($month == 6){echo("selected");}?>>June</option>
<option value="07" <?php if($month == 7){echo("selected");}?>>July</option>
<option value="08" <?php if($month == 8){echo("selected");}?>>August</option>
<option value="09" <?php if($month == 9){echo("selected");}?>>September</option>
<option value="10" <?php if($month == 10){echo("selected");}?>>October</option>
<option value="11" <?php if($month == 11){echo("selected");}?>>November</option>
<option value="12" <?php if($month == 12){echo("selected");}?>>December</option>
</select>
<select name="day">
<option value="01" <?php if($day == 1){echo("selected");}?>>1</option>
<option value="02" <?php if($day == 2){echo("selected");}?>>2</option>
<option value="03" <?php if($day == 3){echo("selected");}?>>3</option>
<option value="04" <?php if($day == 4){echo("selected");}?>>4</option>
<option value="05" <?php if($day == 5){echo("selected");}?>>5</option>
<option value="06" <?php if($day == 6){echo("selected");}?>>6</option>
<option value="07" <?php if($day == 7){echo("selected");}?>>7</option>
<option value="08" <?php if($day == 8){echo("selected");}?>>8</option>
<option value="09" <?php if($day == 9){echo("selected");}?>>9</option>
<option value="10" <?php if($day == 10){echo("selected");}?>>10</option>
<option value="11" <?php if($day == 11){echo("selected");}?>>11</option>
<option value="12" <?php if($day == 12){echo("selected");}?>>12</option>
<option value="13" <?php if($day == 13){echo("selected");}?>>13</option>
<option value="14" <?php if($day == 14){echo("selected");}?>>14</option>
<option value="15" <?php if($day == 15){echo("selected");}?>>15</option>
<option value="16" <?php if($day == 16){echo("selected");}?>>16</option>
<option value="17" <?php if($day == 17){echo("selected");}?>>17</option>
<option value="18" <?php if($day == 18){echo("selected");}?>>18</option>
<option value="19" <?php if($day == 19){echo("selected");}?>>19</option>
<option value="20" <?php if($day == 20){echo("selected");}?>>20</option>
<option value="21" <?php if($day == 21){echo("selected");}?>>21</option>
<option value="22" <?php if($day == 22){echo("selected");}?>>22</option>
<option value="23" <?php if($day == 23){echo("selected");}?>>23</option>
<option value="24" <?php if($day == 24){echo("selected");}?>>24</option>
<option value="25" <?php if($day == 25){echo("selected");}?>>25</option>
<option value="26" <?php if($day == 26){echo("selected");}?>>26</option>
<option value="27" <?php if($day == 27){echo("selected");}?>>27</option>
<option value="28" <?php if($day == 28){echo("selected");}?>>28</option>
<option value="29" <?php if($day == 29){echo("selected");}?>>29</option>
<option value="30" <?php if($day == 30){echo("selected");}?>>30</option>
<option value="31" <?php if($day == 31){echo("selected");}?>>31</option>
</select>
—
<select name="hour">
<option value="00" <?php if($hour == 0){echo("selected");}?>>00</option>
<option value="01" <?php if($hour == 1){echo("selected");}?>>01</option>
<option value="02" <?php if($hour == 2){echo("selected");}?>>02</option>
<option value="03" <?php if($hour == 3){echo("selected");}?>>03</option>
<option value="04" <?php if($hour == 4){echo("selected");}?>>04</option>
<option value="05" <?php if($hour == 5){echo("selected");}?>>05</option>
<option value="06" <?php if($hour == 6){echo("selected");}?>>06</option>
<option value="07" <?php if($hour == 7){echo("selected");}?>>07</option>
<option value="08" <?php if($hour == 8){echo("selected");}?>>08</option>
<option value="09" <?php if($hour == 9){echo("selected");}?>>09</option>
<option value="10" <?php if($hour == 10){echo("selected");}?>>10</option>
<option value="11" <?php if($hour == 11){echo("selected");}?>>11</option>
<option value="12" <?php if($hour == 12){echo("selected");}?>>12</option>
<option value="13" <?php if($hour == 13){echo("selected");}?>>13</option>
<option value="14" <?php if($hour == 14){echo("selected");}?>>14</option>
<option value="15" <?php if($hour == 15){echo("selected");}?>>15</option>
<option value="16" <?php if($hour == 16){echo("selected");}?>>16</option>
<option value="17" <?php if($hour == 17){echo("selected");}?>>17</option>
<option value="18" <?php if($hour == 18){echo("selected");}?>>18</option>
<option value="19" <?php if($hour == 19){echo("selected");}?>>19</option>
<option value="20" <?php if($hour == 20){echo("selected");}?>>20</option>
<option value="21" <?php if($hour == 21){echo("selected");}?>>21</option>
<option value="22" <?php if($hour == 22){echo("selected");}?>>22</option>
<option value="23" <?php if($hour == 23){echo("selected");}?>>23</option>
</select>
:
<select name="minute">
<option value="00" <?php if($minute == 0){echo("selected");}?>>00</option>
<option value="01" <?php if($minute == 1){echo("selected");}?>>01</option>
<option value="02" <?php if($minute == 2){echo("selected");}?>>02</option>
<option value="03" <?php if($minute == 3){echo("selected");}?>>03</option>
<option value="04" <?php if($minute == 4){echo("selected");}?>>04</option>
<option value="05" <?php if($minute == 5){echo("selected");}?>>05</option>
<option value="06" <?php if($minute == 6){echo("selected");}?>>06</option>
<option value="07" <?php if($minute == 7){echo("selected");}?>>07</option>
<option value="08" <?php if($minute == 8){echo("selected");}?>>08</option>
<option value="09" <?php if($minute == 9){echo("selected");}?>>09</option>
<option value="10" <?php if($minute == 10){echo("selected");}?>>10</option>
<option value="11" <?php if($minute == 11){echo("selected");}?>>11</option>
<option value="12" <?php if($minute == 12){echo("selected");}?>>12</option>
<option value="13" <?php if($minute == 13){echo("selected");}?>>13</option>
<option value="14" <?php if($minute == 14){echo("selected");}?>>14</option>
<option value="15" <?php if($minute == 15){echo("selected");}?>>15</option>
<option value="16" <?php if($minute == 16){echo("selected");}?>>16</option>
<option value="17" <?php if($minute == 17){echo("selected");}?>>17</option>
<option value="18" <?php if($minute == 18){echo("selected");}?>>18</option>
<option value="19" <?php if($minute == 19){echo("selected");}?>>19</option>
<option value="20" <?php if($minute == 20){echo("selected");}?>>20</option>
<option value="21" <?php if($minute == 21){echo("selected");}?>>21</option>
<option value="22" <?php if($minute == 22){echo("selected");}?>>22</option>
<option value="23" <?php if($minute == 23){echo("selected");}?>>23</option>
<option value="24" <?php if($minute == 24){echo("selected");}?>>24</option>
<option value="25" <?php if($minute == 25){echo("selected");}?>>25</option>
<option value="26" <?php if($minute == 26){echo("selected");}?>>26</option>
<option value="27" <?php if($minute == 27){echo("selected");}?>>27</option>
<option value="28" <?php if($minute == 28){echo("selected");}?>>28</option>
<option value="29" <?php if($minute == 29){echo("selected");}?>>29</option>
<option value="30" <?php if($minute == 30){echo("selected");}?>>30</option>
<option value="31" <?php if($minute == 31){echo("selected");}?>>31</option>
<option value="32" <?php if($minute == 32){echo("selected");}?>>32</option>
<option value="33" <?php if($minute == 33){echo("selected");}?>>33</option>
<option value="34" <?php if($minute == 34){echo("selected");}?>>34</option>
<option value="35" <?php if($minute == 35){echo("selected");}?>>35</option>
<option value="36" <?php if($minute == 36){echo("selected");}?>>36</option>
<option value="37" <?php if($minute == 37){echo("selected");}?>>37</option>
<option value="38" <?php if($minute == 38){echo("selected");}?>>38</option>
<option value="39" <?php if($minute == 39){echo("selected");}?>>39</option>
<option value="40" <?php if($minute == 40){echo("selected");}?>>40</option>
<option value="41" <?php if($minute == 41){echo("selected");}?>>41</option>
<option value="42" <?php if($minute == 42){echo("selected");}?>>42</option>
<option value="43" <?php if($minute == 43){echo("selected");}?>>43</option>
<option value="44" <?php if($minute == 44){echo("selected");}?>>44</option>
<option value="45" <?php if($minute == 45){echo("selected");}?>>45</option>
<option value="46" <?php if($minute == 46){echo("selected");}?>>46</option>
<option value="47" <?php if($minute == 47){echo("selected");}?>>47</option>
<option value="48" <?php if($minute == 48){echo("selected");}?>>48</option>
<option value="49" <?php if($minute == 49){echo("selected");}?>>49</option>
<option value="50" <?php if($minute == 50){echo("selected");}?>>50</option>
<option value="51" <?php if($minute == 51){echo("selected");}?>>51</option>
<option value="52" <?php if($minute == 52){echo("selected");}?>>52</option>
<option value="53" <?php if($minute == 53){echo("selected");}?>>53</option>
<option value="54" <?php if($minute == 54){echo("selected");}?>>54</option>
<option value="55" <?php if($minute == 55){echo("selected");}?>>55</option>
<option value="56" <?php if($minute == 56){echo("selected");}?>>56</option>
<option value="57" <?php if($minute == 57){echo("selected");}?>>57</option>
<option value="58" <?php if($minute == 58){echo("selected");}?>>58</option>
<option value="59" <?php if($minute == 59){echo("selected");}?>>59</option>
</select><br>
Notes<br>
<textarea name="notes"></textarea><br>
<input name="submit" type="submit" value="Create Book"><br>
<a href="index.php">Back</a>
</form>
</body>
</html>
Second part of the codes is where the magic happens. It handles record creation process. Copy the codes below to the beginning of "create.php" file, we will go through them afterwards:
<?php
require "database.php";
if (!empty($_POST)) {
//keep track post values
$title = isset($_POST['title'])?$_POST['title']:'';
$author = isset($_POST['author'])?$_POST['author']:'';
$date_pub = (isset($_POST['year'])?$_POST['year']:'')."-".(isset($_POST['month'])?$_POST['month']:'')."-".(isset($_POST['day'])?$_POST['day']:'')." ".(isset($_POST['hour'])?$_POST['hour']:'').":".(isset($_POST['minute'])?$_POST['minute']:'').":00";
$notes = isset($_POST['notes'])?$_POST['notes']:'';
//insert data
$pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "INSERT INTO Books (title,author,date_pub,notes) VALUES (?,?,?,?)";
$q = $pdo->prepare($sql);
$q->execute(array($title,$author,$date_pub,$notes));
$sql = "SELECT * FROM Books";
foreach($pdo->query($sql) as $row){
if(($title == $row['title']) && ($date_pub == $row['date_pub'])){
$id = $row['id'];
}
}
Database::disconnect();
header("Location: index.php");
}
else{
date_default_timezone_set('Europe/London');
$year = gmdate("Y");
$month = gmdate("m");
$day = gmdate("d");
$hour = gmdate("G");
$minute = gmdate("i");
}
?>
As you see, firstly we check if there is form submit by checking $_POST variable. If so, we check each entries to ensure they are not empty. Additionally for email address entry, we use PHP filter to verify if it is a valid email address. Then if it passes all validation rules, it inserts data to database using Database class. At last it will redirect to "index.php" using PHP header() function. However if there is any validation error, the validation variables will be showed in the form.
If you have followed correctly. Navigate to "create.php" page, and click on the "Create" button, you should be able to see a PHP form with validation errors as below:
Create some records by entering correct customer information. You should be able to see a CRUD grid as below.
You must have noticed a "Read" button for each row. That is what we have added in step 4 of this tutorial. If you click it now, it will lead you to an error page. And that is what we are going to create next step.
Step 6: Creating a "Read" page
Open "create.php" file we created in step 5 of this tutorial. And edit the code that are highlighted as below:
<?php
require "database.php";
if (!empty($_POST)) {
//keep track post values
$title = isset($_POST['title'])?$_POST['title']:'';
$author = isset($_POST['author'])?$_POST['author']:'';
$date_pub = (isset($_POST['year'])?$_POST['year']:'')."-".(isset($_POST['month'])?$_POST['month']:'')."-".(isset($_POST['day'])?$_POST['day']:'')." ".(isset($_POST['hour'])?$_POST['hour']:'').":".(isset($_POST['minute'])?$_POST['minute']:'').":00";
$notes = isset($_POST['notes'])?$_POST['notes']:'';
//insert data
$pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "INSERT INTO Books (title,author,date_pub,notes) VALUES (?,?,?,?)";
$q = $pdo->prepare($sql);
$q->execute(array($title,$author,$date_pub,$notes));
$sql = "SELECT * FROM Books";
foreach($pdo->query($sql) as $row){
if(($title == $row['title']) && ($date_pub == $row['date_pub'])){
$id = $row['id'];
}
}
Database::disconnect();
header("Location: read.php?id=".$id);
}
else{
date_default_timezone_set('Europe/London');
$year = gmdate("Y");
$month = gmdate("m");
$day = gmdate("d");
$hour = gmdate("G");
$minute = gmdate("i");
}
?>
Create a PHP file "read.php"; this file is the "Read" part of CRUD grid. Comparing to "Create" part, this step is pretty straightforward. Copy codes below to the file and we will explain afterwards.
<?php
require "database.php";
$id = null;
if(!empty($_GET['id'])){
$id = $_REQUEST['id'];
}
if (null == $id){
header("Location: index.php");
}
else{
$pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT * FROM Books WHERE id = ?";
$q = $pdo->prepare($sql);
$q->execute(array($id));
$data = $q->fetch(PDO::FETCH_ASSOC);
Database::disconnect();
}
?>
<!DOCTYPE html>
<html>
<head>
</head>
<body>
<?php
if(isset($_SERVER['HTTP_REFERER'])){
$prev_url = $_SERVER['HTTP_REFERER'];
if($prev_url == "http://localhost/CRUD_Books/create.php"){
echo "<p>Book was successfully created. </p>";
}
else if($prev_url == "http://localhost/CRUD_Books/update.php?id=".$id){
echo "<p>Book was successfully updated. </p>";
}
}
?>
<p><b>Title: </b><?php echo $data['title'];?></p>
<p><b>Author: </b><?php echo $data['author'];?></p>
<p><b>Published at: </b><?php echo $data['date_pub'];?> UTC</p>
<p><b>Notes: </b><?php echo $data['notes'];?></p>
<p>
<?php
echo "<a href='update.php?id=".$id."'>Edit</a>";
?>
| <a href="index.php">Back</a></p>
</body>
</html>
Firstly let us look at the beginning part of the PHP codes. What it does is that, it tries to allocate a $_GET['id'] variable. If it is not found, it redirects to "index.php" page. Otherwise, it will read data from database using the "id" field and store data into a PHP variable $data.
Next part, which is the static html part, is pretty simple. It prints out the $data variable.
If you get it working. "read.php" page should look like below:
Step 7: Adding "Update" button and "Delete" button
Similar to creating "Create" and "Read" part of PHP CRUD grid. We will firstly need "Update" and "Delete" buttons for each row of the table. Open "index.php" file and add following highlighted codes:
Now the "index.php" file's code should look like below, the highlighted codes are what we have added (or you can just copy all the codes below):
<!DOCTYPE html>
<html>
<head>
<h1>Listing books</h1>
</head>
<body>
<table>
<thead>
<tr>
<th>Title</th>
<th>Author</th>
<th>Published at</th>
<th>Notes</th>
<th></th>
</tr>
</thead>
<tbody>
<?php
include "database.php";
$pdo = Database::connect();
$sql = "SELECT * FROM Books ORDER BY id ASC";
foreach($pdo->query($sql) as $row){
echo "<tr>";
echo "<td>".$row['title']."</td>";
echo "<td>".$row['author']."</td>";
echo "<td>".$row['date_pub']." UTC</td>";
echo "<td>".$row['notes']."</td>";
echo "<td>";
echo "<a href='read.php?id=".$row['id']."'>Show</a>";
echo " ";
echo "<a href='update.php?id=".$row['id']."'>Edit</a>";
echo " ";
echo "<a href='delete.php?id=".$row['id']."'>Destroy</a>";
echo "</td>";
echo "</tr>";
}
Database::disconnect();
?>
</tbody>
</table>
<p>
<a href="create.php">New Book</a>
</p>
</body>
</html>
Now if you navigate to "index.php page". You should notice "Update" and "Delete" buttons for each row. They are not functional yet. We are going to complete the "Update" page next.
Step 8: Creating a "Update" page
Create a PHP file "update.php"; this is the "Update" part of CRUD grid. It is almost identically to "Create" part, besides the fact that we will not only update the record, but also show the data.
We will go through this file as two parts like we did for "Create" page.
First part of the codes is an html form. This form part is exactly the same as "Create" page. Copy the code below to "update.php" file:
<!DOCTYPE html>
<html>
<head>
<h1>Editing book</h1>
</head>
<body>
<form action="update.php?id=<?php echo $id?>" method="post">
Title<br>
<input type="text" name="title" id="title" value="<?php echo !empty($title)?$title:'';?>"><br>
Author<br>
<input type="text" name="author" id="author" value="<?php echo !empty($author)?$author:'';?>"><br>
Published at<br>
<select name="year">
<option value="2009" <?php if($year == 2009){echo("selected");}?>>2009</option>
<option value="2010" <?php if($year == 2010){echo("selected");}?>>2010</option>
<option value="2011" <?php if($year == 2011){echo("selected");}?>>2011</option>
<option value="2012" <?php if($year == 2012){echo("selected");}?>>2012</option>
<option value="2013" <?php if($year == 2013){echo("selected");}?>>2013</option>
<option value="2014" <?php if($year == 2014){echo("selected");}?>>2014</option>
<option value="2015" <?php if($year == 2015){echo("selected");}?>>2015</option>
<option value="2016" <?php if($year == 2016){echo("selected");}?>>2016</option>
<option value="2017" <?php if($year == 2017){echo("selected");}?>>2017</option>
<option value="2018" <?php if($year == 2018){echo("selected");}?>>2018</option>
<option value="2019" <?php if($year == 2019){echo("selected");}?>>2019</option>
</select>
<select name="month">
<option value="01" <?php if($month == 1){echo("selected");}?>>January</option>
<option value="02" <?php if($month == 2){echo("selected");}?>>February</option>
<option value="03" <?php if($month == 3){echo("selected");}?>>March</option>
<option value="04" <?php if($month == 4){echo("selected");}?>>April</option>
<option value="05" <?php if($month == 5){echo("selected");}?>>May</option>
<option value="06" <?php if($month == 6){echo("selected");}?>>June</option>
<option value="07" <?php if($month == 7){echo("selected");}?>>July</option>
<option value="08" <?php if($month == 8){echo("selected");}?>>August</option>
<option value="09" <?php if($month == 9){echo("selected");}?>>September</option>
<option value="10" <?php if($month == 10){echo("selected");}?>>October</option>
<option value="11" <?php if($month == 11){echo("selected");}?>>November</option>
<option value="12" <?php if($month == 12){echo("selected");}?>>December</option>
</select>
<select name="day">
<option value="01" <?php if($day == 1){echo("selected");}?>>1</option>
<option value="02" <?php if($day == 2){echo("selected");}?>>2</option>
<option value="03" <?php if($day == 3){echo("selected");}?>>3</option>
<option value="04" <?php if($day == 4){echo("selected");}?>>4</option>
<option value="05" <?php if($day == 5){echo("selected");}?>>5</option>
<option value="06" <?php if($day == 6){echo("selected");}?>>6</option>
<option value="07" <?php if($day == 7){echo("selected");}?>>7</option>
<option value="08" <?php if($day == 8){echo("selected");}?>>8</option>
<option value="09" <?php if($day == 9){echo("selected");}?>>9</option>
<option value="10" <?php if($day == 10){echo("selected");}?>>10</option>
<option value="11" <?php if($day == 11){echo("selected");}?>>11</option>
<option value="12" <?php if($day == 12){echo("selected");}?>>12</option>
<option value="13" <?php if($day == 13){echo("selected");}?>>13</option>
<option value="14" <?php if($day == 14){echo("selected");}?>>14</option>
<option value="15" <?php if($day == 15){echo("selected");}?>>15</option>
<option value="16" <?php if($day == 16){echo("selected");}?>>16</option>
<option value="17" <?php if($day == 17){echo("selected");}?>>17</option>
<option value="18" <?php if($day == 18){echo("selected");}?>>18</option>
<option value="19" <?php if($day == 19){echo("selected");}?>>19</option>
<option value="20" <?php if($day == 20){echo("selected");}?>>20</option>
<option value="21" <?php if($day == 21){echo("selected");}?>>21</option>
<option value="22" <?php if($day == 22){echo("selected");}?>>22</option>
<option value="23" <?php if($day == 23){echo("selected");}?>>23</option>
<option value="24" <?php if($day == 24){echo("selected");}?>>24</option>
<option value="25" <?php if($day == 25){echo("selected");}?>>25</option>
<option value="26" <?php if($day == 26){echo("selected");}?>>26</option>
<option value="27" <?php if($day == 27){echo("selected");}?>>27</option>
<option value="28" <?php if($day == 28){echo("selected");}?>>28</option>
<option value="29" <?php if($day == 29){echo("selected");}?>>29</option>
<option value="30" <?php if($day == 30){echo("selected");}?>>30</option>
<option value="31" <?php if($day == 31){echo("selected");}?>>31</option>
</select>
—
<select name="hour">
<option value="00" <?php if($hour == 0){echo("selected");}?>>00</option>
<option value="01" <?php if($hour == 1){echo("selected");}?>>01</option>
<option value="02" <?php if($hour == 2){echo("selected");}?>>02</option>
<option value="03" <?php if($hour == 3){echo("selected");}?>>03</option>
<option value="04" <?php if($hour == 4){echo("selected");}?>>04</option>
<option value="05" <?php if($hour == 5){echo("selected");}?>>05</option>
<option value="06" <?php if($hour == 6){echo("selected");}?>>06</option>
<option value="07" <?php if($hour == 7){echo("selected");}?>>07</option>
<option value="08" <?php if($hour == 8){echo("selected");}?>>08</option>
<option value="09" <?php if($hour == 9){echo("selected");}?>>09</option>
<option value="10" <?php if($hour == 10){echo("selected");}?>>10</option>
<option value="11" <?php if($hour == 11){echo("selected");}?>>11</option>
<option value="12" <?php if($hour == 12){echo("selected");}?>>12</option>
<option value="13" <?php if($hour == 13){echo("selected");}?>>13</option>
<option value="14" <?php if($hour == 14){echo("selected");}?>>14</option>
<option value="15" <?php if($hour == 15){echo("selected");}?>>15</option>
<option value="16" <?php if($hour == 16){echo("selected");}?>>16</option>
<option value="17" <?php if($hour == 17){echo("selected");}?>>17</option>
<option value="18" <?php if($hour == 18){echo("selected");}?>>18</option>
<option value="19" <?php if($hour == 19){echo("selected");}?>>19</option>
<option value="20" <?php if($hour == 20){echo("selected");}?>>20</option>
<option value="21" <?php if($hour == 21){echo("selected");}?>>21</option>
<option value="22" <?php if($hour == 22){echo("selected");}?>>22</option>
<option value="23" <?php if($hour == 23){echo("selected");}?>>23</option>
</select>
:
<select name="minute">
<option value="00" <?php if($minute == 0){echo("selected");}?>>00</option>
<option value="01" <?php if($minute == 1){echo("selected");}?>>01</option>
<option value="02" <?php if($minute == 2){echo("selected");}?>>02</option>
<option value="03" <?php if($minute == 3){echo("selected");}?>>03</option>
<option value="04" <?php if($minute == 4){echo("selected");}?>>04</option>
<option value="05" <?php if($minute == 5){echo("selected");}?>>05</option>
<option value="06" <?php if($minute == 6){echo("selected");}?>>06</option>
<option value="07" <?php if($minute == 7){echo("selected");}?>>07</option>
<option value="08" <?php if($minute == 8){echo("selected");}?>>08</option>
<option value="09" <?php if($minute == 9){echo("selected");}?>>09</option>
<option value="10" <?php if($minute == 10){echo("selected");}?>>10</option>
<option value="11" <?php if($minute == 11){echo("selected");}?>>11</option>
<option value="12" <?php if($minute == 12){echo("selected");}?>>12</option>
<option value="13" <?php if($minute == 13){echo("selected");}?>>13</option>
<option value="14" <?php if($minute == 14){echo("selected");}?>>14</option>
<option value="15" <?php if($minute == 15){echo("selected");}?>>15</option>
<option value="16" <?php if($minute == 16){echo("selected");}?>>16</option>
<option value="17" <?php if($minute == 17){echo("selected");}?>>17</option>
<option value="18" <?php if($minute == 18){echo("selected");}?>>18</option>
<option value="19" <?php if($minute == 19){echo("selected");}?>>19</option>
<option value="20" <?php if($minute == 20){echo("selected");}?>>20</option>
<option value="21" <?php if($minute == 21){echo("selected");}?>>21</option>
<option value="22" <?php if($minute == 22){echo("selected");}?>>22</option>
<option value="23" <?php if($minute == 23){echo("selected");}?>>23</option>
<option value="24" <?php if($minute == 24){echo("selected");}?>>24</option>
<option value="25" <?php if($minute == 25){echo("selected");}?>>25</option>
<option value="26" <?php if($minute == 26){echo("selected");}?>>26</option>
<option value="27" <?php if($minute == 27){echo("selected");}?>>27</option>
<option value="28" <?php if($minute == 28){echo("selected");}?>>28</option>
<option value="29" <?php if($minute == 29){echo("selected");}?>>29</option>
<option value="30" <?php if($minute == 30){echo("selected");}?>>30</option>
<option value="31" <?php if($minute == 31){echo("selected");}?>>31</option>
<option value="32" <?php if($minute == 32){echo("selected");}?>>32</option>
<option value="33" <?php if($minute == 33){echo("selected");}?>>33</option>
<option value="34" <?php if($minute == 34){echo("selected");}?>>34</option>
<option value="35" <?php if($minute == 35){echo("selected");}?>>35</option>
<option value="36" <?php if($minute == 36){echo("selected");}?>>36</option>
<option value="37" <?php if($minute == 37){echo("selected");}?>>37</option>
<option value="38" <?php if($minute == 38){echo("selected");}?>>38</option>
<option value="39" <?php if($minute == 39){echo("selected");}?>>39</option>
<option value="40" <?php if($minute == 40){echo("selected");}?>>40</option>
<option value="41" <?php if($minute == 41){echo("selected");}?>>41</option>
<option value="42" <?php if($minute == 42){echo("selected");}?>>42</option>
<option value="43" <?php if($minute == 43){echo("selected");}?>>43</option>
<option value="44" <?php if($minute == 44){echo("selected");}?>>44</option>
<option value="45" <?php if($minute == 45){echo("selected");}?>>45</option>
<option value="46" <?php if($minute == 46){echo("selected");}?>>46</option>
<option value="47" <?php if($minute == 47){echo("selected");}?>>47</option>
<option value="48" <?php if($minute == 48){echo("selected");}?>>48</option>
<option value="49" <?php if($minute == 49){echo("selected");}?>>49</option>
<option value="50" <?php if($minute == 50){echo("selected");}?>>50</option>
<option value="51" <?php if($minute == 51){echo("selected");}?>>51</option>
<option value="52" <?php if($minute == 52){echo("selected");}?>>52</option>
<option value="53" <?php if($minute == 53){echo("selected");}?>>53</option>
<option value="54" <?php if($minute == 54){echo("selected");}?>>54</option>
<option value="55" <?php if($minute == 55){echo("selected");}?>>55</option>
<option value="56" <?php if($minute == 56){echo("selected");}?>>56</option>
<option value="57" <?php if($minute == 57){echo("selected");}?>>57</option>
<option value="58" <?php if($minute == 58){echo("selected");}?>>58</option>
<option value="59" <?php if($minute == 59){echo("selected");}?>>59</option>
</select><br>
Notes<br>
<textarea name="notes"><?php echo !empty($notes)?$notes:'';?></textarea><br>
<input name="submit" type="submit" value="Update Book"><br>
<a href="index.php">Back</a>
</form>
</body>
</html>
Second part of the codes is where record update process happens. Copy the codes below to the beginning of "update.php" file; we will go through them afterwards:
<?php
require "database.php";
$id = null;
if(!empty($_GET['id'])){
$id = $_REQUEST['id'];
}
if (null == $id){
header("Location: index.php");
}
if (!empty($_POST)) {
//keep track post values
$title = isset($_POST['title'])?$_POST['title']:'';
$author = isset($_POST['author'])?$_POST['author']:'';
$date_pub = (isset($_POST['year'])?$_POST['year']:'')."-".(isset($_POST['month'])?$_POST['month']:'')."-".(isset($_POST['day'])?$_POST['day']:'')." ".(isset($_POST['hour'])?$_POST['hour']:'').":".(isset($_POST['minute'])?$_POST['minute']:'').":00";
$notes = isset($_POST['notes'])?$_POST['notes']:'';
//update data
$pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "UPDATE Books SET title = ?, author = ?, date_pub = ?, notes = ? WHERE id = ?";
$q = $pdo->prepare($sql);
$q->execute(array($title,$author,$date_pub,$notes,$id));
$sql = "SELECT * FROM Books";
foreach($pdo->query($sql) as $row){
if(($title == $row['title']) && ($date_pub == $row['date_pub'])){
$id = $row['id'];
}
}
Database::disconnect();
header("Location: read.php?id=".$id);
}
else{
$pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT * FROM Books WHERE id = ?";
$q = $pdo->prepare($sql);
$q->execute(array($id));
$data = $q->fetch(PDO::FETCH_ASSOC);
$title = $data['title'];
$author = $data['author'];
$date_pub = $data['date_pub'];
date_default_timezone_set('Europe/London');
$timestamp = strtotime($date_pub);
$year = idate('Y', $timestamp);
$month = idate('m', $timestamp);
$day = idate('d', $timestamp);
$hour = idate('H', $timestamp);
$minute = idate('i', $timestamp);
$notes = $data['notes'];
Database::disconnect();
}
?>
As you see, firstly we check if there is form submit by checking $_POST variable. If so, we check each entries to ensure they pass validation rules, after that it updates database using $_POST data. At last it will redirect to "index.php" using PHP header() function. On the flip side, it is a $_GET request, it will retrieve data record from database.
If you have followed correctly. Clicking "Update" button from "index.php" page should lead you to a page similar to below:
Step 9: Creating a "Delete" page
Here comes the final piece. Create a PHP file "delete.php"; Copy codes below to the file and we will explain afterwards.
<?php
require "database.php";
$id = 0;
if (!empty($_GET['id'])){
$id = $_REQUEST['id'];
}
if (!empty($_POST)){
//keep track post values
$id = isset($_POST['id'])?$_POST['id']:'';
//delete data
$pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "DELETE FROM Books WHERE id = ? LIMIT 1";
$q = $pdo->prepare($sql);
$q->execute(array($id));
Database::disconnect();
header("Location: index.php");
}
else{
$pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT * FROM Books WHERE id = ?";
$q = $pdo->prepare($sql);
$q->execute(array($id));
$data = $q->fetch(PDO::FETCH_ASSOC);
Database::disconnect();
}
?>
<!DOCTYPE html>
<html>
<head>
<h1>Destroying book</h1>
</head>
<body>
<p><b>Title: </b><?php echo $data['title'];?></p>
<p><b>Author: </b><?php echo $data['author'];?></p>
<p><b>Published at: </b><?php echo $data['date_pub'];?> UTC</p>
<p><b>Notes: </b><?php echo $data['notes'];?></p>
<form action="delete.php" method="post">
<input type="hidden" name="id" value="<?php echo $id;?>"/>
<input name="delete" type="submit" value="Delete Book"><br>
</form>
<a href="index.php">Back</a>
</body>
</html>
Let us look at the beginning part of the PHP codes. It firstly capture the $id from $_GET request. Once a $_GET request is determined. It shows a confirmation page. If a $_POST request is detected, it indicates that user has click confirmation button "Yes". Then it will proceed to delete the data record and redirects to "index.php" page.
Next part, which is the static html part is pretty simple. It simple store the $_GET['id'] to a hidden field.
If you get it working. "delete.php" page should look like below. If you click "Yes" button, it should direct to index page and the selected record should be deleted.
Final and Source Code
You can download the full source code here.
Return to Internship Note (LoanStreet)
Previous Episode:
Next Episode: Install Ubuntu Desktop
0 comments:
Post a Comment