Creating a complete blog (CRUD) using MySQL and PHP

Ahmed KhanJuly 13th, 2016Last Updated: July 13th, 2016

8 574 4 minutes read

Blogs have become an essential component of corporate websites. This is why every major and minor CMS now offer an integrated or independent blog as a default option.

For many new developers, the functionality of the blogs remains a mystery. Blogs have become so tightly integrated with the overall structure of the CMS (especially WP, Joomla and Drupal) that it is not easy to discern the working of the blog from the rest of the CMS.

This two-part tutorial is intended to show new developers the architecture of a blog and how this architecture functions. The first part of the tutorial will deal with a post class that performs CRUD functions for the data of the blog. The second part of the tutorial will demonstrate the usage of the post class at a page that will show, create and delete blog posts using the capabilities of the class.

Let’s get started with the first part by creating the database.

Step 1: Create the Database

Open MySQL Manager and create a new database. Name it blog for now. In the newly created database, create a new table with the following schema:

CREATE TABLE `post` (
  `article_id` int(11) NOT NULL AUTO_INCREMENT,
  `article_name` varchar(255) NOT NULL,
  `article_content` text NOT NULL,
  `img` varchar(50) NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

This schema is easy to understand. First item is the post id that auto increments. Next are the name, body, image and the date of publication.

Now that the database and the appropriate table have been created, let’s create a class that will handle database connectivity.

Step 2: Connecting to the Database

I will use MySQLi to connect to the database and perform database queries. Following is the code for connecting to the database.

class DbConnection
{
    protected $conn = null;
    public function OpenCon()
    {
        $this->conn = new mysqli(servername, username, password, dbname) or die($conn->connect_error);
        return $this->conn;
    }
    public function CloseCon()
    {
        $this->conn->close();
    }
}

This class will be used to open and handle the connectivity. Next up is the actual class that will perform CRUD operations.

Step 3: Class Post: Create a Post

Let’s first create a new class Post and a constructor that will handle database connectivity:

<?php
include "DbConnection.php";


class Post
{

    protected $db      = null;
   

    public function __construct()
    {

        $this->db      = new DbConnection();
        

    }

Now, let’s create a new function which will handle new post request and save it to the database.

 public function insertpost($a_name, $a_content, $imgname)
    {

        $con     = $this->db->OpenCon();
        $title   = $con->real_escape_string($a_name);
        $content = $con->real_escape_string($a_content);
        $img     = $con->real_escape_string($imgname);
        $query   = $con->prepare("INSERT INTO post(article_name, article_content, img) VALUES(?, ?, ?)");
        $query->bind_param("sss", $title, $content, $img);
        $result = $query->execute();
        if (!$result) {

            $error = $con->error;

            $this->db->CloseCon();
            return $error;
        }
        $result = true;
        return $result;
    }

The above function saves the post in the database and return true if there are no errors.

Step 4: Class Post: Getting a Post

Now let’s create a function which will get the post in order to view it.

public function getarticle($articleid)
    {
        $con = $this->db->OpenCon();

        $stmt = "SELECT article_name,article_content,img,date from post WHERE article_id = '$articleid'";

        $result = $con->query($stmt);

        if ($result->num_rows == 1) {
            $sql = $result;
        } else {
            $sql = "No article";
        }

        $this->db->CloseCon();

        return $sql;

    }

The above function gets the id of the post and will return the appropriate post. Now let’s create a function that will update a post.

Step 5: Class Post: Updating a Post

In a blog, editing and updating a post is a common activity. The following function handles all such requests:

 public function updatearticle($a_id, $a_content, $a_name, $a_image)
    {

        $con     = $this->db->OpenCon();
        $title   = $con->real_escape_string($a_name);
        $content = $con->real_escape_string($a_content);
        $img     = $con->real_escape_string($imgname);
        $query   = $con->prepare("UPDATE post SET article_name = ? , article_content = ?, img = ? WHERE article_id = ?");
        $query->bind_param("sssi", $title, $content, $img, $a_id);
        $result = $query->execute();
        if (!$result) {
            $error = $con->error;

            $this->db->CloseCon();
            return $error;
        }
        $result = true;
        return $result;

    }

Now we have created, updated and read our post let’s create a function which will delete it.

Step 6: Class Post: Delete a Post

Delete Post function takes a single parameter, post id and deletes the associated post:

public function deletearticle($id)
    {

        $con    = $this->db->OpenCon();
        $sql    = "DELETE FROM post WHERE article_id = '$id'";
        $result = $con->query($sql);

        if (!$result) {

            $error = $con->error;

            $this->db->CloseCon();
            return $error;
        }
        $result = true;
        return $result;
    }

To Recap:

Here is the complete structure of the Post class:

<?php
include "DbConnection.php";

class Post
{

    protected $db = null;

    public function __construct()
    {

        $this->db = new DbConnection();

    }
    public function insertpost($a_name, $a_content, $imgname)
    {

        $con     = $this->db->OpenCon();
        $title   = $con->real_escape_string($a_name);
        $content = $con->real_escape_string($a_content);
        $img     = $con->real_escape_string($imgname);
        $query   = $con->prepare("INSERT INTO post(article_name, article_content, img) VALUES(?, ?, ?)");
        $query->bind_param("sss", $title, $content, $img);
        $result = $query->execute();
        if (!$result) {

            $error = $con->error;

            $this->db->CloseCon();
            return $error;
        }
        $result = true;
        return $result;
    }

    public function getarticle($articleid)
    {
        $con = $this->db->OpenCon();

        $stmt = "SELECT article_name,article_content,img,date from post WHERE article_id = '$articleid'";

        $result = $con->query($stmt);

        if ($result->num_rows == 1) {
            $sql = $result;
        } else {
            $sql = "No article";
        }

        $this->db->CloseCon();

        return $sql;

    }

    public function deletearticle($id)
    {

        $con    = $this->db->OpenCon();
        $sql    = "DELETE FROM post WHERE article_id = '$id'";
        $result = $con->query($sql);

        if (!$result) {

            $error = $con->error;

            $this->db->CloseCon();
            return $error;
        }
        $result = true;
        return $result;
    }

    public function updatearticle($a_id, $a_content, $a_name, $a_image)
    {

        $con     = $this->db->OpenCon();
        $title   = $con->real_escape_string($a_name);
        $content = $con->real_escape_string($a_content);
        $img     = $con->real_escape_string($imgname);
        $query   = $con->prepare("UPDATE post SET article_name = ? , article_content = ?, img = ? WHERE article_id = ?");
        $query->bind_param("sssi", $title, $content, $img, $a_id);
        $result = $query->execute();
        if (!$result) {
            $error = $con->error;

            $this->db->CloseCon();
            return $error;
        }
        $result = true;
        return $result;

    }
}

Conclusion:

In this part of the tutorial, I have introduced the code of the class that handles all the CRUD requests. In the next part, I will use this class to Insert, read, update and delete posts. Till then goodbye.