Page 1 of 1

Last Post by: .... using MySQL

Posted: Sat Apr 25, 2009 5:43 pm
by ninethousandfeet
hello,

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;
 

Re: Last Post by: .... using MySQL

Posted: Mon May 04, 2009 10:03 am
by Bill H
Use "ORDER BY field DESC LIMIT 1" to get the single record with the greatest value.

Re: Last Post by: .... using MySQL

Posted: Mon May 04, 2009 1:08 pm
by jayshields
Bill H wrote:Use "ORDER BY field DESC LIMIT 1" to get the single record with the greatest value.
As an aside - is it quicker to

Code: Select all

ORDER BY `field` DESC LIMIT 1
or is it quicker to

Code: Select all

WHERE `field` = MAX(`field`)
?

Ps. MAX() is a MySQL-specific function isn't it?

Re: Last Post by: .... using MySQL

Posted: Mon May 04, 2009 4:29 pm
by Bill H
Off the top of my head, using the MAX() sounds like two database hits. It would need to hit the db for the MAX(field) calculation, and then again to retrieve the record. If the ORDER BY field is indexed, I would guess the ORDER BY, LIMIT would be faster. But I'm not sure how the MAX() works.

Re: Last Post by: .... using MySQL

Posted: Tue May 12, 2009 2:45 am
by ninethousandfeet
the problem is that i get an error when i use LIMIT 1 because i have this within a repeat region. the page displays all conversations that the logged in user is involved in. i want these conversations to always be ordered from in DESC by date of last post. i also want to be able to retrieve the username and date/time of the most recent post in each conversation. will i be able to do this?