Server, Database, Application and Laravel Backups - Get fully protected with SnapShooter
Simple PHP CRUD Source Code with PHP 8 and MySQL
In this tutorial, we will demonstrate how to build a simple CRUD grid using PHP 8, MySQL, and Tailwind CSS.
Create the Database
We will need a database table to work on. Run the command below on MySQL console to create a database table "posts":
CREATE TABLE posts (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(30) NOT NULL,
content TEXT NOT NULL
)
Create the CRUD functions
Our plan is to work on the backend PHP code before creating any view pages.
Create a PHP file functions.php.
Connect to the database
Let's first create a function to connect to the database. This is a commonly shared function, that is used to create a PDO connection:
function connection($host, $database, $username, $password)
{
try {
$conn = new PDO("mysql:host=$host;dbname=$database", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
return $conn;
} catch (PDOException $e) {
echo "Connection failed: ".$e->getMessage();
}
}
List out all the records
To show all the posts, we create a function to list out all the posts records:
function listAll($connection)
{
$sql = "SELECT * FROM posts";
$statement = $connection->prepare($sql);
$statement->execute();
$statement->setFetchMode(PDO::FETCH_ASSOC);
$posts = $statement->fetchAll();
return $posts;
}
Create a record
We create a function to create a post record:
function create($connection, $title, $content)
{
$sql = "INSERT INTO posts (title, content) VALUES (:title, :content)";
$stmt = $connection->prepare($sql);
$stmt->execute(['title' => $title, 'content' => $content]);
}
Update a record
We create a function to update an existing post record:
function update($connection, $id, $title, $content)
{
$sql = "UPDATE posts SET title=:title, content=:content WHERE id=:id";
$stmt = $connection->prepare($sql);
$stmt->execute(['title' => $title, 'content' => $content, 'id' => $id]);
}
Delete a record
We create a function to delete an existing post record:
function delete($connection, $id)
{
$sql = "DELETE FROM posts WHERE id=:id";
$stmt = $connection->prepare($sql);
$stmt->execute(['id' => $id]);
}
Let's consolidate all the functions above, we will get a functions.php with the content below:
function connection($host, $database, $username, $password)
{
try {
$conn = new PDO("mysql:host=$host;dbname=$database", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
return $conn;
} catch (PDOException $e) {
echo "Connection failed: ".$e->getMessage();
}
}
function listAll($connection)
{
$sql = "SELECT * FROM posts";
$statement = $connection->prepare($sql);
$statement->execute();
$statement->setFetchMode(PDO::FETCH_ASSOC);
$posts = $statement->fetchAll();
return $posts;
}
function create($connection, $title, $content)
{
$sql = "INSERT INTO posts (title, content) VALUES (:title, :content)";
$stmt = $connection->prepare($sql);
$stmt->execute(['title' => $title, 'content' => $content]);
}
function read($connection, $id)
{
$sql = "SELECT * FROM posts WHERE id = :id";
$stmt = $connection->prepare($sql);
$stmt->execute(['id' => $id]);
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$post = $stmt->fetch();
return $post;
}
function update($connection, $id, $title, $content)
{
$sql = "UPDATE posts SET title=:title, content=:content WHERE id=:id";
$stmt = $connection->prepare($sql);
$stmt->execute(['title' => $title, 'content' => $content, 'id' => $id]);
}
function delete($connection, $id)
{
$sql = "DELETE FROM posts WHERE id=:id";
$stmt = $connection->prepare($sql);
$stmt->execute(['id' => $id]);
}
Implement Create page
Time to start making the view page. The first page we are going to create is the page for creating a post.
We will be using Tailwind CSS for style.
Copy the content below to create.php:
<?php
include 'functions.php';
if ($_POST) {
$connection = connection('localhost', 'crud', 'root', 'root');
create($connection, $_POST['title'], $_POST['content']);
$msg = 'Post created successfully';
}
?>
<!doctype html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<script src="https://cdn.tailwindcss.com"></script>
</head>
<body class="max-w-4xl mx-auto space-y-10 py-10">
<h1 class="text-3xl font-bold underline">Create </h1>
<?php if (isset($msg)): ?>
<div class="bg-green-100 border border-green-400 text-green-700 px-4 py-3 rounded relative" role="alert">
<strong class="font-bold">Success!</strong>
<span class="block sm:inline"><?php echo $msg; ?></span>
</div>
<?php endif; ?>
<form action="create.php" method="post">
<div class="mb-4">
<label class="block text-gray-700 text-sm font-bold mb-2" for="title">
Title
</label>
<input class="shadow appearance-none border rounded w-full py-2 px-3 text-gray-700 leading-tight focus:outline-none focus:shadow-outline"
id="title" name="title" type="text" placeholder="Title">
</div>
<div class="mb-6">
<label class="block text-gray-700 text-sm font-bold mb-2" for="content">
Content
</label>
<textarea
class="shadow appearance-none border rounded w-full py-2 px-3 text-gray-700 leading-tight focus:outline-none focus:shadow-outline"
id="content" name="content" type="text" placeholder="Content"></textarea>
</div>
<div class="flex items-center justify-between">
<button class="bg-blue-500 hover:bg-blue-700 text-white font-bold py-2 px-4 rounded focus:outline-none focus:shadow-outline"
type="submit">
Submit
</button>
<a href="index.php"
class="bg-gray-500 hover:bg-gray-700 text-white font-bold py-2 px-4 rounded focus:outline-none focus:shadow-outline"
type="submit">
Cancel
</a>
</div>
</form>
</body>
</html>
Make sure you change the credentials according to when calling $connection = connection('localhost', 'crud', 'root', 'root'); method on top of the page.
Start the webserver php -S localhost:8888, and navigate to URL http://localhost:8888/create.php on your browser. You should be able to see a good-looking page for post creation:
Implement Index page
Now we have successfully implemented the page to create a post, time to list out the posts we have created.
Copy the content below to index.php:
<?php
include 'functions.php';
$connection = connection('localhost', 'crud', 'root', 'root');
$posts = listAll($connection);
?>
<!doctype html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<script src="https://cdn.tailwindcss.com"></script>
</head>
<body class="max-w-4xl mx-auto space-y-10 py-10">
<div class="flex items-center justify-between">
<h1 class="text-3xl font-bold underline">List </h1>
<a href="create.php"
class="bg-blue-500 hover:bg-blue-700 text-white font-bold py-2 px-4 rounded focus:outline-none focus:shadow-outline">Create</a>
</div>
<table class="table-auto w-full">
<thead>
<tr>
<th class="px-4 py-2 text-left">Title</th>
<th class="px-4 py-2 text-left">Content</th>
<th class="px-4 py-2 text-left">Actions</th>
</tr>
</thead>
<tbody>
<?php foreach ($posts as $post): ?>
<tr>
<td class="border p-4"><?php echo $post['title']; ?></td>
<td class="border p-4"><?php echo $post['content']; ?></td>
<td class="border p-4">
<a href="update.php?id=<?php echo $post['id']; ?>"
class="bg-blue-500 hover:bg-blue-700 text-white font-bold py-2 px-4 rounded focus:outline-none focus:shadow-outline">Edit</a>
<a href="delete.php?id=<?php echo $post['id']; ?>"
onclick="return confirm('Are you sure to delete?');"
class="bg-red-500 hover:bg-red-700 text-white font-bold py-2 px-4 rounded focus:outline-none focus:shadow-outline">Delete</a>
</td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</body>
</html>
Again, make sure you update the credentials on top of the page $connection = connection('localhost', 'crud', 'root', 'root');.
Now navigate to http://localhost:8888/index.php, you should be able to see a listing page:
Implement Update page
If you click the Edit button on the index page, it will give you a 404 page. We are going to fix that on this step.
Copy the content below to update.php:
<?php
include 'functions.php';
$connection = connection('localhost', 'crud', 'root', 'root');
if ($_POST) {
update($connection, $_GET['id'], $_POST['title'], $_POST['content']);
$msg = 'Post updated successfully';
}
$post = read($connection, $_GET['id']);
?>
<!doctype html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<script src="https://cdn.tailwindcss.com"></script>
</head>
<body class="max-w-4xl mx-auto space-y-10 py-10">
<h1 class="text-3xl font-bold underline">Update </h1>
<?php if (isset($msg)): ?>
<div class="bg-green-100 border border-green-400 text-green-700 px-4 py-3 rounded relative" role="alert">
<strong class="font-bold">Success!</strong>
<span class="block sm:inline"><?php echo $msg; ?></span>
</div>
<?php endif; ?>
<!-- a update form to update a post -->
<form action="update.php?id=<?= $_GET['id'] ?>" method="post">
<div class="mb-4">
<label class="block text-gray-700 text-sm font-bold mb-2" for="title">
Title
</label>
<input class="shadow appearance-none border rounded w-full py-2 px-3 text-gray-700 leading-tight focus:outline-none focus:shadow-outline"
value="<?= $post['title'] ?>"
id="title" name="title" type="text" placeholder="Title">
</div>
<div class="mb-6">
<label class="block text-gray-700 text-sm font-bold mb-2" for="content">
Content
</label>
<textarea
class="shadow appearance-none border rounded w-full py-2 px-3 text-gray-700 leading-tight focus:outline-none focus:shadow-outline"
id="content" name="content" type="text" placeholder="Content"><?= $post['content'] ?></textarea>
</div>
<div class="flex items-center justify-between">
<button class="bg-blue-500 hover:bg-blue-700 text-white font-bold py-2 px-4 rounded focus:outline-none focus:shadow-outline"
type="submit">
Submit
</button>
<a href="index.php"
class="bg-gray-500 hover:bg-gray-700 text-white font-bold py-2 px-4 rounded focus:outline-none focus:shadow-outline"
type="submit">
Cancel
</a>
</div>
</form>
</body>
</html>
Update the credentials on $connection = connection('localhost', 'crud', 'root', 'root');. Now the Update page should work too:
Implement Delete page
The last piece for us to fix is the Delete button.
Copy the content below to delete.php:
<?php
include 'functions.php';
if ($_GET['id']) {
$connection = connection('localhost', 'crud', 'root', 'root');
delete($connection, $_GET['id']);
header('Location: index.php');
}
?>
Update the credentials as usual. Now the Delete button on the listing page should be working too.
The end
You have completed a very simple CRUD grid using PHP 8 and Tailwind CSS.