TailTemplate Build stunning websites faster with our pre-designed Tailwind CSS templates

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.