Simple Forum logic

[TheLastRider]

Hey all. I've been making my own forum application and I was thinking, "How do I make it so that new replies/ new topics will show up at the top of the page?"

Right now, I'm sorting the data by;

Sticky - Separate selection than the regular datas. So example would be for stickies I'd do

SELECT id, title, timestamp FROM topics WHERE category = ? ORDER BY id AND sticky DESC

Then for a regular topic that isn't sticky, I'd do

SELECT id, title, timestamp FROM topics WHERE category = ? AND sticky = ? ORDER BY id DESC

This one is a little similar, but not completely. sticky = ? is specified to 0 which means that the topic is not sticky and is a regular topic. So when ever someone posts a topic the sticky topics will always stay at the top while the regular topics are under the sticky topics.

Example:

---

Ebola outbreak - (35 posts) - Sticky
Cancer awareness month - (9 posts) - Sticky
-----------
Have you heard of this new disease yet? - (1 post) - regular topic
I just ate something poisonous - (0 posts) - regular topic

After a few hours

[CODE]

Ebola outbreak - (35 posts) - Sticky

Cancer awareness month - (9 posts) - Sticky

I thought this was fake - (0 posts) - regular topic
Nothing is done with these diseases - (12 posts) - regular topic
Have you heard of this new disease yet? - (5 post) - regular topic
Growing fungous - (0 posts) - regular topic
I just ate something poisonous - (0 posts) - regular topic[/CODE]

So basically the logic to this forum is to bump what ever is new or what ever gets replied to the top of the page while old topics get pushed down. The sticky topics aren't affected at all because the sticky topics has a separate SELECT to it.

How would I go about this? I don't want to post my codes because it's 2000 + lines of code. Just want to get some insight or reviews on how I should do this.

[Weedpacket]

What is a topic's timestamp?
If individual posts have timestamps, then you could order topics in order of decreasing maximum timestamp of their posts.

[NogDog]

That's what I was thinking, and you should be able to get everything in one query by ordering by sticky first, then max timestamp, e.g. (made up table/column names):

select
  topic.sticky,
  (select max(post.timestamp) from post where topic_id = topic.topic_id) as last_post_time,
  <other fields of interest>
from topic
order by topic.sticky desc, last_post_time desc

[TheLastRider]

Weedpacket;11043541 wrote:

What is a topic's timestamp?
If individual posts have timestamps, then you could order topics in order of decreasing maximum timestamp of their posts.

The timestamp is used with the time(); function. I'm using it because I find it a lot easier to use rather than the typical date time. I know that time(); only counts from 1990 +, but how is it any relevant if my website was created in 2014? It still gives the same exact time as the server's.

But any who, yes. Each topic has their own timestamp. When an author creates their topic, it will automatically assigns the time(); function to the topic.

NogDog;11043545 wrote:

That's what I was thinking, and you should be able to get everything in one query by ordering by sticky first, then max timestamp, e.g. (made up table/column names):

select
  topic.sticky,
  (select max(post.timestamp) from post where topic_id = topic.topic_id) as last_post_time,
  <other fields of interest>
from topic
order by topic.sticky desc, last_post_time desc

Thanks for the suggestion. So if I use this query line (different queries), then I don't have to have 2 separate statements?

The new logic

<?php
$sql = $mysqli->prepare("SELECT topic.sticky, (SELECT max(post.timestamp) FROM post WHERE topic_id = topic.topic_id) as last_post_time FROM topic ORDER BY topic.sticky DESC, last_post_time DESC");
$sql->execute();
$sql->store_result();

if($sql->num_rows) {
	echo "Topic is sticky";
} else {
	echo "Topic is regular";
}
?>

Scratches head I think that's how it goes..

The old logic

<?php
$sql = $mysqli->prepare("SELECT id, title, timestamp FROM topics WHERE category = ? ORDER BY id AND sticky DESC");
$sql->bind_param("d", $category);
$category = filter_var($_GET['id'], FILTER_SANITIZE_URL);
$sql->execute();
$sql->store_result();

if($sql->num_rows) {
	echo "This is for stickys";
}

$sql2 = $mysqli->prepare("SELECT id, title, timestamp FROM topics WHERE category = ? AND sticky = ? ORDER BY id DESC");
$sql2->bind_param("d", $category2, $sticky);
$category2 = filter_var($_GET['id'], FILTER_SANITIZE_URL);
$sticky = filter_var(0, FILTER_SANITIZE_URL);
$sql2->execute();
$sql2->store_result();

if($sql2->num_rows) {
	echo "This is for regular topics";
}
?>

Let me know if I'm doing it wrong. I'm not sure if I get the point.

[Weedpacket]

TheLastRider wrote:

The timestamp is used with the time(); function. I'm using it because I find it a lot easier to use rather than the typical date time. I know that time(); only counts from 1990 +, but how is it any relevant if my website was created in 2014? It still gives the same exact time as the server's.

But any who, yes. Each topic has their own timestamp. When an author creates their topic, it will automatically assigns the time(); function to the topic.

I guess I should have asked what it meant. I know what a timestamp is (whatever format you're using, as long as it sorts sensibly). And I didn't ask if each topic had its own timestamp (if it didn't it wouldn't have a field); I asked if each post had its own timestamp. Because if it did, then the query I described and NogDog gave would work.

[TheLastRider]

Weedpacket;11043553 wrote:

I guess I should have asked what it meant. I know what a timestamp is (whatever format you're using, as long as it sorts sensibly). And I didn't ask if each topic had its own timestamp (if it didn't it wouldn't have a field); I asked if each post had its own timestamp. Because if it did, then the query I described and NogDog gave would work.

Yup. Each post has it's own timestamp. The posts are in a different table. I just grab them using the category ID and since each category has its own ID. It gets assigned to the topic that's posted in it.

So let's say this is my category table

+----+---------------+------------+
| ID | Category Name | Timestamp  |
+----+---------------+------------+
| 1  | Current News  | 1414051991 |
+----+---------------+------------+
| 2  | Announcements | 1414014753 |
+----+---------------+------------+
| 3  | Help          | 1414041469 |
+----+---------------+------------+
| 4  | Off-topic     | 1414007145 |
+----+---------------+------------+

While the topics table looks like this

+----+-------------+---------------+------------+
| ID | Category ID | Topic Title   | Timestamp  |
+----+-------------+---------------+------------+
| 1  | 3           | Where do I..  | 1414051991 |
+----+-------------+---------------+------------+
| 2  | 2           | Today we..    | 1414014753 |
+----+-------------+---------------+------------+
| 3  | 1           | In Florida..  | 1414041469 |
+----+-------------+---------------+------------+
| 4  | 3           | I lost my..   | 1414007145 |
+----+-------------+---------------+------------+
| 5  | 1           | A snake ate.. | 1414097134 |
+----+-------------+---------------+------------+

NOTE: Some of the dates are in the future and some are in the past, this is just an example of how I do it.

You can see that for both, category and topic; they both have their own ID that's primary and auto incremented. For the topics table, I store the category ID so that when someone goes to let's say category.php?id=3, it displays every topic that has the category ID 3 in it. Then when they see let's say the topic title Where do I.. and they click on the link. It leads them to topic.php?id=1 since topic.php?id=1's category ID is 3 which they were just on.

That's how I'm doing my forum system.

[TheLastRider]

Also, I'm not sure if I answered your question yet, but for each category. Since I ordered them by descending. All of the topics with newer timestamp will automatically show at the top. The only actual question on my original post was how do I bring them up if I have this set up?

Do I assign a new timestamp column to it? So like the topic would have its original timestamp and another timestamp. So each time someone posts a comment to the topic, it updates the second timestamp instead of updating the original timestamp.

I'm not sure if I'm making any sense at all.

[Bonesnap]

TheLastRider;11043567 wrote:

Do I assign a new timestamp column to it? So like the topic would have its original timestamp and another timestamp. So each time someone posts a comment to the topic, it updates the second timestamp instead of updating the original timestamp.

You wouldn't have to since you're sorting based on the latest post's timestamp (for that topic).

I created custom forum software a couple years ago and went through this too (had I not suffered a large hard drive failure earlier this year I could post some of my code and database structure). Off the top of my head from what I remember, I had a timestamp on each topic of when it was created, but that was the only timestamp related to the topic. The sorting of them depended on the timestamp of the latest post in each of those topics (basically what Weedpacket was saying).

I also remember having a column in my topic table that related to the "status" of the topic which I used to cover stickied topics, because you can have regular topics, locked topics, stickied topics, and locked stickied topics. You should also consider topics that have been "soft deleted" - essentially hidden by the moderation team but haven't actually been deleted (hard deleted). They're still viewable by the moderation team (and sort accordingly for them). That is, of course, if that's something you'll be including.

It's really interesting reading this thread because it brings me back. I learned a lot creating my forum (which was ultimately abandoned), so hopefully you do too! 🙂

[TheLastRider]

Bonesnap;11043571 wrote:

You wouldn't have to since you're sorting based on the latest post's timestamp (for that topic).

I created custom forum software a couple years ago and went through this too (had I not suffered a large hard drive failure earlier this year I could post some of my code and database structure). Off the top of my head from what I remember, I had a timestamp on each topic of when it was created, but that was the only timestamp related to the topic. The sorting of them depended on the timestamp of the latest post in each of those topics (basically what Weedpacket was saying).

I also remember having a column in my topic table that related to the "status" of the topic which I used to cover stickied topics, because you can have regular topics, locked topics, stickied topics, and locked stickied topics. You should also consider topics that have been "soft deleted" - essentially hidden by the moderation team but haven't actually been deleted (hard deleted). They're still viewable by the moderation team (and sort accordingly for them). That is, of course, if that's something you'll be including.

It's really interesting reading this thread because it brings me back. I learned a lot creating my forum (which was ultimately abandoned), so hopefully you do too! 🙂

Thanks and yes. I have already made these columns available for admins and moderators only. I've already thought of soft deleting the topics because if you hard delete a topic, you really can't decide if someone was violating the Terms of Service or not, so I've decided from the beginning of this application that it's best to make some options for admins in case someone decides to be rude and violate the terms.

I've also made separate columns for the sticky status. My table has admin_only, sticky, locked, moved, and delete columns.

The moved option is only available for topics that don't belong in the rightful category and sometimes might be moved because of flooding or spamming. I don't want to soft delete those because those will just clog up the moderating process. So I made a category named "Trash" for this type of situation. In the "Trash" category, all of those flood/spam topics won't allow any users to post in them and will sooner or later be hard deleted if it starts to have a lot of topics in it.

I actually also have another column that stores the category ID twice. One is to distinguish which category the topic belongs to and the other is for the moved option. This is so that I can restore the topic to the original category if I feel like I made a mistake.

The other options are explained by their names. Sticky is to distinguish if it's a sticky topic, locked is to distinguish if the topic is locked by an admin or moderator, and delete is a soft delete option which is only for admins or moderators. Again, when I soft delete, it's only shown on an admin's or moderator's screen. A regular user won't be able to see topics that are soft deleted.

The "admin_only" column refers to both category and topic. I made the column so that admins or moderators can create their own category or topic and hide it from a regular user or discuss something on a private topic without a regular user knowing about it. The only thing that a user would notice is if the ID in the URL is larger than average and there is only a small amount of topics in all categories. But again, since users actually won't be able to distinguish if a topic exists or not, the ID in the URL won't really matter. I made it so that the topic.php page displays "Topic doesn't exist" if it's hidden or soft deleted to a regular user. On the other hand, admins and moderators will see everything and the message "Topic doesn't exist" will be ignored and the actual data will be displayed to them.

I'm not sure if this is a good idea, but give me your thoughts.