Working on a sql query getting one of the results wrong

Discussion in 'Web Design and Development' started by Cabbit, Jul 8, 2008.

  1. Cabbit macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #1
    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:
    <?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
    Code:
    	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

    Code:
    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

    Code:
    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
    
     
  2. angryboffin macrumors newbie

    Joined:
    May 7, 2008
    Location:
    London
    #2
    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):


    Code:
    
    $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
     

Share This Page