How to create a blog in PHP and MySQL database - Admin Posts


This is the 4th and last part of a 4-part series on How To Build a Blog with PHP and MySQL database. You can view the other parts here: part 1part 2, and part 3.

So far we have created a public area that lists published posts from a MySQL database posts table. We have also completed a user registration system that handles login for both admin users and normal users. In the backend, the logged in admin user can now create other admin users as well as topics.

In this section, we will be working on blog posts. We will create a page that provides logged in admin users (or authors) with a form to create a new blog post.

ad

We will create two files: posts.php file inside admin folder, and post_functions.php inside admin/includes folder. The posts.php file lists all the posts gotten from the database in a table format while the post_functions.php contains functions that perform operations on posts such as querying them from the database and returning them to the posts.php file.

Place this code in your posts.php file:

<?php  include('../config.php'); ?>
<?php  include(ROOT_PATH . '/admin/includes/admin_functions.php'); ?>
<?php  include(ROOT_PATH . '/admin/includes/post_functions.php'); ?>
<?php include(ROOT_PATH . '/admin/includes/head_section.php'); ?>

<!-- Get all admin posts from DB -->
<?php $posts = getAllPosts(); ?>
        <title>Admin | Manage Posts</title>
</head>
<body>
        <!-- admin navbar -->
        <?php include(ROOT_PATH . '/admin/includes/navbar.php') ?>

        <div class="container content">
                <!-- Left side menu -->
                <?php include(ROOT_PATH . '/admin/includes/menu.php') ?>

                <!-- Display records from DB-->
                <div class="table-div"  style="width: 80%;">
                        <!-- Display notification message -->
                        <?php include(ROOT_PATH . '/includes/messages.php') ?>

                        <?php if (empty($posts)): ?>
                                <h1 style="text-align: center; margin-top: 20px;">No posts in the database.</h1>
                        <?php else: ?>
                                <table class="table">
                                                <thead>
                                                <th>N</th>
                                                <th>Title</th>
                                                <th>Author</th>
                                                <th>Views</th>
                                                <!-- Only Admin can publish/unpublish post -->
                                                <?php if ($_SESSION['user']['role'] == "Admin"): ?>
                                                        <th><small>Publish</small></th>
                                                <?php endif ?>
                                                <th><small>Edit</small></th>
                                                <th><small>Delete</small></th>
                                        </thead>
                                        <tbody>
                                        <?php foreach ($posts as $key => $post): ?>
                                                <tr>
                                                        <td><?php echo $key + 1; ?></td>
                                                        <td><?php echo $post['author']; ?></td>
                                                        <td>
                                                                <a   target="_blank"
                                                                href="<?php echo BASE_URL . 'single_post.php?post-slug=' . $post['slug'] ?>">
                                                                        <?php echo $post['title']; ?>     
                                                                </a>
                                                        </td>
                                                        <td><?php echo $post['views']; ?></td>
                                                        
                                                        <!-- Only Admin can publish/unpublish post -->
                                                        <?php if ($_SESSION['user']['role'] == "Admin" ): ?>
                                                                <td>
                                                                <?php if ($post['published'] == true): ?>
                                                                        <a class="fa fa-check btn unpublish"
                                                                                href="posts.php?unpublish=<?php echo $post['id'] ?>">
                                                                        </a>
                                                                <?php else: ?>
                                                                        <a class="fa fa-times btn publish"
                                                                                href="posts.php?publish=<?php echo $post['id'] ?>">
                                                                        </a>
                                                                <?php endif ?>
                                                                </td>
                                                        <?php endif ?>

                                                        <td>
                                                                <a class="fa fa-pencil btn edit"
                                                                        href="create_post.php?edit-post=<?php echo $post['id'] ?>">
                                                                </a>
                                                        </td>
                                                        <td>
                                                                <a  class="fa fa-trash btn delete" 
                                                                        href="create_post.php?delete-post=<?php echo $post['id'] ?>">
                                                                </a>
                                                        </td>
                                                </tr>
                                        <?php endforeach ?>
                                        </tbody>
                                </table>
                        <?php endif ?>
                </div>
                <!-- // Display records from DB -->
        </div>
</body>
</html>
vli_ad

In the top section, we include the newly created post_functions.php file. Let's open it now and add the code that retrieves posts from the database.

post_functions.php:

<?php 
// Post variables
$post_id = 0;
$isEditingPost = false;
$published = 0;
$title = "";
$post_slug = "";
$body = "";
$featured_image = "";
$post_topic = "";

/* - - - - - - - - - - 
-  Post functions
- - - - - - - - - - -*/
// get all posts from DB
function getAllPosts()
{
        global $conn;
        
        // Admin can view all posts
        // Author can only view their posts
        if ($_SESSION['user']['role'] == "Admin") {
                $sql = "SELECT * FROM posts";
        } elseif ($_SESSION['user']['role'] == "Author") {
                $user_id = $_SESSION['user']['id'];
                $sql = "SELECT * FROM posts WHERE user_id=$user_id";
        }
        $result = mysqli_query($conn, $sql);
        $posts = mysqli_fetch_all($result, MYSQLI_ASSOC);

        $final_posts = array();
        foreach ($posts as $post) {
                $post['author'] = getPostAuthorById($post['user_id']);
                array_push($final_posts, $post);
        }
        return $final_posts;
}
// get the author/username of a post
function getPostAuthorById($user_id)
{
        global $conn;
        $sql = "SELECT username FROM users WHERE id=$user_id";
        $result = mysqli_query($conn, $sql);
        if ($result) {
                // return username
                return mysqli_fetch_assoc($result)['username'];
        } else {
                return null;
        }
}
?>

Now go to http://localhost/complete-blog-php/login.php and log in with the username and password you created in the last tutorial. If you don't login you are going to encounter errors.

vli_ad

 

 

After logging in, go to http://localhost/complete-blog-php/admin/posts.php. 

If all went well, you'll see that the page has 2 posts (the ones we created earlier) displayed in a table.

At this point, we are able to query the database for posts and list them in a tabular format. Let's provide a form for actually creating posts. Create a file create_post.php inside your admin folder and paste this code into it:

complete-blog-php/admin/create_post.php:

<?php  include('../config.php'); ?>
<?php  include(ROOT_PATH . '/admin/includes/admin_functions.php'); ?>
<?php  include(ROOT_PATH . '/admin/includes/post_functions.php'); ?>
<?php include(ROOT_PATH . '/admin/includes/head_section.php'); ?>
<!-- Get all topics -->
<?php $topics = getAllTopics();      ?>
        <title>Admin | Create Post</title>
</head>
<body>
        <!-- admin navbar -->
        <?php include(ROOT_PATH . '/admin/includes/navbar.php') ?>

        <div class="container content">
                <!-- Left side menu -->
                <?php include(ROOT_PATH . '/admin/includes/menu.php') ?>

                <!-- Middle form - to create and edit  -->
                <div class="action create-post-div">
                        <h1 class="page-title">Create/Edit Post</h1>
                        <form method="post" enctype="multipart/form-data" action="<?php echo BASE_URL . 'admin/create_post.php'; ?>" >
                                <!-- validation errors for the form -->
                                <?php include(ROOT_PATH . '/includes/errors.php') ?>

                                <!-- if editing post, the id is required to identify that post -->
                                <?php if ($isEditingPost === true): ?>
                                        <input type="hidden" name="post_id" value="<?php echo $post_id; ?>">
                                <?php endif ?>

                                <input type="text" name="title" value="<?php echo $title; ?>" placeholder="Title">
                                <label style="float: left; margin: 5px auto 5px;">Featured image</label>
                                <input type="file" name="featured_image" >
                                <textarea name="body" id="body" cols="30" rows="10"><?php echo $body; ?></textarea>
                                <select name="topic_id">
                                        <option value="" selected disabled>Choose topic</option>
                                        <?php foreach ($topics as $topic): ?>
                                                <option value="<?php echo $topic['id']; ?>">
                                                        <?php echo $topic['name']; ?>
                                                </option>
                                        <?php endforeach ?>
                                </select>
                                
                                <!-- Only admin users can view publish input field -->
                                <?php if ($_SESSION['user']['role'] == "Admin"): ?>
                                        <!-- display checkbox according to whether post has been published or not -->
                                        <?php if ($published == true): ?>
                                                <label for="publish">
                                                        Publish
                                                        <input type="checkbox" value="1" name="publish" checked="checked">&nbsp;
                                                </label>
                                        <?php else: ?>
                                                <label for="publish">
                                                        Publish
                                                        <input type="checkbox" value="1" name="publish">&nbsp;
                                                </label>
                                        <?php endif ?>
                                <?php endif ?>
                                
                                <!-- if editing post, display the update button instead of create button -->
                                <?php if ($isEditingPost === true): ?> 
                                        <button type="submit" class="btn" name="update_post">UPDATE</button>
                                <?php else: ?>
                                        <button type="submit" class="btn" name="create_post">Save Post</button>
                                <?php endif ?>

                        </form>
                </div>
                <!-- // Middle form - to create and edit -->
        </div>
</body>
</html>

<script>
        CKEDITOR.replace('body');
</script>

Often for a blog post, you will need to write some text in Bold, Italics, Underline, Headings, ordered and unordered lists as well as upload images. To do this you will need a ckeditor to provide you with a textarea rich in these features. This requires us to include the ckeditor plugin script which we already did in the head_section.php file.

ad

To activate this ckeditor on a textarea, 3 things must be done:

  1. We must include the CKEditor source script (as we already did in the head_section.php file)
  2. We must have a textarea and give it an id (say id="body", as we did in this case)
  3. Lastly, we must initialize the textarea with this script (as we did in create_post.php):
<script>
        CKEDITOR.replace('body');
</script>

Open http://localhost/complete-blog-php/admin/create_post.php in the browser and you'll see the transformed textarea.

We are going to use the same form to create and edit posts. What is left now is for us to write the functions responsible for creating, editing, updating and deleting posts. We do that in post_functions.php. Open post_functions.php and add these functions and if statements to it. 

complete-blog-php/admin/includes/post_functions.php:

/* - - - - - - - - - - 
-  Post actions
- - - - - - - - - - -*/
// if user clicks the create post button
if (isset($_POST['create_post'])) { createPost($_POST); }
// if user clicks the Edit post button
if (isset($_GET['edit-post'])) {
        $isEditingPost = true;
        $post_id = $_GET['edit-post'];
        editPost($post_id);
}
// if user clicks the update post button
if (isset($_POST['update_post'])) {
        updatePost($_POST);
}
// if user clicks the Delete post button
if (isset($_GET['delete-post'])) {
        $post_id = $_GET['delete-post'];
        deletePost($post_id);
}

/* - - - - - - - - - - 
-  Post functions
- - - - - - - - - - -*/
function createPost($request_values)
        {
                global $conn, $errors, $title, $featured_image, $topic_id, $body, $published;
                $title = esc($request_values['title']);
                $body = htmlentities(esc($request_values['body']));
                if (isset($request_values['topic_id'])) {
                        $topic_id = esc($request_values['topic_id']);
                }
                if (isset($request_values['publish'])) {
                        $published = esc($request_values['publish']);
                }
                // create slug: if title is "The Storm Is Over", return "the-storm-is-over" as slug
                $post_slug = makeSlug($title);
                // validate form
                if (empty($title)) { array_push($errors, "Post title is required"); }
                if (empty($body)) { array_push($errors, "Post body is required"); }
                if (empty($topic_id)) { array_push($errors, "Post topic is required"); }
                // Get image name
                $featured_image = $_FILES['featured_image']['name'];
                if (empty($featured_image)) { array_push($errors, "Featured image is required"); }
                // image file directory
                $target = "../static/images/" . basename($featured_image);
                if (!move_uploaded_file($_FILES['featured_image']['tmp_name'], $target)) {
                        array_push($errors, "Failed to upload image. Please check file settings for your server");
                }
                // Ensure that no post is saved twice. 
                $post_check_query = "SELECT * FROM posts WHERE slug='$post_slug' LIMIT 1";
                $result = mysqli_query($conn, $post_check_query);

                if (mysqli_num_rows($result) > 0) { // if post exists
                        array_push($errors, "A post already exists with that title.");
                }
                // create post if there are no errors in the form
                if (count($errors) == 0) {
                        $query = "INSERT INTO posts (user_id, title, slug, image, body, published, created_at, updated_at) VALUES(1, '$title', '$post_slug', '$featured_image', '$body', $published, now(), now())";
                        if(mysqli_query($conn, $query)){ // if post created successfully
                                $inserted_post_id = mysqli_insert_id($conn);
                                // create relationship between post and topic
                                $sql = "INSERT INTO post_topic (post_id, topic_id) VALUES($inserted_post_id, $topic_id)";
                                mysqli_query($conn, $sql);

                                $_SESSION['message'] = "Post created successfully";
                                header('location: posts.php');
                                exit(0);
                        }
                }
        }

        /* * * * * * * * * * * * * * * * * * * * *
        * - Takes post id as parameter
        * - Fetches the post from database
        * - sets post fields on form for editing
        * * * * * * * * * * * * * * * * * * * * * */
        function editPost($role_id)
        {
                global $conn, $title, $post_slug, $body, $published, $isEditingPost, $post_id;
                $sql = "SELECT * FROM posts WHERE id=$role_id LIMIT 1";
                $result = mysqli_query($conn, $sql);
                $post = mysqli_fetch_assoc($result);
                // set form values on the form to be updated
                $title = $post['title'];
                $body = $post['body'];
                $published = $post['published'];
        }

        function updatePost($request_values)
        {
                global $conn, $errors, $post_id, $title, $featured_image, $topic_id, $body, $published;

                $title = esc($request_values['title']);
                $body = esc($request_values['body']);
                $post_id = esc($request_values['post_id']);
                if (isset($request_values['topic_id'])) {
                        $topic_id = esc($request_values['topic_id']);
                }
                // create slug: if title is "The Storm Is Over", return "the-storm-is-over" as slug
                $post_slug = makeSlug($title);

                if (empty($title)) { array_push($errors, "Post title is required"); }
                if (empty($body)) { array_push($errors, "Post body is required"); }
                // if new featured image has been provided
                if (isset($_POST['featured_image'])) {
                        // Get image name
                        $featured_image = $_FILES['featured_image']['name'];
                        // image file directory
                        $target = "../static/images/" . basename($featured_image);
                        if (!move_uploaded_file($_FILES['featured_image']['tmp_name'], $target)) {
                                array_push($errors, "Failed to upload image. Please check file settings for your server");
                        }
                }

                // register topic if there are no errors in the form
                if (count($errors) == 0) {
                        $query = "UPDATE posts SET title='$title', slug='$post_slug', views=0, image='$featured_image', body='$body', published=$published, updated_at=now() WHERE id=$post_id";
                        // attach topic to post on post_topic table
                        if(mysqli_query($conn, $query)){ // if post created successfully
                                if (isset($topic_id)) {
                                        $inserted_post_id = mysqli_insert_id($conn);
                                        // create relationship between post and topic
                                        $sql = "INSERT INTO post_topic (post_id, topic_id) VALUES($inserted_post_id, $topic_id)";
                                        mysqli_query($conn, $sql);
                                        $_SESSION['message'] = "Post created successfully";
                                        header('location: posts.php');
                                        exit(0);
                                }
                        }
                        $_SESSION['message'] = "Post updated successfully";
                        header('location: posts.php');
                        exit(0);
                }
        }
        // delete blog post
        function deletePost($post_id)
        {
                global $conn;
                $sql = "DELETE FROM posts WHERE id=$post_id";
                if (mysqli_query($conn, $sql)) {
                        $_SESSION['message'] = "Post successfully deleted";
                        header("location: posts.php");
                        exit(0);
                }
        }

Now we are able to create, read, update and delete posts.

ad

One last thing, let's add code to publish/unpublish post. In the same post_functions.php file, add this code:

// if user clicks the publish post button
if (isset($_GET['publish']) || isset($_GET['unpublish'])) {
        $message = "";
        if (isset($_GET['publish'])) {
                $message = "Post published successfully";
                $post_id = $_GET['publish'];
        } else if (isset($_GET['unpublish'])) {
                $message = "Post successfully unpublished";
                $post_id = $_GET['unpublish'];
        }
        togglePublishPost($post_id, $message);
}
// delete blog post
function togglePublishPost($post_id, $message)
{
        global $conn;
        $sql = "UPDATE posts SET published=!published WHERE id=$post_id";
        
        if (mysqli_query($conn, $sql)) {
                $_SESSION['message'] = $message;
                header("location: posts.php");
                exit(0);
        }
}

This permits the user (Admin user) to publish/unpublish a post. And that brings us to the end of this tutorial. 

Conclusion

Thank you very much for your patience. It gives me immense satisfaction to know that someone actually followed my tutorial to the very end. I hope you learned something that was worth your time. If you have any comments, worries, suggestions, please leave them in the comments section below. You can even just say hi in the comments if you don't have anything to say. I just need to know that you made it this far because that encourages me to do more tutorials like this.

Remember you can always help greatly by sharing with your friends.

Enjoy the rest of your day.

Awa Melvine

ad


Comments