Blog System using Laravel & MySQL

Summary

The goal of this article is to make a complete documentation as to how we can make a simple blog system on web, from scratch. As you follow along the discussion, you can expect to read about these certain feature specifications: user management, profile customization, blog posting & editing, like & comment functions.

Technologies

As per requirement, these are the following technologies that should be used to make the entire application.

• Laraval Framework
• PHP
• Javascript
• HTML & CSS
• MySQL

I. Function List

This section defines the specific elaborate actions that the system will do through modular operations, as we call it, functions. For our Blog System, the function list will be divided into two, the user functions & the administrator functions.

A. User Specifications

Contains the the user management & blogging modules, such as posting & editing blogs, liking and commenting on them; on the end of a registered user.

##Login Function

A function that lets a registered user log in to their registered account.
Method: POST
Display:

• Username - Text Field | String

• Password - Password Text Field | String

• Register - Button

• Back - Button

• Login Button (on-click)

  • Username (required field) > Error: “Please enter your username.”

    • Username should exist in database.
      • Error: “Username or password is incorrect.”

  • Password (required field) > Error: “Please enter your password.”

    • If username exists, password should match in the database.
      • Error: “Password must be at least 8 characters.”

  • If all conditions are met, session data will be stored, then redirects to “Home” page.

• Register Button (on-click)

  • Redirects to "Registration" component/page.

Logout Function

A function that lets the user log out from session as a registered user.
Method: NONE
Display:
Logout - Button

• Logout Button (on-click)
  • Delete session from browser then redirect to 'Home' page.

Register Function

A function that allows user to register to use blogging/comment features.
Method: POST
Display:

• Username - Text Field | String

• Email address - Text Field | String

• Password - Password Text Field | String

• Confirm Password - Password Text Field

• Register - Button

• Back - Button

• Register Button (on-click)

  • Username (required field) > Error: “Please enter your username.”

    • Must be alphanumeric only.
      • Error: “Please enter letter & number characters only.”
    • Does not exceed more than 20 characters.
      • Error: “Username should not exceed 20 characters.”
    • Username should not exist in database.
      • Error: “Username already exists.”*

  • Password (required field) > Error: “Please enter your password.”

    • Must contain letters and numbers.
      • Error: “Password must contain letters & numbers.”
    • Must be at least 8 characters.
      • Error: “Password must be at least 8 characters.”

  • Confirm Password (required field) > Error: “Please rewrite your password.”

    • Must match the "allowed" password.
      • Error: “Does not match set password.”

  • Email Address (required field) > Error: “Please enter your email address.”

    • Must follow an email format.
      • Error: “Please enter an appropriate email address.”
    • Email should not exist in database.
      • Error: “Email address already exists.”

  • If all conditions are met, inserts all data in database (password will be converted to MD5), then redirects to “Home” page.

    • Inserts in User Table:
      • USER.username = varchar(50)
      • USER.password = varchar(100) [MD5-hashed]
      • USER.email = varchar(100)
      • USER.status = tinyInt(1)
      • USER.isAdmin = tinyInt(1)
      • USER.url = varchar(50)

• Back Button (on-click)

  • Redirects to "Login" component/page.

Edit Profile

A function that allows user to edit his/her profile information.
Method: PUT
Display:

• New Password - Password Text Field | String

• Confirm New Password - Password Text Field

• New Email Address - Text Field | String

• Blog Title - Text Field | String (Pre-filled if already set)

• Blog Description - Text Field | String (Pre-filled if already set)

• Blog URL - Text Field | String (Username by default | Pre-filled)

• Header Image - Image | Blob

• Current Password - Password Text Field | String

• Change - Button

• Change Button (on-click)

  • User must be logged-in. (Session)
    • Otherwise, gets redirected to Login page.
  • New Password
    • Must contain letters and numbers.
      • Error: “Password must contain letters & numbers.”
    • Must be at least 8 characters.
      • Error: “Password must be at least 8 characters.”
  • Confirm Password (required field) > Error: “Please rewrite your password.”
    • Must match the "allowed" password.
      • Error: “Does not match set password.”
  • New Email Address (required field) > Error: “Please enter your email address.”
    • Must follow an email format.
      • Error: “Please enter an appropriate email address.”
  • Blog Title
    • Should not be more than 50 characters.
      • Error: "Title can't exceed more than 50 characters."
  • Blog Description
    • Should not be more than 200 characters.
      • Error: "Title can't exceed more than 200 characters."
  • Blog URL
    • Must be alphanumeric only.
      • Error: Please enter letter & number characters only.”
  • At least one field and the current password should be filled.
    • Error: "Please fill up at least one field you want to edit."
  • Checks if current password matches from the database then save changes in database.
    • Error: "Password incorrect. Changes can't be done."
    • Updates in User Table:
      • USER.password = varchar(100) [MD5-hashed]
      • USER.email = varchar(100)
      • USER.blogTitle = varchar(50)
      • USER.blogDesc = varchar(200)
      • USER.headerImage = blob
      • USER.url = varchar(50)

Blog Posts (Create, Update, Delete)

Contains the functions for creating, editing and deleting posts on the end of a standard user.

Create Function

A function that lets a registered user create his own blog post.
Method: POST
Display:

• Title - Text Field | String

• Body - Text Area | String

• Create - Button

• Create Button (on-click)

  • User must be logged-in. (Session)
    • Otherwise, gets redirected to Login page.
  • Title (required field) > Error: “Please input your blog title.”
    • Title should less than 101 characters only.
      • Error: "Your title should not exceed more than 100 characters."
  • Body (required field) > Error: “Blog content should not be empty.”
  • If all conditions are met, saves blog post in database.
    • Inserts in Blog Post Table:
      • BLOG_POST.title = varchar(100)
      • BLOG_POST.body = varchar(MAX)

Edit Function

A function that lets a registered user edit the blog post he/she created.
Method: PUT
Display:

• Title - Text Field | String (Pre-filled)

• Body - Text Area | String (Pre-filled)

• Change - Button

• Change Button (on-click)

  • User must be logged-in. (Session)
    • Otherwise, gets redirected to Login page.
  • Title (required field) > Error: “Blog title should not be empty.”
    • Title should less than 51 characters only.
      • Error: "Your title should not exceed more than 50 characters."
  • Body (required field) > Error: “Blog content should not be empty.”
  • Current Title or Body string should not be the same when compared to the edited strings.
    • Error: There were no changes in your blog post, we can't update it.
  • If all conditions are met, updates blog post in database.
    • Updates in Blog Post Table:
      • BLOG_POST.title = varchar(100)
      • BLOG_POST.body = varchar(MAX)

Delete Function

A function that lets registered user delete the blog post he/she created.
Method: DELETE
Display:

• Title - Text | String (To display what blog post is to be deleted.)

• Delete - Button

• Delete Button (on-click)

  • User must be logged-in. (Session)
    • Otherwise, gets redirected to Login page.
  • Prompts a confirmation dialog, "Are you sure you want to delete this post?" with the blog post Title.
    • Yes: dialog dismisses, then deletes the blog post from database.
      • Deletes row in Blog Post Table equal to the BLOG_POST.blogPostID.
    • No: dialog dismisses.

Blog Posts (Read)

Contains functions related to displaying data and interacting with blog posts on the end of a standard user.

User Blogs (List)

Shows a list of the user's blogs, with specific functions dedicated to the author.
Method: GET
Display: (List of Items)

• Title - Text | BLOG_POST.title

• Like Count - Text | LIKE.likeID

• Date Posted - Text | BLOG_POST.datePosted

• Date Modified - Text | BLOG_POST.dateModified

• Edit - Button

• Includes: Search & Delete Functions

• Edit Button (on-click)

  • Redirects to Edit blog component/page.

• Item (on-click)

  • Redirects to the chosen blog post.

• Conditions

  • If the number of blog posts are more than 10, utilize pagination display.
    • Otherwise, directly display blog posts.

Home Blogs (List)

Shows a list of everyone's chronologically ordered blogs.
Method: GET
Display: (List of Items)

• Title - Text | BLOG_POST.title

• Blog Author - Text | BLOG_POST.userID

• Date Posted - Text | BLOG_POST.datePosted

• Like Count - Text | LIKE.likeID count

• Includes: Search Function

• Item (on-click)

  • Redirects to the chosen blog post.

• Conditions

  • If the number of blog posts are more than 10, utilize pagination display.
    • Otherwise, directly display blog posts.

Blog Post

With the chosen blog from a list, the user can view the blog's content and do comments.
Method: GET
Display:

• Title - Text | BLOG_POST.title

• Body - Text Area | BLOG_POST.body

• Date Posted - Text | BLOG_POST.datePosted

• Like Count - Text | LIKE.likeID count

• Includes: Like & Comment Functions

• Conditions

  • If blogPostID is null, redirects to Home page.
    • Otherwise, the list will be displayed.

Search Function

A function that filters blogs based on title or username.
Method: NONE
Display: (Filtered List of Items)

• Search - Text Field

• Search (on-keypress | on-change)

  • An empty search text field, displays the complete list by default.
  • A searched text should match from the list of items (Either by title or username)
    • Otherwise, the list will become empty.
  • If the condition is met, it returns and displays a filtered list of the searched item.

Like Function

A function that allows a registered user to leave a like in a blog post.
Method: PATCH
Display:

• Like - Button

• Like Button (on-click)

  • User must be logged-in. (Session)
    • Otherwise,gets redirected to Login page.
  • User should have yet to like the post, based on status from database.
    • Error: "You have already liked this post."
  • If all conditions are met, the count will be updated and the user is recorded in the database.
    • Updates in Like Table:
      • LIKE.blogPostID = Int
      • LIKE.userID = Int

Comment Function

A function that allows a registered user to add comments to a specific blog post.
Method: POST
Display:

• Comment - Text Area | String

• Username - Text | String

• Submit - Button

• Submit Button (on-click)

  • User must be logged-in. (Session)
    • Otherwise, gets redirected to Login page.
  • Comment (required field) > Error: “Please input your comment.”
  • If all conditions are met, the user's ID and comment will be saved in database.
    • Updates in Comment Table:
      • COMMENT.blogPostID = Int
      • COMMENT.userID = Int
      • COMMENT.body = varchar(MAX)

B. Admin Specifications

Contains override functions to manage users, modify and delete data globally. This is maintained by the administrators.

Login Function

A function that lets the administrator login to their account.
Method: POST
Display:

• Username - Text Field | String

• Password - Password Text Field | String

• Register - Button

• Back - Button

• Login Button (on-click)

  • Username (required field) > Error: “Please enter your username.”

    • Username should exist in database.
      • Error: “Username or password is incorrect.”*

  • Password (required field) > Error: “Please enter your password.”

    • If username exists, password should match in the database.
      • Error: “Password must be at least 8 characters.”

  • Checks from database, where isAdmin property from the User Table, should be true.

    • Otherwise, the user is logged-in as a standard user.

  • If all conditions are met, session data will be stored then redirects to “Home” page as Super User.

• Register Button (on-click)

  • Redirects to "Registration" component/page.

Logout Function

A function that lets the administrator log out from session.
Method: NONE
Display:
Logout - Button

• Logout Button (on-click)
  • Delete session from browser then redirect to 'Home' page.

Users (Table)

Shows and display users' information for the administrator to view, also an interface that allows disabling/enabling accounts.
Method: GET
Display: (Table)

• Username Column - Text | USER.username

• Email Address Column - Text | USER.email

• Status Column - Text | USER.status

• Includes: Disable/Enable User & Search Functions

• Status Column Row

  • The status retrieved from database are in boolean, interpreted as:
    • "Active" = true
    • "Inactive" = false

Disable/Enable User Function

A function that lets the administrator enable/disable a user account. Restricting user access.
Method: PATCH
Display:

• Enable/Disable - Button

• Enable/Disable Button (on-click)

  • Prompts administrator with a dialog, "Are you sure you want to change this user's status?"
    • Yes: Dismisses dialog then, the boolean status will be checked and be reassigned with the opposite boolean value. After that, notifies the user by sending an email.
      • Updates in User Table:
        • USER.status = tinyInt(1)
    • No: Dismisses dialog.

Search Function (User)

A search function that filters blogs based on username or email address.
Method: NONE
Display: (Filtered List of Items)

• Search - Text Field

• Search (on-keypress | on-change)

  • An empty search text field, displays the complete list by default.
  • A searched text should match from the list of items (Either by username or email address)
    • Otherwise, the list will return an empty list.
  • If the condition is met, it returns and displays a filtered list of the searched item.

Search Function (Blog Post)

A search function that filters blogs based on title or username.
Method: NONE
Display: (Filtered List of Items)

• Search - Text Field

• Search (on-keypress | on-change)

  • An empty search text field, displays the complete list by default.
  • A searched text should match from the list of items (Either by title or username)
    • Otherwise, the list will return an empty list.
  • If the condition is met, it returns and displays a filtered list of the searched item.

Blog Posts (Edit, Delete)

Contains the functions for globally editing and deleting blog posts, on the end of an administrator's privilege.

Edit Function

A function that lets the administrator edit any existing blog post.
Method: PUT
Display:

• Title - Text Field | String (Pre-filled)

• Body - Text Area | String (Pre-filled)

• Change - Button

• Change Button (on-click)

  • User must be logged-in as Admin. (Session)
    • Otherwise, gets redirected to Login page.
  • Title (required field) > Error: “Blog title should not be empty.”
    • Title should less than 51 characters only.
      • Error: "Your title should not exceed more than 50 characters."
  • Body (required field) > Error: “Blog content should not be empty.”
  • Current Title or Body string should not be the same when compared to the edited strings.
    • Error: There were no changes in your blog post, we can't update it.
  • If all conditions are met, updates blog in database.
    • Updates in Blog Post Table:
      • BLOG_POST.title = varchar(100)
      • BLOG_POST.body = varchar(MAX)

Delete Function

A function that lets the user delete the blog post he/she created.
Method: DELETE
Display:

• Title - Text | String (To display what blog post is to be deleted.)

• Delete - Button

• Delete Button (on-click)

  • User must be logged-in as Admin. (Session)
    • Otherwise, gets redirected to Login page.
  • Prompts a confirmation dialog. "Are you sure you want to delete this post?" with the blog post Title displayed.
    • Yes: dialog dismisses, then deletes the blog post from database.
      • Deletes in Blog Post Table equal to the BLOG_POST.blogPostID.
    • No: dialog dismisses.

Blog Posts (Read)

Contains functions related to displaying data and globally interacting with blog posts, on the end of an administrator's privilege.

Global Blogs (List)

Shows a list of all the existing blogs for Administrator modification.
Method: GET
Display: (List of Items)

• Title - Text | BLOG_POST.title

• Like Count - Text | LIKE.likeID query count

• Date Posted - Text | BLOG_POST.datePosted

• Date Modified - Text | BLOG_POST.dateModified

• Edit - Button

• Comments - Button

• Includes: Search & Delete Functions

• Edit Button (on-click)

  • Redirects to Edit blog component/page.

• Comments (on-click)

  • Redirects to Comment List (filtered by chosen post.)

• Item (on-click)

  • Redirects to the chosen blog post.

• Conditions

  • If the number of blog posts are more than 10, utilize pagination display.
    • Otherwise, directly display blog posts.

Comments (List)

Shows and display all comments, globally and chronologically. Deletion of comments can be done in here by the Administrator.
Method: GET
Display: (List of Items)

• Username - Text | COMMENT.userID
• Comment - Text | COMMENT.body
• Date Posted - Text | COMMENT.datePosted
• Includes: Delete Comment Function

Delete Comment Function

A function that lets the administrator delete a certain comment from the global comments list.
Method: DELETE
Display:

• Delete - Button

• Delete Button (on-click)

  • User must be logged-in as Admin. (Session)
    • Otherwise, gets redirected to Login page.
  • Prompts a confirmation dialog. "Are you sure you want to delete this comment?"
    • Yes: dialog dismisses, then deletes the comment from database.
      • Deletes row in Comment Table equal to the COMMENT.commentID.
    • No: dialog dismisses.

II. System Flow

In this section, shows the overall flow of the system with the defined functions list. The diagram is illustrated in a flow chart, divided into two states after logging in, the User State and the Admin State, both possessing different access on features as to how it interacts with the system. See the figure below.

As the diagram being already complex as it is, we only included the registered user and the administrator's possible actions to be shown in the flow. However, unregistered users being limited on their access are still indicated in the diagram based on the blue legend, labeled as Public; where marked features can be accessed by everyone on the planned blog website.

III. Database Architecture

This section shows the architecture as to how the system will be implemented and tied together in a relational manner. As a program works in a systematic way, putting relationships is essential; as this will make it easier to understand, track and trace our data flow.

Entity Relationship Diagram

Based on the function list defined above, this is somehow the basic interpretation of it as an entity relation diagram; showing how our features are interconnected to one another. See figure below.

Relation & Cardinality

As shown on the diagram, we made 4 tables all in all: the USER, BLOG, LIKE, COMMENT tables. If we will simply explain their relationships, from USER table, a user can have zero or many blog posts from BLOG_POST, as to why we have set the cardinality of zero to many. On the opposite side, one post from the BLOG_POST table can only have one user. Thus, the cardinality of one and only one.

On the otherhand, for the COMMENT table, users can have zero or more comments. This goes the same with, the blog posts, each post can have zero or more comments. Which brings us to an optimized solution of normalization to eliminate the data redundancy problem of IDs. This is done by having the COMMENT table cater both USER & BLOG_POST tables' IDs. This same problem occurs with the LIKE table, so we used normalization for this one as well. The cardinality shown above shows how this is actually managed well.

Design

The following are the specifications of each table that are presented on the figure above on the Entity Relationship Diagram.

###User Table

Column Name	Condensed Type	Null (?)	Key Type
userID	INT	No	Primary
username	VARCHAR	No	--
password	VARCHAR	No	--
email	VARCHAR	No	--
status	BOOLEAN	No	--
blogTitle	VARCHAR	Yes	--
blogDesc	VARCHAR	Yes	--
blogHeaderImg	BLOB	Yes	--
isAdmin	BOOLEAN	No	--
url	VARCHAR	No	--

This table is where a registered user's information will be stored. There are basically two parts, the basic credentials such as the username, password, email and so on. (The password will not directly be stored as is, since we're trying to guard any breach, an MD5 hashed password will do.) The other one is for the blog profile customization, such as the blog's title, description, url, and header image.

Blog Post Table

Column Name	Condensed Type	Null (?)	Key Type
blogPostID	INT	No	Primary
userID	INT	No	Foreign
title	VARCHAR	No	--
body	VARCHAR	No	--

This table is where the user's blog posts will be stored, the data information are pretty straightforward, it's a blog post having title and a body, linked with the user who posted it; in this case, the userID.

Like Table

Column Name	Condensed Type	Null (?)	Key Type
likeID	INT	No	Primary
blogPostID	INT	No	Foreign
userID	INT	No	Foreign

This table is responsible for managing the like feature's data, we simply link both of the IDs of the blog post and the author to keep track whether or not, the user has liked a post.

Comment Table

Column Name	Condensed Type	Null (?)	Key Type
commentID	INT	No	Primary
blogPostID	INT	No	Foreign
userID	INT	No	Foreign
body	VARCHAR	No	--

This table is responsible for storing the data of user comments in a post. Similarly, the structure is the same with the LIKE table, with the an additional attribute, the body, which holds the user's post.