Build Push Notification System with PHP & MySQL – notification alert in php mysql

Today, We want to share with you notification alert in php mysql.In this post we will show you push notification using php and mysql, hear for php notification alert when new record is inserted we will give you demo and example for implement.In this post, we will learn about Push Notification System With PHP & MySQL Demo [Updated – 2021] with an example.

notification in php demo

I will cover this tutorial step by step with live example to create notifications with push information by administrator and normal can login to see the push notifications.

So let’s implement Push subscription System with PHP and MySQL. The major files are:

  • login.php
  • index.php
  • manage.php
  • subscription.js
  • subscription.php
  • Visitor.php: Class to hold visitor methods.
  • Subscription.php: Class to hold subscription methods.

Step 1: Create MySQL Database Table

First we will Make MySQL database table subscription_visitor to admin and normal visitor for testing the push subscription system.

CREATE TABLE `subscription_visitor` (
  `id` int(11) NOT NULL,
  `visitorname` varchar(100) NOT NULL,
  `password` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `subscription_visitor`
  ADD PRIMARY KEY (`id`);
  
ALTER TABLE `subscription_visitor`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
I will insert few visitor records for testing purpose.

INSERT INTO `subscription_visitor` (`id`, `visitorname`, `password`) VALUES
(1, 'infinityknow', '12345'),
(2, 'admin', '12345');

I will Make MySQL database table subscriptions to store subscriptions information.

CREATE TABLE `subscriptions` (
  `id` int(11) NOT NULL,
  `title` varchar(250) NOT NULL,
  `message` text NOT NULL,
  `ntime` datetime DEFAULT NULL,
  `repeat` int(11) DEFAULT 1,
  `nloop` int(11) NOT NULL DEFAULT 1,
  `publish_date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `visitorname` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `subscriptions`
  ADD PRIMARY KEY (`id`);
  
ALTER TABLE `subscriptions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

Step 2: Design Visitor Login Form

I will Make login.php file and design login form.

Visitor Login:


I will handle visitor login functionality by calling login() method from Visitor.php class. I will store visitorname into session to use while implementing push subscription functionality.

session_start();
$message = '';
if (!empty($_POST['visitorname']) && !empty($_POST['password'])) {
	
	include_once 'config/Database.php';
	include_once 'class/Visitor.php';

	$database = new Database();
	$db = $database->getConnection();
	$visitor = new Visitor($db);

	$visitor->visitorname = $_POST['visitorname'];
    $visitor->password = $_POST['password'];	
	
	if($visitor->login()) {
		$_SESSION['visitorname'] = $visitor->visitorname;
		header("Location:index.php");
	} else {
		$message = "Invalid visitorname or password!";
	}
}

I will implement method login in class Visitor.php.

function login (){		
	$sqlQQ = $this->conn->prepare("
		SELECT id as visitorid, visitorname, password 
		FROM ".$this->visitorTable." 
		WHERE visitorname = ? AND password = ? ");
	$sqlQQ->bind_param("ss", $this->visitorname, $this->password);	
	$sqlQQ->execute();
	$result = $sqlQQ->get_result();		
	return $result;			
}

Step 3: Display Visitor Account

I will Make index.php file to display logged in visitor information. I will also display manage subscription section for Administrator visitor.

Example: Build Push Notification System with PHP & MySQL

Visitor Account

Manage Subscription | Logged in : | Logout Login
id="loggedIn" >

You're welcome! You can manage

Step 4: Manage Subscriptions and list

I will Make file manage.php and manage Administrator functionality to Make new subscription with information and assign to visitors to display push subscriptions. I will call method saveSubscription() from class Subscription.php. I will also list created subscriptions on this page.

Add New Subscription:

Title
Message
Broadcast time
Loop (time)
Loop Every (Minute)
For
title = $_POST['title']; $subscription->message = $_POST['message']; $subscription->ntime = date('Y-m-d H:i:s'); $subscription->repeat = $_POST['loops']; $subscription->nloop = $_POST['loop_every']; $subscription->visitorname = $_POST['visitor']; if($subscription->saveSubscription()) { echo '* save new subscription success'; } else { echo 'error save data'; } } else { echo '* completed the parameter above'; } } ?>

Subscriptions List:

listSubscription(); while ($subscr = $subscriptionList->fetch_assoc()) { ?>
No Next Schedule Title Message Remains Visitor
I will implement method saveSubscription() in class Subscription.php. function saveSubscription(){ $insertQuery = " INSERT INTO ".$this->subscriptionTable."( `title`, `message`, `ntime`, `repeat`, `nloop`, `visitorname`) VALUES(?,?,?,?,?,?)"; $sqlQQ = $this->conn->prepare($insertQuery); $sqlQQ->bind_param("sssiis",$this->title, $this->message, $this->ntime, $this->repeat, $this->nloop, $this->visitorname); if($sqlQQ->execute()){ return true; } return false; }

Step 5: Handle Push Subscriptions

I will Make a JavaScript file subscription.js and implement function getSubscription() to get subscription information by making Ajax request to subscription.php file. The function will also handle push subscription functionality by checking permissions and display it.

function getSubscription() {	
	if (!Subscription) {
		$('body').append('*Browser does not support Web Subscription');
		return;
	}
	if (Subscription.permission !== "granted") {		
		Subscription.requestPermission();
	} else {		
		$.ajax({
			url : "subscription.php",
			type: "POST",
			success: function(response, textStatus, jqXHR) {
				var response = jQuery.parseJSON(response);
				if(response.result == true) {
					var subscriptionDetails = response.subscr;
					for (var i = subscriptionDetails.length - 1; i >= 0; i--) {
						var subscriptionUrl = subscriptionDetails[i]['url'];
						var subscriptionObj = new Subscription(subscriptionDetails[i]['title'], {
							icon: subscriptionDetails[i]['icon'],
							body: subscriptionDetails[i]['message'],
						});
						subscriptionObj.onclick = function () {
							window.open(subscriptionUrl); 
							subscriptionObj.close();     
						};
						setTimeout(function(){
							subscriptionObj.close();
						}, 5000);
					};
				} else {
				}
			},
			error: function(jqXHR, textStatus, errorThrown)	{}
		}); 
	}
}

Step 6: Get Push Subscription Details

I will Make subscription.php file and get logged in visitor subscription information by calling method getSubscriptionByVisitor() to show the subscription. The subscription information has been updated after displaying subscription by calling method updateSubscription() from class Subscription.php. The information of subscription is returned as JSON data.

session_start(); 
include_once 'config/Database.php';
include_once 'class/Subscription.php';
$database = new Database();
$db = $database->getConnection();
$subscription = new Subscription($db);
$array=array(); 
$rows=array(); 
$subscription->visitorname = $_SESSION['visitorname'];
$result = $subscription->getSubscriptionByVisitor(); 
$totalSubscription = 0;
while ($visitorSubscription = $result->fetch_assoc()) { 		
 $data['title'] = $visitorSubscription['title'];
 $data['message'] = $visitorSubscription['message'];
 $data['icon'] = 'https://www.infinityknow.com/demo/build-push-subscription-system-php-mysql-demo/avatar.png';
 $data['url'] = 'https://www.infinityknow.com';
 $rows[] = $data;
 $nextime = date('Y-m-d H:i:s',strtotime(date('Y-m-d H:i:s'))+($visitorSubscription['repeat']*60));
 $subscription->nexttime = $nextime;
 $subscription->id = $visitorSubscription['id'];
 $subscription->updateSubscription();
 $totalSubscription++;
}
$array['subscr'] = $rows;
$array['count'] = $totalSubscription;
$array['result'] = true;
echo json_encode($array);

I will implement method getSubscriptionByVisitor() in class Subscription.php.

function getSubscriptionByVisitor(){
	$query = "
		SELECT *
		FROM ".$this->subscriptionTable." 
		WHERE visitorname= ? AND nloop > 0 AND ntime <= CURRENT_TIMESTAMP()";
	$sqlQQ = $this->conn->prepare($query);				
	$sqlQQ->bind_param("s", $this->visitorname);	
	$sqlQQ->execute();		
	$result = $sqlQQ->get_result();		
	return $result;	
}

I will implement method updateSubscription() in class Subscription.php.

function updateSubscription() {		
	$updateQuery = "
		UPDATE ".$this->subscriptionTable." 
		SET ntime= ?, publish_date=CURRENT_TIMESTAMP(), nloop = nloop-1 
		WHERE id= ? ";		
	$sqlQQ = $this->conn->prepare($updateQuery);	 		 
	$sqlQQ->bind_param("si", $this->nexttime, $this->id);		
	if($sqlQQ->execute()){
		return true;
	}	 
	return false;		
}	

I hope you get an idea about php notification system like facebook.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.

Leave a Comment