PHP Custom Forums - Bumping Topics

[iLikePHP]

I put it all here, it shows as a paste... mind looking and maybe editing and giving me your link? :)

public function getTopics($cid)
{
    require("database.php");
    $cat_id = strip_tags($cid);
    $query = $db->query("SELECT DISTINCT * FROM `forum_topics` INNER JOIN `forum_replies` ON `forum_topics`.`topic_id`=`forum_replies`.`reply_id` GROUP BY `forum_topics`.`topic_id` ORDER BY coalesce(`forum_replies`.`posted_on`, `forum_topics`.`posted`) DESC");
    if ($query->rowCount()==0)
    {
        return "<p>No topics, yet...</p>";
    }else{
        $returns = "<table class='table table-striped'>";
        $returns .= "<tr><th></th><th>Posted</th><th>Replies</th></tr>";
        while ($row = $query->fetch(PDO::FETCH_ASSOC))
        {
            $returns .= "<tr><td><a href='topic?tid=".$row['topic_id']."'><h4>{$row['topic_title']}</h4></a><p>".substr($row['topic_body'], 0, 150)."...</p></td><td><h5>".User::getInformation($row['topic_starter'], "user_id", "username")."</h5><p>on {$row['posted']}</p></th><td><h4>N/A</h4></td></tr>";
        }
        $returns .= "</table>";
        return $returns;
    }
}


// Output: http://www.seriouslycode.com/category?cid=1
// "test" should be on top as it has the most recent reply... I am trying to make the "bumping" feature.

[diafol]

This should have gone in the other thread - no need to start a new one.

Your code show that test2 is the most recent reply:

test2 - on 2013-10-18 03:04:11
test - on 2013-10-18 02:31:12

So it seems to be working fine. Am I missing something?

I sort of get it that one of the coalesce members will be null for a thread starter or for a reply. So is it an issue with this?

[iLikePHP]

^ I was told by a person who started with mike on chat to post, and I thought he meant new thread... ^

They're not reply times actually, they're post times...
The most recent replies are below:
Test2 - 2013-10-18 04:39:25
Test - 2013-10-18 06:4111

Sorry about me not including this, forgot that I never had reply time on the table.

[diafol]

Coalescing will return the first non-NULL value - are you sure that the dates you think are being used to sort are the ones being used?

[iLikePHP]

Could you maybe improve my code? Using what I already have.

[iLikePHP]

They're the only dates I have btw.

[diafol]

Without an example of the data in the tables of the SELECT query, no.

[iLikePHP]

I can do that. :)

http://imgur.com/J0GwkqB,s2uAKPC

[diafol]

Well that doesn't really help as it's the data I typed previously. I was wondering more about the coalesce output.

Can you run and output the coalesce bit and see what it gives?

SELECT *, coalesce(`forum_replies`.`posted_on`, `forum_topics`.`posted`) as coal FROM `forum_topics` INNER JOIN `forum_replies` ON `forum_topics`.`topic_id`=`forum_replies`.`reply_id` GROUP BY `forum_topics`.`topic_id`

[iLikePHP]

http://imgur.com/EriP0wb

[diafol]

sigh

Now that really doesn't help. We need to see the result of the coalesce field.
If you could just copy/paste the result here instead of posting an image link - much easier. :)

[iLikePHP]

Can't, I use a programme to get my stuff...

[diafol]

Can't you at least provide the data for the rows from the SQL I provided...

topic_title | coal

I don't see why using a programme (a GUI?) prevents you from pasting info here.

[iLikePHP]

What do you mean by coal? I am new to this stuff.

[Dani]

This is just off the cuff, but maybe something like this:

select forum_topics.*, MAX(posted) AS last_post LEFT JOIN forum_replies ON (forum_topics.topic_id = forum_replies.topic_id GROUP BY forum_topics.topic_id ORDER BY last_post DESC

[Dani]

Sorry, my syntax is off, but that's the general idea.

[iLikePHP]

            $query = $db->query("SELECT forum_topics.*, MAX(posted) AS last_post LEFT JOIN forum_replies ON (forum_topics.topic_id = forum_replies.topic_id GROUP BY forum_topics.topic_id ORDER BY last_post DESC");

and

            $query = $db->query("SELECT forum_topics.*, MAX(posted) AS last_post LEFT JOIN forum_replies ON (forum_topics.topic_id = forum_replies.topic_id) GROUP BY forum_topics.topic_id ORDER BY last_post DESC");

No output now...
not even in mysql workbench

[diafol]

You

What do you mean by coal? I am new to this stuff.

Me

Can't you at least provide the data for the rows from the SQL I provided...

The SQL I provided...

SELECT *, coalesce(`forum_replies`.`posted_on`, `forum_topics`.`posted`) as coal FROM `forum_topics` INNER JOIN `forum_replies` ON `forum_topics`.`topic_id`=`forum_replies`.`reply_id` GROUP BY `forum_topics`.`topic_id`

Note the field 'coal'

Nevermind

[iLikePHP]

test is still #1

[Dani]

My queries were pretty much just pseudocode that I typed off-the-cuff to give you a general idea. It's not actually valid MySQL.

[iLikePHP]

I would appreciate a full query, as I am only good at basic queries.

[Dani]

Something similar to

SELECT forum_topics.*, MAX(forum_replies.posted) AS last_post
LEFT JOIN forum_replies ON (forum_topics.topic_id = forum_replies.topic_id)
GROUP BY forum_topics.topic_id
ORDER BY last_post DESC

[iLikePHP]

Didn't work... :/