[Tanax]

Yea, here we go again ;)

I've come a good bit. I already have a user system that I've built before, that I'm planning on implanting, so I'm mainly concentrating on getting the forum functions right now.

For example, categories, forums in different categories, and threads.

Now I have a question.
My database structure looks like this:

forums => 
forum_id tinynt(10)
forum_name varchar(25)
forum_desc varchar(255)
forum_parent tinyint(10)
forum_order tinyint(10)
forum_category tinyint(1)

threads =>
thread_id int(10)
thread_name varchar(25)
thread_forumId tinyint(10)
thread_author varchar(25)
thread_sticky tinyint(10)

It's working quite well right now.
I think you understand the basics about the forum table.
If a forum is a category, the row is the value 1, if it's a simple forum it's a 0. If it's a regular forum, the "parent" row is set with the forum_id of the category forum that it's supposed to be under.

The thread table is quite easy aswell.

My question concerns the POSTS in the threads.
Should I store them inside the thread table(?? :S), or should I create a seperate table for the posts, like this?

posts =>
post_id
post_threadId
post_author
post_time
post_content

??

[CMellor]

In my personal opinion, the answer to your question is yes. Forum software like Invision Power Board and phpBB (any maybe even vBulliten) do this method. I also plan on doing it this way when I start to build my own forum.

Hope that helps!

[Tanax]

Okey, thank you! Do you have any suggestions about the forum and thread db structure? Does it look good? Can it be done better?

[bdm]

I'm confused!

You have tinyint(10) but the highest tinyint you can get is an unsigned tinyint which is 225 digits max. :\

[Karl]

In extension to bdm's comment, you should never use tinyint for an ID unless you know there will never be more than 255 (unsigned) IDs. It's also important to understand unsigned, by making a number "unsigned" you prohibit negative values and effectively double the amount of valid IDs that you can store in a single field.

[Tanax]

Originally Posted by Karl

In extension to bdm's comment, you should never use tinyint for an ID unless you know there will never be more than 255 (unsigned) IDs. It's also important to understand unsigned, by making a number "unsigned" you prohibit negative values and effectively double the amount of valid IDs that you can store in a single field.

Okey, so how should it be then?

[Karl]

Make any ID fields unsigned, for example, consider your current thread_forumId field, which is of type tinyint.

As it currently stands, that field can hold 255 values: -128 to 127. However, by making that specific field unsigned, we change that range to 0-255, thus giving us another 128 possible IDs. The same applies for most (if not all?) numeric data types.

[bdm]

So in your case, I'd say it's safe to use unsigned tinyint.

Now that I think about, I have yet to see a forums have anywhere close to 255 forums+categories.

Just another decision you must make.

Also, in your threads table, you should replace thread_author with author_id or something along those lines. Where author_id will link to your users table.

threads
...
author_id (FK)
|
|
users
|
|
user_id (PK)
...

We do this to normalize your data. So for example, you may wish to include a feature where the administrator can change someones username. What would have happened if you hard coded their username to each thread/forums post they made? What are the advantages of linking the threads table to the users table?

Also, shouldn't the thread_sticky column from the threads table be a bit or even tinyint(1)?

[Tanax]

What's unsigned? :S

[bdm]

Originally Posted by Tanax

What's unsigned? :S

Taken from Karl: However, by making that specific field unsigned, we change that range to 0-255, thus giving us another 128 possible IDs. The same applies for most (if not all?) numeric data types.
It's also important to understand unsigned, by making a number "unsigned" you prohibit negative values and effectively double the amount of valid IDs that you can store in a single field.

[Tanax]

Oh, you edited your post above my last post :D
Anyways, thanks for the tip about the author, much better, so big thanks :D

And yes, sticky could be only 1, since it's only 1 number.. :)

And the "unsigned", I change that in phpmyadmin huh??

[xenon]

Originally Posted by Tanax

And the "unsigned", I change that in phpmyadmin huh??

Yep, it's the 'Attributes' field.

[Tanax]

Originally Posted by xenon

Yep, it's the 'Attributes' field.

Yea hehe, I found it :) Thanks

[Tanax]

Mhm, now I'm having another problem...

public function newPost($details, $thread) {
            
            $sql = sprintf("    INSERT INTO
                                    `%s`
                                SET
                                    `%s` = NOW(),
                                    `%s` = '%s',
                                    `%s` = '%s',
                                    `%s` = '%s'",
                                    
                                    
                                $this->db->table['posts'],
                                $this->db->col['post_time'],
                                $this->db->col['post_content'],
                                $details['content'],
                                $this->db->col['post_author'],
                                $details['author'],
                                $this->db->col['post_thread'],
                                $thread);
                                
            $query = $this->db->query($sql);
            
            if($query) {
                
                return true;
                
            }
            
            return false;
            
        }

And then this:

$t = $_GET['t'];
$newpost = array();
        
        $newpost['author'] = $_POST['author'];
        $newpost['content'] = $_POST['post'];
        
        if($tanaxia['forum']->newPost($newpost, $t)) {
            
            echo 'Reply posted!';
            
        }
        
        else {
            
            echo 'Couldn\'t post reply..';
            
        }

It inserts the data, and it echoes out that "reply posted".
I check the thread, but the post isn't there!

So I check the database, and sure enough, the post EXISTS in the posts table. But the "post_thread" row value is set to 0 for some reason.

So it's.. yea, no idea what's wrong :S

[Tanax]

Anyone? =//

[dschreck]

Originally Posted by Tanax

Anyone? =//

I'd have to see the code that pulls the post from the DB

[bdm]

Like dschreck said, we'd need to see all of the code that's being used to insert a new post.

[Tanax]

You already see all the code? :S

newpost.php

<?php

/**
 * @author Tanax
 * @copyright 2007
 */

    include('includes/config.php');
    
    $t = $_GET['t'];
    
    if(!$_POST['submit']) {
        
        ?>
        
            <form action="newpost.php" method="POST">
            Author:<br />
            <input type="text" name="author" /><br />
            Post:<br />
            <input type="text" name="post" /><br />
            <input type="submit" value="Post" name="submit" />
            </form>
        
        <?php
        
    }
    
    else {
        
        $newpost = array();
        
        $newpost['author'] = $_POST['author'];
        $newpost['content'] = $_POST['post'];
        
        if($tanaxia['forum']->newPost($newpost, $t)) {
            
            echo 'Reply posted!';
            
        }
        
        else {
            
            echo 'Couldn\'t post reply..';
            
        }
        
    }

?>

forum.php (the class)

public function newPost($details, $thread) {
            
            $sql = sprintf("    INSERT INTO
                                    `%s`
                                SET
                                    `%s` = NOW(),
                                    `%s` = '%s',
                                    `%s` = '%s',
                                    `%s` = '%s'",
                                    
                                    
                                $this->db->table['posts'],
                                $this->db->col['post_time'],
                                $this->db->col['post_content'],
                                $details['content'],
                                $this->db->col['post_author'],
                                $details['author'],
                                $this->db->col['post_thread'],
                                $thread);
                                
            $query = $this->db->query($sql);
            
            if($query) {
                
                return true;
                
            }
            
            return false;
            
        }

showthread.php

<?php

/**
 * @author Tanax
 * @copyright 2007
 */

    include('includes/config.php');
    
    $t = $_GET['t'];
    
    if(!isset($t)) {
        
        echo 'Please specify a valid thread';
        
    }
    
    else {
        
        $thread = $tanaxia['forum']->getThreadInfo($t);
        $forum = $tanaxia['forum']->getForumInfo($thread['thread_forum']);
        $category = $tanaxia['forum']->getForumInfo($forum['forum_parent']);
        $posts = $tanaxia['forum']->getPosts($thread['thread_id']);
        
        echo '<a href="index.php">Index</a> -> <a href="forumdisplay.php?f='.$category['forum_id'].'">'.$category['forum_name'].'</a> ';
        echo '-> <a href="forumdisplay.php?f='.$forum['forum_id'].'">'.$forum['forum_name'].'</a> ';
        echo '-> <a href="showthread.php?t='.$thread['thread_id'].'">'.$thread['thread_name'].'</a>';
        
        echo '<center>';        
        
        foreach($posts as $post) {
            
            echo '<table width="900" border="1">';
            echo '<tr>';
            echo '<td colspan="3"><font size="2">Posted '.$post['post_time'].'</font></td>';
            echo '</tr>';
            
            echo '<tr>';
            echo '<td colspan="2" width="30%">'.$post['post_author'].'</td>';
            echo '<td>'.$post['post_content'].'</td>';    
            echo '</tr></table><br />';
            
        }
        
        echo '<a href="newpost.php?t='.$t.'">Post reply</a>';
        echo '</center>';
        
    }

?>

[dschreck]

Oh.

 <form action="newpost.php" method="POST">

try changing that to:

 <form action="newpost.php?t=<?=$_GET['t'];?>" method="POST">

(Using PHP short tags)

[Tanax]

Originally Posted by dschreck

Oh.

 <form action="newpost.php" method="POST">

try changing that to:

 <form action="newpost.php?t=<?=$_GET['t'];?>" method="POST">

(Using PHP short tags)

Oh lol!
I didn't think I needed that number, but thanks :D