Myujth

I written a simple script where users can post without sign up or log in, something like conffesion website, people can post and get like and unlike on their posts. I want to count like then sort by most popular and most newest and show it on a main page, and I tried literally everything but nothing seems to work, heres my code:

comment-like-unlike.php

 ?php
require_once ("db.php");

$memberId = 1;
$commentId = $_POST['comment_id'];
$likeOrUnlike = 0;
if($_POST['like_unlike'] == 1)

$likeOrUnlike = $_POST['like_unlike'];


$sql = "SELECT * FROM tbl_like_unlike WHERE comment_id=" . $commentId . " and member_id=" . $memberId;
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);

if (! empty($row)) 

 $query = "UPDATE tbl_like_unlike SET like_unlike = " . $likeOrUnlike . " WHERE comment_id=" . $commentId . " and member_id=" . $memberId;
 else

 $query = "INSERT INTO tbl_like_unlike(member_id,comment_id,like_unlike) VALUES ('" . $memberId . "','" . $commentId . "','" . $likeOrUnlike . "')";

mysqli_query($conn, $query);

$totalLikes = "No ";
$likeQuery = "SELECT sum(like_unlike) AS likesCount FROM tbl_like_unlike WHERE comment_id=".$commentId;
$resultLikeQuery = mysqli_query($conn,$likeQuery);
$fetchLikes = mysqli_fetch_array($resultLikeQuery,MYSQLI_ASSOC);
if(isset($fetchLikes['likesCount'])) 
 $totalLikes = $fetchLikes['likesCount'];


echo $totalLikes;
?>

comment-add.php

 <?php
require_once ("db.php");
$commentId = isset($_POST['comment_id']) ? $_POST['comment_id'] : "";
$comment = isset($_POST['comment']) ? $_POST['comment'] : "";
$commentSenderName = isset($_POST['name']) ? $_POST['name'] : "";
$date = date('Y-m-d H:i:s');

$sql = "INSERT INTO tbl_comment(parent_comment_id,comment,comment_sender_name,date) VALUES ('" . $commentId . "','" . $comment . "','" . $commentSenderName . "','" . $date . "')";

$result = mysqli_query($conn, $sql);

if (! $result) 
 $result = mysqli_error($conn);

echo $result;
?>

get-like-unlike.php

 <?php
require_once ("db.php");

$commentId = $_POST['comment_id'];
$totalLikes = "No ";
$likeQuery = "SELECT sum(like_unlike) AS likesCount FROM tbl_like_unlike WHERE comment_id=".$commentId;
$resultLikeQuery = mysqli_query($conn,$likeQuery);
$fetchLikes = mysqli_fetch_array($resultLikeQuery,MYSQLI_ASSOC);
if(isset($fetchLikes['likesCount'])) 
 $totalLikes = $fetchLikes['likesCount'];


echo $totalLikes;
?>

And my index.php code for posts and likes:

 <div class="comment-form-container">
 <form id="frm-comment">
 <div class="input-row">
 <input type="hidden" name="comment_id" id="commentId"
 placeholder="Name" /> <input class="input-field"
 type="text" name="name" id="name" placeholder="Name" />
 </div>
 <div class="input-row">
 <textarea class="input-field" type="text" name="comment"
 id="comment" placeholder="Add a Comment"> </textarea>
 </div>
 <div>
 <font color="white"><input type="button" class="btn-submit" id="submitButton"
 value="Publish" /></font>
 </div>

 </form>
 </div>
 <div id="output"></div>
 <script>
 var totalLikes = 0;
 var totalUnlikes = 0;

 function postReply(commentId) 
 $('#commentId').val(commentId);
 $("#name").focus();
 

 $("#submitButton").click(function () 
 $("#comment-message").css('display', 'none');
 var str = $("#frm-comment").serialize();

 $.ajax(
 url: "comment-add.php",
 data: str,
 type: 'post',
 success: function (response)
 
 var result = eval('(' + response + ')');
 if (response)
 
 $("#comment-message").css('display', 'inline-block');
 $("#name").val("");
 $("#comment").val("");
 $("#commentId").val("");
 listComment();
 else
 
 alert("Failed to add comments !");
 return false;
 
 
 );
 );

 $(document).ready(function () 
 listComment();
 );

 function listComment() 
 $.post("comment-list.php",
 function (data) 
 var data = JSON.parse(data);

 var comments = "";
 var replies = "";
 var item = "";
 var parent = -1;
 var results = new Array();

 var list = $("<ul class='outer-comment'>");
 var item = $("<li>").html(comments);

 for (var i = 0; (i < data.length); i++)
 
 var commentId = data[i]['comment_id'];
 parent = data[i]['parent_comment_id'];

 var obj = getLikesUnlikes(commentId);

 if (parent == "0")
 
 if(data[i]['like_unlike'] >= 1) 
 
 like_icon = "<img src='like.png' id='unlike_" + data[i]['comment_id'] + "' class='like-unlike' onClick='likeOrDislike(" + data[i]['comment_id'] + ",-1)' />";
 like_icon += "<img style='display:none;' src='unlike.png' id='like_" + data[i]['comment_id'] + "' class='like-unlike' onClick='likeOrDislike(" + data[i]['comment_id'] + ",1)' />";
 
 else
 
 like_icon = "<img style='display:none;' src='like.png' id='unlike_" + data[i]['comment_id'] + "' class='like-unlike' onClick='likeOrDislike(" + data[i]['comment_id'] + ",-1)' />";
 like_icon += "<img src='unlike.png' id='like_" + data[i]['comment_id'] + "' class='like-unlike' onClick='likeOrDislike(" + data[i]['comment_id'] + ",1)' />";

 

 comments = "
 <div class='comment-row'>
 <div class='comment-info'>
 <span class='commet-row-label'>from</span>
 <span class='posted-by'>" + data[i]['comment_sender_name'] + "</span>
 <span class='commet-row-label'>at</span> 
 <span class='posted-at'>" + data[i]['date'] + "</span>
 </div>
 <div class='comment-text'>" + data[i]['comment'] + "</div>
 <div>
 <a class='btn-reply' onClick='postReply(" + commentId + ")'>Reply</a>
 </div>
 <div class='post-action'> " + like_icon + "&nbsp;
 <span id='likes_" + commentId + "'> " + totalLikes + " likes </span>
 </div>
 </div>";

 var item = $("<li>").html(comments);
 list.append(item);
 var reply_list = $('<ul>');
 item.append(reply_list);
 listReplies(commentId, data, reply_list);
 
 
 $("#output").html(list);
 );
 

 function listReplies(commentId, data, list) 

 for (var i = 0; (i < data.length); i++)
 

 var obj = getLikesUnlikes(data[i].comment_id);
 if (commentId == data[i].parent_comment_id)
 
 if(data[i]['like_unlike'] >= 1) 
 
 like_icon = "<img src='like.png' id='unlike_" + data[i]['comment_id'] + "' class='like-unlike' onClick='likeOrDislike(" + data[i]['comment_id'] + ",-1)' />";
 like_icon += "<img style='display:none;' src='unlike.png' id='like_" + data[i]['comment_id'] + "' class='like-unlike' onClick='likeOrDislike(" + data[i]['comment_id'] + ",1)' />";

 
 else
 
 like_icon = "<img style='display:none;' src='like.png' id='unlike_" + data[i]['comment_id'] + "' class='like-unlike' onClick='likeOrDislike(" + data[i]['comment_id'] + ",-1)' />";
 like_icon += "<img src='unlike.png' id='like_" + data[i]['comment_id'] + "' class='like-unlike' onClick='likeOrDislike(" + data[i]['comment_id'] + ",1)' />";

 
 var comments = "
 <div class='comment-row'>
 <div class='comment-info'>
 <span class='commet-row-label'>from</span>
 <span class='posted-by'>" + data[i]['comment_sender_name'] + "</span>
 <span class='commet-row-label'>at</span> 
 <span class='posted-at'>" + data[i]['date'] + "</span>
 </div>
 <div class='comment-text'>" + data[i]['comment'] + "</div>
 <div>
 <a class='btn-reply' onClick='postReply(" + data[i]['comment_id'] + ")'>Reply</a>
 </div>
 <div class='post-action'> " + like_icon + "&nbsp;
 <span id='likes_" + data[i]['comment_id'] + "'> " + totalLikes + " likes </span>
 </div>
 </div>";

 var item = $("<li>").html(comments);
 var reply_list = $('<ul>');
 list.append(item);
 item.append(reply_list);
 listReplies(data[i].comment_id, data, reply_list);
 
 
 

 function getLikesUnlikes(commentId)
 

 $.ajax(
 type: 'POST',
 async: false,
 url: 'get-like-unlike.php',
 data: comment_id: commentId,
 success: function (data)
 
 totalLikes = data;
 

 );

 


 function likeOrDislike(comment_id,like_unlike)
 


 $.ajax(
 url: 'comment-like-unlike.php',
 async: false,
 type: 'post',
 data: comment_id:comment_id,like_unlike:like_unlike,
 dataType: 'json',
 success: function (data) 

 $("#likes_"+comment_id).text(data + " likes");

 if (like_unlike == 1) 
 $("#like_" + comment_id).css("display", "none");
 $("#unlike_" + comment_id).show();
 

 if (like_unlike == -1) 
 $("#unlike_" + comment_id).css("display", "none");
 $("#like_" + comment_id).show();
 

 ,
 error: function (data) 
 alert("error : " + JSON.stringify(data));
 
 );
 



 </script>

comment-list.php

<?php
require_once ("db.php");
$memberId = 1;
$sql = "SELECT tbl_comment.*,tbl_like_unlike.like_unlike FROM tbl_comment LEFT JOIN tbl_like_unlike ON tbl_comment.comment_id = tbl_like_unlike.comment_id AND member_id = " . $memberId . " ORDER BY parent_comment_id asc, comment_id asc";

$result = mysqli_query($conn, $sql);
$record_set = array();
while ($row = mysqli_fetch_assoc($result)) 
 array_push($record_set, $row);

mysqli_free_result($result);

mysqli_close($conn);
echo json_encode($record_set);
?>

Im really looking forward for some help, ive been working on this script for a while and this is the last thing I need before launching, thanks in advice!
Here's a picture of a website, just to see how its working

You should write your query in comment-list.php like this

$sql = "SELECT tbl_comment.*,tbl_like_unlike.like_unlike FROM tbl_comment LEFT JOIN tbl_like_unlike ON tbl_comment.comment_id = tbl_like_unlike.comment_id AND member_id = " . $memberId . " ORDER BY tbl_like_unlike.like_unlike DESC, tbl_comment.date DESC";

This will automatically sort your data to have comment with most likes on top and then sort by the newest date. So if a comment has the same amount of likes of another comment, the newest will show up on top.

COMMENT RESPONSE

You can do it in javascript like this:

for (var i = 0; i < data.length; i++) 
 if(i == 0) 
 //put comment at top of page
 else 
 // Put comment in normal place