Here is the complete source code of notification system using php and mysqli
Sql command
-- phpMyAdmin SQL Dump
-- version 4.7.4
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Feb 11, 2018 at 03:07 AM
-- Server version: 10.1.30-MariaDB
-- PHP Version: 7.2.1
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `notificationsystem`
--
-- --------------------------------------------------------
--
-- Table structure for table `notifications`
--
CREATE TABLE `notifications` (
`id` INT(11) NOT NULL,
`name` text NOT NULL,
`type` text NOT NULL,
`message` text NOT NULL,
`status` text NOT NULL,
`date` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `notifications`
--
INSERT INTO `notifications` (`id`, `name`, `type`, `message`, `status`, `date`) VALUES
(10, '', 'comment', 'Hi crush', 'read', '2018-02-09 00:21:21'),
(11, 'Irene', 'like', '', 'read', '2018-02-09 00:21:34'),
(12, 'Joe', 'like', '', 'unread', '2018-02-09 00:22:25');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `notifications`
--
ALTER TABLE `notifications`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `notifications`
--
ALTER TABLE `notifications`
MODIFY `id` INT(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
function.php
<?php
define('DBINFO', 'mysql:host=localhost;dbname=notificationsystem');
define('DBUSER','root');
define('DBPASS','');
function fetchAll($query){
$con = new PDO(DBINFO, DBUSER, DBPASS);
$stmt = $con->query($query);
return $stmt->fetchAll();
}
function performQuery($query){
$con = new PDO(DBINFO, DBUSER, DBPASS);
$stmt = $con->prepare($query);
if($stmt->execute()){
return true;
}else{
return false;
}
}
?>
index.php
<?php
include("functions.php");
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<meta name="description" content="">
<meta name="author" content="">
<title>Notification System in PHP and MySql</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
</head>
<body>
<nav class="navbar navbar-expand-md navbar-dark bg-dark fixed-top">
<a class="navbar-brand" href="#">Notification System in PHP and MySql</a>
<button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarsExampleDefault" aria-controls="navbarsExampleDefault" aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarsExampleDefault">
<ul class="navbar-nav mr-auto">
<li class="nav-item dropdown">
<a class="nav-link" href="http://example.com" id="dropdown01" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">Notifications
<?php
$query = "SELECT * from `notifications` where `status` = 'unread' order by `date` DESC";
if(count(fetchAll($query))>0){
?>
<span class="badge badge-light"><?php echo count(fetchAll($query)); ?></span>
<?php
}
?>
</a>
<div class="dropdown-menu" aria-labelledby="dropdown01">
<?php
$query = "SELECT * from `notifications` order by `date` DESC";
if(count(fetchAll($query))>0){
foreach(fetchAll($query) as $i){
?>
<a style ="
<?php
if($i['status']=='unread'){
echo "font-weight:bold;";
}
?>
" class="dropdown-item" href="view.php?id=<?php echo $i['id'] ?>">
<small><i><?php echo date('F j, Y, g:i a',strtotime($i['date'])) ?></i></small><br/>
<?php
if($i['type']=='comment'){
echo "Someone commented on your post.";
}else if($i['type']=='like'){
echo ucfirst($i['name'])." liked your post.";
}
?>
</a>
<div class="dropdown-divider"></div>
<?php
}
}else{
echo "No Records yet.";
}
?>
</div>
</li>
</ul>
<?php
if(isset($_POST['submit'])){
$message = $_POST['message'];
$query ="INSERT INTO `notifications` (`id`, `name`, `type`, `message`, `status`, `date`) VALUES (NULL, '', 'comment', '$message', 'unread', CURRENT_TIMESTAMP)";
if(performQuery($query)){
header("location:index.php");
}
}
?>
<form method="post" class="form-inline my-2 my-lg-0">
<input name="message"class="form-control mr-sm-2" type="text" placeholder="Message" required>
<button name="submit" class="btn btn-outline-success my-2 my-sm-0" type="submit">Submit</button>
</form> |
<?php
if(isset($_POST['like'])){
$name = $_POST['name'];
$query ="INSERT INTO `notifications` (`id`, `name`, `type`, `message`, `status`, `date`) VALUES (NULL, '$name', 'like', '', 'unread', CURRENT_TIMESTAMP)";
if(performQuery($query)){
header("location:index.php");
}
}
?>
<form method="post" class="form-inline my-2 my-lg-0">
<input name="name" class="form-control mr-sm-2" type="text" placeholder="Name" required>
<button name="like" class="btn btn-outline-success my-2 my-sm-0" type="submit">Like </button>
</form>
</div>
</nav>
<main role="main" class="container">
<div class="starter-template">
<h1>Bootstrap starter template</h1>
<p class="lead">Use this document as a way to quickly start any new project.<br> All you get is this text and a mostly barebones HTML document.</p>
</div>
</main><!-- /.container -->
<script src="https://code.jquery.com/jquery-3.2.1.slim.min.js" integrity="sha384-KJ3o2DKtIkvYIK3UENzmM7KCkRr/rE9/Qpg6aAZGJwFDMVNA/GpGFF93hXpG5KkN" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js" integrity="sha384-ApNbgh9B+Y1QKtv3Rn7W3mgPxhU9K/ScQsAP7hUibX39j7fakFPskvXusvfa0b4Q" crossorigin="anonymous"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>
</body>
</html>
view.php
<h1>Notifications</h1>
<?php
include("functions.php");
$id = $_GET['id'];
$query ="UPDATE `notifications` SET `status` = 'read' WHERE `id` = $id;";
performQuery($query);
$query = "SELECT * from `notifications` where `id` = '$id';";
if(count(fetchAll($query))>0){
foreach(fetchAll($query) as $i){
if($i['type']=='like'){
echo ucfirst($i['name'])." liked your post. <br/>".$i['date'];
}else{
echo "Some commented on your post.<br/>".$i['message'];
}
}
}
?><br/>
<a href="index.php">Back</a>