I have two tables named comments and users where id in comments is the foreign key.
I am successfully able to show notification of the message posted. But I also want to post the name and details of the user who posted it but it doesn't seem to work.I tried joins but I am not able to figure out where the problem exactly is.
Here is my comments table:
CREATE TABLE IF NOT EXISTS 'comments' (
'notificationid' int(30) NOT NULL,
'comment' varchar(300) NOT NULL,
'id' bigont(20) NOT NULL /* foreign key */
'status' int(1) NOT NULL DEFAULT '0',
'subject' varchar(300) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ;
users.sql:
CREATE TABLE 'users' (
'id' bigint(20) NOT NULL auto_increment,
'full_name' tinytext collate latin1_general_ci NOT NULL,
'user_name' varchar(200) collate latin1_general_ci NOT NULL default '',
'user_email' varchar(220) collate latin1_general_ci NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ;
This is my view-notification.php:
<?php
$conn = new mysqli("localhost","root","","noti");
$sql="UPDATE comments SET status=1 WHERE status=0";
$result=mysqli_query($conn, $sql);
$sql="select * from comments ORDER BY id DESC limit 5";
$result=mysqli_query($conn, $sql);
$result3 = mysql_query("SELECT * FROM 'users'
JOIN comments ON comments.id = users.id
WHERE `user_id` = 54"); // user_id not is a column, is users.id
$response='';
while($row=mysqli_fetch_array($result)) {
$response = $response . "<div class='notification-item'>" .
"<div class='notification-subject'>". $row["subject"] . "</div>" .
"<div class='notification-comment'>" . $row["comment"] . "</div>" .
"</div>";
}
while($row1=mysqli_fetch_array($result3)) { //This is what is tried
"<div class='notification-comment'>" . $row["user_name"] . "</div>".
"</div>"; // change $row for $row1
}
if(!empty($response)) {
print $response;
}
?>
The notification.php file is responsible for displaying notifications in the index.php page.
And this is the final index.php page:
<?php
$conn = new mysqli("localhost","root","","noti");
$count=0;
if(!empty($_POST['add'])) {
$subject = mysqli_real_escape_string($conn,$_POST["subject"]);
$comment = mysqli_real_escape_string($conn,$_POST["comment"]);
$sql = "INSERT INTO comments (subject,comment) VALUES('" . $subject . "','" . $comment . "')";
mysqli_query($conn, $sql);
}
$sql2="SELECT * FROM comments WHERE status = 0";
$result=mysqli_query($conn, $sql2);
$count=mysqli_num_rows($result);
?>
<html>
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Facebook Like Header Notification in PHP</title>
<link rel="stylesheet" href="notification-demo-style.css" type="text/css">
<script src="https://code.jquery.com/jquery-2.1.1.min.js" type="text/javascript"></script>
<script type="text/javascript">
function myFunction() {
$.ajax({
url: "view_notification.php",
type: "POST",
processData:false,
success: function(data){
$("#notification-count").remove();
$("#notification-latest").show();$("#notification-latest").html(data);
},
error: function(){}
});
}
$(document).ready(function() {
$('body').click(function(e){
if ( e.target.id != 'notification-icon'){
$("#notification-latest").hide();
}
});
});
</script>
</head>
<body>
<div class="demo-content">
<div id="notification-header">
<div style="position:relative">
<button id="notification-icon" name="button" onclick="myFunction()" class="dropbtn"><span id="notification-count"><?php if($count>0) { echo $count; } ?></span><img src="notification-icon.png" /></button>
<div id="notification-latest"></div>
</div>
</div>
<?php if(isset($message)) { ?> <div class="error"><?php echo $message; ?></div> <?php } ?>
<?php if(isset($success)) { ?> <div class="success"><?php echo $success;?></div> <?php } ?>
<form name="frmNotification" id="frmNotification" action="" method="post" >
<div id="form-header" class="form-row">Add New Message</div>
<div class="form-row">
<div class="form-label">Subject:</div><div class="error" id="subject"></div>
<div class="form-element">
<input type="text" name="subject" id="subject" required>
</div>
</div>
<div class="form-row">
<div class="form-label">Comment:</div><div class="error" id="comment"></div>
<div class="form-element">
<textarea rows="4" cols="30" name="comment" id="comment"></textarea>
</div>
</div>
<div class="form-row">
<div class="form-element">
<input type="submit" name="add" id="btn-send" value="Submit">
</div>
</div>
</form>
</div>
</body>
</html>
Everything is working except for that the user details in the notification.
