In this chapter, you will be combining everything you have learned into one example website. This website will allow you to create, read, update, and delete (otherwise known as CRUD). A basic CRUD website is a standard way to manage information within a business or organization. Almost every application out there can be broken down into CRUD if you think about it. Facebook allows you to create posts, read posts, update them or your profile, and delete information. This functionality is the basic interaction that most websites are looking for, but your imagination is the limit to where you can go with it.
This chapter will cover the following:
The PHP CRUD GET method and example functions: deleteBook, showEditBook, showAddBook, and showBooks
The PHP CRUD POST method for examples bookToUpdate and bookToAdd
For this example, you will create the basic CRUD for you to review and add to. This CRUD uses both POST and GET methods as well as MySQL PDO parameter binding. This is a great first step towards developing a more dynamic and advanced application.
Let’s get right into this with home.php within the chapter12 link.
<?php
$title = "Home";
$thisDir = 12;
?>
These first lines declare some global variables.
<!DOCTYPE html>
<html lang="en">
<head>
<link href="../bootstrap/css/bootstrap.min.css" rel="stylesheet">
</head>
<div class="container">
<header class="d-flex flex-wrap justify-content-center py-3 mb-4 border-bottom">
<a href="/" class="d-flex align-items-center mb-3 mb-md-0 me-md-auto text-dark text-decoration-none">
<svg class="bi me-2" width="40" height="32"><use xlink:href="#bootstrap"></use></svg>
<span class="fs-4"><?= $title ?></span>
</a>
<ul class="nav nav-pills">
<li class="nav-item"><a href="home.php" class="nav-link" aria-current="page">Home</a></li>
<li class="nav-item"><a href="home.php?q=add" class="nav-link" aria-current="page">Add</a></li>
</ul>
</header>
<div>
</div>
This section creates the header and navigation for the app. For now, these links are static, but they can be made dynamic through reading menu items from a database, for example.
<?php
try {
echo '<br />';
echo 'Current PHP version: ' . phpversion();
echo '<br />';
$host = 'mysql-db';
$dbname = 'beginningPHP';
$user = 'user';
$pass = 'pass';
$dsn = "mysql:host=$host;dbname=$dbname;charset=utf8";
$conn = new PDO($dsn, $user, $pass);
echo 'Database connected successfully';
echo '<br />';
} catch (Throwable $t) {
echo 'Error: ' . $t->getMessage();
echo '<br />';
}
This is your basic database connection block. Here you attempt to connect with your credentials and return an error if there are any issues. You will use the $conn variable next in several functions. You will need to use global $conn within those functions because this variable exists outside the scope of the new functions.
function deleteBook($theBook) {
global $conn;
$sql = "delete FROM `books` WHERE `id`=$theBook";
$result = $conn->query($sql);
echo "Book Deleted<br />";
}
This function deleteBook takes the passed-in variable $theBook and targets the database item through a specific database query. The function then returns “Book Deleted.” This function can be improved in multiple ways:
Variable sanitization to protect against SQL injection attacks
Verification that the item to delete exits
Checking for MySQL errors and showing them
function showEditBook($theBook) {
global $conn;
$sql = "SELECT * FROM `books` WHERE `id`=$theBook";
$result = $conn->query($sql);
foreach($result as $row) {
$addForm ='<form action="home.php" method="post"><table>';
$addForm .= '<tr><td>Title</td><td><input type="text" name="title" value="'.$row['title'].'"></td></tr>';
$addForm .= '<tr><td>Author</td><td><input type="text" name="author" value="'.$row['author'].'"></td></tr>';
$addForm .= '<tr><td>Category</td><td><input type="text" name="category" value="'.$row['category'].'"></td></tr>';
$addForm .= '<tr><td>ISBN</td><td><input type="text" name="isbn" value="'.$row['isbn'].'"></td></tr>';
$addForm .= '<tr><td></td><td><input type="submit" name="submit"></td></tr>';
$addForm .= '<input type="hidden" name="bookToUpdate" value="'.$row['id'].'">';
$addForm .= '</table></form>';
echo $addForm;
}
}
The function showEditBook shows the edit book form given the book id ($theBook). With this form, you then submit it back to home.php via POST. With this form, you can add validation to ensure that values are properly filled out and able to be added into the database. The hidden field is there as an indicator to home.php as to how to handle the form submission. You will get to that later when you update the book in a function.
function showAddBook() {
$addForm ='<form action="home.php" method="post"><table>';
$addForm .= '<tr><td>Title</td><td><input type="text" name="title"></td></tr>';
$addForm .= '<tr><td>Author</td><td><input type="text" name="author"></td></tr>';
$addForm .= '<tr><td>Category</td><td><input type="text" name="category"></td></tr>';
$addForm .= '<tr><td>ISBN</td><td><input type="text" name="isbn"></td></tr>';
$addForm .= '<tr><td></td><td><input type="submit" name="submit"></td></tr>';
$addForm .= '<input type="hidden" name="bookToAdd" value="true">';
$addForm .= '</table></form>';
echo $addForm;
}
The function showAddBook shows the Add a book form. Again, here you use a hidden field to notify home.php via POST what action you want to take.
function showBooks() {
global $conn;
$sql = "SELECT * FROM `books` WHERE `id`";
$result = $conn->query($sql);
if ($result !== false) {
$rowCount = $result->rowCount();
echo "Number of Books: $rowCount <br />";
}
foreach($result as $row) {
echo $row['id'].' - '. $row['title'] .' - '. $row['author'] .' - '. $row['category'] .' - '. $row['isbn'] .' [ <a href="home.php?q=edit&book='.$row['id'].'"> Edit</a> <a href="home.php?q=delete&book='.$row['id'].'"> Delete</a> ]<br />';
}
}
The function showBooks is the default display of the page. It shows all of the books in the database with links to edit and delete.
if (isset($_GET['q'])) {
if ($_GET['q'] == 'add') {
echo "Adding Book<br />";
showAddBook();
}
if ($_GET['q'] == 'edit') {
$theBook = $_GET['book'];
echo "Editing Book<br />";
showEditBook($theBook);
}
if ($_GET['q'] == 'delete') {
$theBook = $_GET['book'];
echo "Deleting Book<br />";
deleteBook($theBook);
}
}
Above is the logic that you use to determine what action to take via GET. Remember that GET variables are the ones used within the URL. You use q as the variable you assign to the action (add, edit, delete) in your URL.
if (isset($_POST['bookToUpdate'])) {
global $conn;
$sql = "update books set title=?, author=?, category=?, isbn=? where id=?";
if ($stmt = $conn->prepare($sql)) {
$stmt->bindParam(1,$_POST['title']);
$stmt->bindParam(2,$_POST['author']);
$stmt->bindParam(3,$_POST['category']);
$stmt->bindParam(4,$_POST['isbn']);
$stmt->bindParam(5,$_POST['bookToUpdate']);
if($stmt->execute()) {
echo "Book ". $_POST['title'] ."added";
}
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
echo "</br>Stmt error: ".$stmt->error();
}
}
The above if statement checks to see if you are calling for the variable bookToUpdate. If this variable is set, then you attempt to update a book. You use the PDO, as explained in Chapter 10, to prepare a statement to ensure that you are protecting against SQL injections and to specify variables. Once $stmt is executed, you return the book title and “added;” otherwise, you return the error. This can be improved by
Sanitizing POST data
Verifying that the item in the DB is available to update
if (isset($_POST['bookToAdd'])) {
global $conn;
$sql = "insert into books (title, author, category, isbn) VALUES (?,?,?,?)";
if ($stmt = $conn->prepare($sql)) {
$stmt->bindParam(1,$_POST['title']);
$stmt->bindParam(2,$_POST['author']);
$stmt->bindParam(3,$_POST['category']);
$stmt->bindParam(4,$_POST['isbn']);
if($stmt->execute()) {
echo "New Book added";
}
}
}
This if statement checks for the POST variable bookToAdd. If it is found, then the SQL query is created and executed. This can be improved by
Sanitizing POST data
Verifying that the item is not already in the DB
All values are filled out
showBooks();
This is the default view for this page, a list of books available:
?>
</div>
With the improvements listed above, try turning this into an API that returns JSON data. Instead of returning HTML, the output should look something like this pseudo-code:
$sql = "SELECT * FROM `books` WHERE `id`";
$result = $conn->query($sql);
if ($result !== false) {
$rowCount = $result->rowCount();
$output[] = "Number of Books: $rowCount";
}
foreach($result as $row) {
$output[] = "title:". $row['title'];
$output[] = "author:". $row['author'];
$output[] = "category:". $row['category'];
$output[] = "isbn:". $row['isbn'];
$output = json_encode($output);
Return $output;
}
Summary
In this chapter, you combined everything you have learned so far and build one example website. You learned how to build this website to create, read, update, and delete (otherwise known as CRUD) and use it with both POST and GET methods as well as the MySQL PDO parameter binding.
In the next chapter, you will learn about frameworks, which use a lot of best practices and design patterns so as to allow developers to quickly use them to solve problems.