Last Post by: .... using MySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ninethousandfeet
Forum Contributor
Posts: 130
Joined: Tue Mar 10, 2009 4:56 pm

Last Post by: .... using MySQL

Post 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;
 
Last edited by Benjamin on Mon May 04, 2009 11:44 am, edited 1 time in total.
Reason: Changed code type from text to php.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

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

Post by Bill H »

Use "ORDER BY field DESC LIMIT 1" to get the single record with the greatest value.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

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

Post 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?
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

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

Post 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.
ninethousandfeet
Forum Contributor
Posts: 130
Joined: Tue Mar 10, 2009 4:56 pm

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

Post 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?
Post Reply