Working on a sql query getting one of the results wrong

[Cabbit]

The idea of this script is to replace that last post part of the tables with something dynamic so what i can just delete spam quickly and topics and replys and posts quickly without laborious re writing of the last posts and stuff to make mantanece really easy and pain free. Now once the date part is sorted out this will be a great.

<?php
	include("include/session.php");
	$sql = "SELECT
				topic.`title`,
				post.`id` AS `id_post`,
				post.`subject` AS `post_subject`,
				post.`username`,
				post.`date` AS `post_date`,
				reply.`reply-id`,
				reply.`subject`,
				reply.`username`,
				reply.`date` AS `reply_date`,
				GREATEST(post.`date`, reply.`date`) AS `latest_date`
			FROM
				`forum-topics`
			AS
				`topic`
			LEFT JOIN
				`forum-posts`
			AS
				`post`
			ON
				post.`post-id` = topic.`id`
			LEFT JOIN
				`forum-reply`
			AS
				`reply`
			ON
				reply.`reply-id` = post.`id`
			WHERE
				topic.`id` = '3'
			GROUP BY
				GREATEST(post.`date`, reply.`date`)
			ORDER BY
				topic.`id`
			LIMIT 1
			"; 
///start result//
$result = mysql_query($sql) or die("Error connecting to database");
while ($row = mysql_fetch_assoc($result)) {
	echo $row['id_post'];
	echo "<br />";
	echo $row['post_subject'];
	echo "<br />";
	echo $row['username'];
	echo "<br />";
	echo $row['latest_date'];
}
?>

this php code outputs this as a result

27
Posting
JerryLouise
2008-07-06

when it would be

27
Posting
JerryLouise
2008-07-07

from the following database info


reply table

	id	reply-id	username	date	subject	post
			56	27	JerryLouise	2008-07-06	cookies	<p>^_^ there will be cookies soon, and rewards for...
			62	27	Sissy	2008-07-05	 	<p>Thanks, JerryLouise.  I love the idea of collec...
			78	27	abzeb	2008-07-04	yayyayyaya	i cant wait to make my own nersery i should be so ...
			79	27	JerryLouise	2008-07-03	 	it will take me a wile even with my new laptop. i ...
			80	27	abzeb	2008-07-02	time to wait	i can wait but very impatintly tho lol

sigh oth...
			117	27	JerryLouise	2008-07-07	Test	^_^ yip the stories are great, check out the up an...

post table

id	post-id	username	date	subject	post	lastpost	lastposttime	views
27	3	Sissy	2008-07-01	Posting	<p>I'd like to make a suggestion.  In order t...	JerryLouise	2008-07-01	81

topic table

id	category_id	title	about	lastpost	lastposttime	lasttopic	lastid
3	1	Suggestions	Feel free to post any suggestions you have for the...	JerryLouise	0000-00-00	Posting	27

 

[angryboffin]

I may be completely misunderstanding you

but this SQL will pull out the latest (post|reply)'s post. If there's more than one with the latest timestamp, it will pull one out randomly (that's the danger of using LIMIT 1 with an ORDER BY on something that's not unique):

$sql = "SELECT 
                post.`id` AS `id_post`, 
                post.`subject` AS `post_subject`, 
                post.`username`, 
                GREATEST(post.`date`, reply.`date`) AS `latest_date` 
            FROM 
                `forum-topics` 
            AS 
                `topic` 
            LEFT JOIN 
                `forum-posts` 
            AS 
                `post` 
            ON 
                post.`post-id` = topic.`id` 
            LEFT JOIN 
                `forum-reply` 
            AS 
                `reply` 
            ON 
                reply.`reply-id` = post.`id` 
            WHERE 
                topic.`id` = '3' 
            ORDER BY 
                GREATEST(post.`date`, reply.`date`) DESC
            LIMIT 1

As I'm not near a computer with mySQL (I'm in bed), I can't test out the behaviour of GREATEST when it comes to NULLs, so your outer joins may also cause issues.
So, apart from the fact that it may not be what you want and might fail for posts without replies, it's perfect.

Regards (& Night night),

Joshua