Developing a mini blog just with sql queries 1

In this tutorial we shall be focusing on using SQL Queries to build a blog, but you can also build a standalone blog with it just by integrate it with any server programming language like PHP, Python, or your Node lol.

What is a DATABASE

A database (DB) for short is a sort of formal structure to keep data in – much like offices used to keep paper files with employee information, databases now can do the same thing, except electronically.

It’s a lot easier to edit and share that information now of course, and more information can be stored in one place. It’s also possible to set up connections between different tables and databases to better manage large amounts of information.

On the otherhand,

A DATABASE TABLE

The database table is where all the data in a database is stored, and without tables, there would not be much use for relational databases.

Most tables have only a few columns, yet can have hundreds if not thousands of rows of data, with each row making up one set of connected data, such as a name, birth date and job title. Compared to previous data storage methods like paper and the like, electronic databases can be updated very quickly and easily, and it’s incredibly easy to look up information – what used to take hours can be done in just a few seconds with a simple database query or search!

Source [technipages.com/definition/database]

Meaning of SQL Query

Structured Query Language (SQL) is a standardized programming language that is used to manage relational databases and perform various operations on the data in them. Initially created in the 1970s, SQL is regularly used not only by database administrators, but also by developers writing data integration scripts and data analysts looking to set up and run analytical queries.

Source [techtarget.com/searchdatamanagement/definit..

Creating the DATABASE

Visit any SQL RUNNER e.g PhpMyAdmin, MySQL Workbench e.t.c copy and paste the following codes

CREATE DATABASE IF NOT EXISTS Blog;

Creation of the Necessary Tables

Author Table

CREATE TABLE IF NOT EXISTS  Author (
    author_id INT AUTO_INCREMENT,
    full_name VARCHAR(255) NOT NULL,
    about TEXT,
    is_admin TINYINT(1),
    status TINYINT(1),
    date_registered DATETIME,
    password VARCHAR(255),
    PRIMARY KEY (account_id)
)

Posts Table

CREATE TABLE IF NOT EXISTS Posts (
    post_id INT AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    is_published TINYINT(1),
    date_published DATETIME,
    date_edited DATETIME,
    author_id INT NOT NULL,
    thumbnail VARCHAR(255) DEFAULT('thumbnail.png'),
    PRIMARY KEY (post_id),
    FOREIGN KEY (author_id) REFERENCES Author(author_id)
)

Comments Table

CREATE TABLE IF NOT EXISTS Comments (
    c_id INT AUTO_INCREMENT,
    post_id INT NOT NULL,
    comment TEXT,
    is_published TINYINT(1),
    date_published DATETIME,
    date_edited DATETIME,
    author_id INT NOT NULL,
    PRIMARY KEY (c_id),
    FOREIGN KEY (post_id) REFERENCES Posts(post_id)
)

Replies Table

CREATE TABLE IF NOT EXISTS Replies (
    r_id INT AUTO_INCREMENT,
    c_id INT NOT NULL,
    reply TEXT,
    is_published TINYINT(1),
    date_published DATETIME,
    date_edited DATETIME,
    author_id INT NOT NULL,
    PRIMARY KEY (r_id),
    FOREIGN KEY (c_id) REFERENCES Comments(c_id)
)

Likes Table

CREATE TABLE IF NOT EXISTS PostLikes (
    like_id INT AUTO_INCREMENT,
    post_id INT NOT NULL,
    likes TINYINT(1),
    is_published TINYINT(1) DEFAULT('1'),
    date_published DATETIME,
    date_edited DATETIME,
    author_id INT NOT NULL,
    PRIMARY KEY (like_id),
    FOREIGN KEY (post_id) REFERENCES Posts(post_id)
)

Adding Authors

Adding An Administrator

INSERT INTO Author (full_name, about, is_admin, status, password) VALUES('Admin', 'Hey am an Admin', 1, 1, encrypt(password))

Adding An Normal user

INSERT INTO Author (full_name, about, is_admin, status, password) VALUES('Admin', 'Hey am an Admin', 1, 0, encrypt(password))

Adding Posts

INSERT INTO Posts (title, description, is_published, author_id, thumbnail) VALUES('Hash Node APi', 'This is a post about Hash Node Api',  1, 1, 'thumbnail.png')

Adding Comments

INSERT INTO Comments (post_id, comment, is_published, author_id) VALUES(1, 'New comment on Hash Node APi', 1, 1)

Replying to Comments

INSERT INTO Replies (c_id, comment, is_published, author_id) VALUES(1, 'New Reply on Hash Node APi', 1, 1)

Liking Posts

INSERT INTO Replies (post_id, likes, is_published, author_id) VALUES(1, 1, 1, 1)