Member Avatar
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.
Member Avatar
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?

Member Avatar
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.

Member Avatar
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?

Member Avatar
iLikePHP

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

Member Avatar
iLikePHP

They're the only dates I have btw.

Member Avatar
diafol

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

Member Avatar
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`
Member Avatar
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. :)

Member Avatar
iLikePHP

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

Member Avatar
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.

Member Avatar
iLikePHP

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

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

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

Member Avatar
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

Member Avatar
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

Member Avatar
iLikePHP

test is still #1

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.

Member Avatar
iLikePHP

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

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
Member Avatar
iLikePHP

Didn't work... :/