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.

<div class="container">		
	<h2>Visitor Login:</h2>
	<div class="row">
		<div class="col-sm-4">
			<form method="post">
				<div class="form-group">
				<?php if ($message ) { ?>
					<div class="alert alert-warning"><?php echo $message; ?></div>
				<?php } ?>
				</div>
				<div class="form-group">
					<label for="visitorname">Visitorname:</label>
					<input type="visitorname" class="form-control" name="visitorname" required>
				</div>
				<div class="form-group">
					<label for="password">Password:</label>
					<input type="password" class="form-control" name="password" required>
				</div>  
				<button type="submit" name="login" class="btn btn-default">Login</button>
			</form><br>					
		</div>
	</div>
</div>	

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.

<div class="container">		
	<h2>Example: Build Push Notification System with PHP & MySQL</h2>	
	<h3>Visitor Account </h3>
	<?php if(isset($_SESSION['visitorname']) && $_SESSION['visitorname'] == 'admin') { ?>
		<a href="manage.php">Manage Subscription</a> | 
	<?php } ?>
	<?php if(isset($_SESSION['visitorname']) && $_SESSION['visitorname']) { ?>
		Logged in : <strong><?php echo $_SESSION['visitorname']; ?></strong> | <a href="logout.php">Logout</a>
	<?php } else { ?>
		<a href="login.php">Login</a>
	<?php } ?>
	<hr> 
	<?php if (isset($_SESSION['visitorname']) && $_SESSION['visitorname']) { ?>
		<div <?php if($_SESSION['visitorname'] != 'admin') { ?> id="loggedIn" <?php } ?>>
			<h4>
				You're welcome! You can manage 
			</h4>
		</div>
	<?php } ?>		
</div>	

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.

Read Also:  Laravel Autocomplete Live Search from database

<div class="row">
	<div class="col-sm-6">
		<h3>Add New Subscription:</h3>
		<form method="post"  action="<?php echo $_SERVER['PHP_SELF']; ?>">										
			<table class="table borderless">
				<tr>
					<td>Title</td>
					<td><input type="text" name="title" class="form-control" required></td>
				</tr>	
				<tr>
					<td>Message</td>
					<td><textarea name="message" cols="50" rows="4" class="form-control" required></textarea></td>
				</tr>			
				<tr>
					<td>Broadcast time</td>
					<td><select name="ntime" class="form-control"><option>Now</option></select> </td>
				</tr>
				<tr>
					<td>Loop (time)</td>
					<td><select name="loops" class="form-control">
					<?php 
						for ($i=1; $i<=5 ; $i++) { ?>
							<option value="<?php echo $i ?>"><?php echo $i ?></option>
					<?php } ?>
					</select></td>
				</tr>
				<tr>
					<td>Loop Every (Minute)</td>
					<td><select name="loop_every" class="form-control">
					<?php 
					for ($i=1; $i<=60 ; $i++) { ?>
						<option value="<?php echo $i ?>"><?php echo $i ?></option>
					<?php } ?>
					</select> </td>
				</tr>
				<tr>
					<td>For</td>
					<td><select name="visitor" class="form-control">
					<?php 		
					$allVisitor = $visitor->listAll(); 							
					while ($visitor = $allVisitor->fetch_assoc()) { 	
					?>
					<option value="<?php echo $visitor['visitorname'] ?>"><?php echo $visitor['visitorname'] ?></option>
					<?php } ?>
					</select></td>
				</tr>
				<tr>
					<td colspan=1></td>
					<td colspan=1></td>
				</tr>					
				<tr>
					<td colspan=1></td>
					<td><button name="submit" type="submit" class="btn btn-info">Add Message</button></td>
				</tr>
			</table>
		</form>
	</div>
</div>
<?php 
if (isset($_POST['submit'])) { 
	if(isset($_POST['message']) and isset($_POST['ntime']) and isset($_POST['loops']) and isset($_POST['loop_every']) and isset($_POST['visitor'])) {
		$subscription->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';
	}
} 
?>
<h3>Subscriptions List:</h3>
<table class="table">
	<thead>
		<tr>
			<th>No</th>
			<th>Next Schedule</th>
			<th>Title</th>
			<th>Message</th>
			<th>Remains</th>
			<th>Visitor</th>
		</tr>
	</thead>
	<tbody>
		<?php $subscriptionCount =1; 
		$subscriptionList = $subscription->listSubscription(); 			
		while ($subscr = $subscriptionList->fetch_assoc()) { 	
		?>
		<tr>
			<td><?php echo $subscriptionCount ?></td>
			<td><?php echo $subscr['ntime'] ?></td>
			<td><?php echo $subscr['title'] ?></td>
			<td><?php echo $subscr['message'] ?></td>
			<td><?php echo $subscr['nloop']; ?></td>
			<td><?php echo $subscr['visitorname'] ?></td>
		</tr>
		<?php $subscriptionCount++; } ?>
	</tbody>
</table>

I will implement method saveSubscription() in class <b>Subscription.php.</b>

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.

Read Also:  Simple Ecommerce PHP Shopping Cart Application

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.

Read Also:  PHP stdClass object Array Examples with scripts

Related FAQ

Here are some more FAQ related to this Article:

  1. Read Also:  authentication login with username or email in laravel
  2. Read Also:  Install MongoDB on MacOS Step By Step
  3. Read Also:  How to notify when new record inserted in sql database in php?
  4. Read Also:  Jquery Customised datatable Grids inline add edit delete
  5. Read Also:  Full form of Technologies Related
  6. Read Also:  Set flashdata(set_flashdata) in codeigniter Example
  7. Read Also:  Angular 9/8 Material Textarea Examples
  8. Read Also:  Push Notification System with PHP & MySQL Demo [updated - 2021]
  9. Read Also:  Simple PHP Rating System with database using JavaScript
  10. Read Also:  Simple Ecommerce PHP Shopping Cart Application
CLOSEX