Home » PHP » Creating a complete blog (CRUD) using MySQL and PHP

About Ahmed Khan

Ahmed Khan is the PHP Community Manager at Cloudways, a hosting company that specializes in optimized PHP hosting services. He writes about PHP, MySQL and covers different tips and tricks related to PHP. He is currently active on Cloudways and other different blogs. When he is not writing about PHP, he likes watching The Flash, Game Of Thrones and is a die-hard fan of DC Comics.

Creating a complete blog (CRUD) using MySQL and PHP

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.

Do you want to know how to develop your skillset to become a Web Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. Building web apps with Node.js

2. HTML5 Programming Cookbook

3. CSS Programming Cookbook

4. AngularJS Programming Cookbook

5. jQuery Programming Cookbook

6. Bootstrap Programming Cookbook

 

and many more ....

 

I have read and agree to the terms & conditions

 

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

8 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
ricardo
ricardo
5 years ago

have download code or demo?

Ahmed Khan
Ahmed Khan
5 years ago

Here you can find the post class in git repository : https://github.com/ahmedkhan847/postclass

G Tsol
G Tsol
4 years ago

I can not create the database please help!

Saikat
Saikat
2 years ago
Reply to  G Tsol

CREATE TABLE post (
article_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
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
);

Selena
2 years ago

Thanks for sharing the blog on crud using mysql PHP

Jay Baer
2 years ago

hey, thnx for the amazing post.I just wants to share some knowledge with you. Check out: Node js Vs PHP: What’s Better for Your Project in 2020?

Sri Laxmi
2 years ago

Explained in a good understandable manner.

Oladipupo Isaac Tunji
Oladipupo Isaac Tunji
1 year ago

hi

can i get this kind of comment box and blog post with icons below working?
you can reply to me via mail
thanks and God bless you