Social Networking Site Project

This project requires the creation of a social networking site similar to blogging platforms, using HTML5, CSS, PHP, JavaScript/Ajax, and a RESTful API for communication. The site must include features such as posting, commenting, liking, editing, deleting, hiding posts, tagging users, sharing links, user account management (including admin controls), email notifications, and topic categorization. More challenging aspects include trend analysis for recommendations and image upload functionality. A complete MySQL database schema is provided, including tables for posts, comments, likes, notifications, users, endorsements, and tags, with appropriate foreign key relationships for data integrity. The project emphasizes practical application of web development technologies and database management.

Create a social networking site that enables people to share any ideas, similar to blogging sites. The
implementation must be using HTML5, CSS, PHP, JavaScript/Ajax webpages sending JSON through
RESTful API of responders.
I have included SQL script for MySQL database for the website at the end of this document. Also, do
not worry too much about the looks of the site (CSS) as that will be changed anyways.
The site must have/do the following:-
Minor functionalities of proposed SN.
A. Posts/messages/comments/chat/editing and uploading
1. Post: enable users to post
2. Comment count: count the amount of comment and display it next to the post.
3. Like count: count the amount of likes a post get and display it next to the post.
4. Like: the ability to register when user click on like link.
5. Comment: being able to comment on a post
6. Delete: ability to delete own post or comment.
7. Edit: ability to edit own post or comment.
8. Hide post: ability to hide own post or tags.
B. Groups, friends, followers & other relations with other users
9. Tag: able to tag another user.
10. Share: ability to share links form other sites. This will not require the user to be on our SN,
but will require an active account. (Not priority)
11. Social network links: create a link to other social networks.
C. Accounts, Authorization, Users, Profiles
12. Management account: which will give access to extra buttons on all posts and comments
that will enable admin to delete and restrict comments or accounts.
13. Register: create and manage own an account
14. Delete certain post or comment: buttons for deleting any post or comment for admin
accounts.
15. Restrict account: button to temporarily hide user account.
16. User account
D. Widespread general Web-site features & good practice (i.e. not specifically "Social Networking")
17. Email notification: enable email notification. This will be used to notify users of comments,
likes and tags.

E. Specific Social Networking features of YOUR site
18. Topic: this will allow users to post or view ideas by topic. Most new ideas are generic by
nature, but one might share idea that is specific to a profession. Ideas that are specific to a
subject such as science, IT, cosmology will be categorized.
Challenging functionalities of proposed SN.
19. Trend analysis for search and recommend trending posts on the home page of our SN. The
technical details of how to implement the function is yet to be researched.
20. Image upload. Drag and drop would be considered but not prioritized.

CREATE DATABASE if not exists web_coursework;
use web_coursework;
create table tblPosts (
postID int auto_increment,
userID int not null,
postTitle varchar(250) not null,
postTopic varchar(250) not null,
postDateTime TIMESTAMP not null DEFAULT CURRENT_TIMESTAMP,
postBody varchar(10000) not null,
PRIMARY KEY (postID)
)AUTO_INCREMENT=1;
create table tblComment (
commentID int auto_increment,
postID int not null,
userID int not null,
commentsDateTime TIMESTAMP not null DEFAULT CURRENT_TIMESTAMP,
comment_Body varchar(1000) not null,
PRIMARY KEY (commentID)
)AUTO_INCREMENT=1;
create table tblLike (
likeID int auto_increment,
userID int not null,
postID int not null,

commentID int not null,
likeDateTime TIMESTAMP not null DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (likeID),
unique (likeID, postID),
unique (likeID, commentID)
)AUTO_INCREMENT=1;
create table tblNotification (
notificationID int auto_increment,
userID int not null,
postID int not null,
notificationDateTime TIMESTAMP not null DEFAULT CURRENT_TIMESTAMP,
notificationBody varchar(250),
PRIMARY KEY (notificationID)
)AUTO_INCREMENT=1;
create table tblUser (
userID int auto_increment,
userName varchar(100) not null,
userPass varchar(100) not null,
userEmail varchar(100) not null,
userJoinDate TIMESTAMP not null DEFAULT CURRENT_TIMESTAMP,
userType varchar(10) not null DEFAULT 'user',
userFollowers int(10) not null DEFAULT 0,
userImage varchar(250) null,
PRIMARY KEY (userID)
)AUTO_INCREMENT=1;

create table tblEndorse (
endorseID int auto_increment,
userID int not null,
postID int not null,
endorserMessage varchar(1000) not null,
endorseDateTime TIMESTAMP not null DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (endorseID)
)AUTO_INCREMENT=1;
create table tblTag (
tagID int auto_increment,
tagName varchar(100) not null,
userID int not null,
postID int not null,
tagURL varchar(250) not null,
targetName varchar(250) not null,
tagDateTime TIMESTAMP not null DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (tagID)
)AUTO_INCREMENT=1;
ALTER TABLE tblComment ADD FOREIGN KEY (postID) REFERENCES tblPosts(postID) ON DELETE
CASCADE ON UPDATE CASCADE;
ALTER TABLE tblComment ADD FOREIGN KEY (userID) REFERENCES tblUser(userID) ON DELETE
CASCADE ON UPDATE CASCADE;
ALTER TABLE tblPosts ADD FOREIGN KEY (userID) REFERENCES tblUser(userID) ON DELETE CASCADE
ON UPDATE CASCADE;

ALTER TABLE tblLike ADD FOREIGN KEY (postID) REFERENCES tblPosts(postID) ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE tblLike ADD FOREIGN KEY (userID) REFERENCES tblUser(userID) ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE tblLike ADD FOREIGN KEY (commentID) REFERENCES tblComment(commentID) ON
DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE tblNotification ADD FOREIGN KEY (postID) REFERENCES tblPosts(postID) ON DELETE
CASCADE ON UPDATE CASCADE;
ALTER TABLE tblNotification ADD FOREIGN KEY (userID) REFERENCES tblUser(userID) ON DELETE
CASCADE ON UPDATE CASCADE;
ALTER TABLE tblEndorse ADD FOREIGN KEY (postID) REFERENCES tblPosts(postID) ON DELETE
CASCADE ON UPDATE CASCADE;
ALTER TABLE tblEndorse ADD FOREIGN KEY (userID) REFERENCES tblUser(userID) ON DELETE
CASCADE ON UPDATE CASCADE;
ALTER TABLE tblTag ADD FOREIGN KEY (postID) REFERENCES tblPosts(postID) ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE tblTag ADD FOREIGN KEY (userID) REFERENCES tblUser(userID) ON DELETE CASCADE
ON UPDATE CASCADE;