i am trying to add a column that retrieves who made the last post in a conversation and also the datetime of that post. i tried LIMIT 1 and DISTINCT and both are not working... i have all conversations involving a user in a repeat region so i think i also need to create a different SQL statement to get the most recent commenter & date, but i am stuck...
as an example, i want it just how this website uses the "Last Action:" column in the topic lists for each category.
here is the SQL that retrieves all conversations involving this user. any ideas?
Code: Select all
$maxRows_getComment = 10;
$pageNum_getComment = 0;
if (isset($_GET['pageNum_getComment'])) {
$pageNum_getComment = $_GET['pageNum_getComment'];
}
$startRow_getComment = $pageNum_getComment * $maxRows_getComment;
$var5_getComment = "-1";
if (isset($_GET['user_id'])) {
$var5_getComment = $_GET['user_id'];
}
mysql_select_db($database_connUser, $connUser);
$query_getComment = sprintf("SELECT testComment.user_id, testComment.comment_id, testComment.comment_username, testComment.`comment`, testComment.comment_title, testComment.comment_date, DATE_FORMAT(comment_date, '%%c/%%e/%%y\r %%h:%%i %%p') AS dateOFcomment, userTable.user_id, userTable.username FROM testComment INNER JOIN userTable ON testComment.user_id = userTable.user_id OR testComment.comment_id = userTable.user_id WHERE userTable.user_id = %s GROUP BY testComment.comment_title", GetSQLValueString($var5_getComment, "int"));
$query_limit_getComment = sprintf("%s LIMIT %d, %d", $query_getComment, $startRow_getComment, $maxRows_getComment);
$getComment = mysql_query($query_limit_getComment, $connUser) or die(mysql_error());
$row_getComment = mysql_fetch_assoc($getComment);
if (isset($_GET['totalRows_getComment'])) {
$totalRows_getComment = $_GET['totalRows_getComment'];
} else {
$all_getComment = mysql_query($query_getComment);
$totalRows_getComment = mysql_num_rows($all_getComment);
}
$totalPages_getComment = ceil($totalRows_getComment/$maxRows_getComment)-1;