Message Board in PHP with MySQL

[Neo]

I want to create a message-board/forum (for use among my friends,classmates etc). Most of my design ideas are from this board . But i have a few questions

1) Would it be more efficient to store long text entries/messages in a database field or in a text file?
2) If i use a database for storing the messages what data type would be best?
3) What is the difference between the 'char' and 'varchar' types in SQL? does char occupy fixed size and varchar occupy only space depending on its contents?
4) How do i automatically assign ids to a message etc? For e.g. in this forum each new thread is assigned a unique individual id number, how do i do this? is there any data type in SQL which does this? (i'm not referring to the 'unique' clause but on the automatic number generation)

[AGI1122]

Wow, finally my area of specialty... PHP/MySQL forums! I have worked on YaBBSE and it's predecessor, as well as made my own forum.

1) It is better to store it in a database... it's faster and more effeicent for alot of data. Plus lots of hosts don't like flatfile bulletin boards and will toss you if they catch you useing one. So database is the best way to go.
2) Well for the message itself I use TEXT because of the ammount of data it can store... not too much... not too little.
3) Well CHAR is generally more flexible, it can use BINARY, ASCII, and UNICODE, while VARCHAR can only use BINARY. varchar and char allow you to choose the size it occupies which is also the maximum size of the data than can be in the field. For instance varchar(255) and char(255) would only allow you to store 255 bytes of data. But char(255) would let you store 2 more types of data(ASCII and UNICODE).
4) Create the id field to be the primary key, and give it the property of auto_increment. Then when you insert data into the database it automatically assigns an id for you. I recommend using an INT field for the id.

If you have any more questions feel free to ask me as it is my best area when it comes to programming.

[Curufir]

3) Yes. Although it's DB specific a CHAR type will be filled to the correct amount, whilst VARCHAR will reduce to the size of the string. This means VARCHAR is better for space (Obviously), but most of the time CHAR is better for insert/modify speed.

[Neo]

Thanks a lot , I'm real glad that this is your area of speciality. I only hope by the time I've got my forum working you'll not me mad at me

Anyway i used the 'auto_increment' property (i was using the id as primary key) and it works nicely but i was just wondering if we always need to insert a blank entry ('') each time in that field as shown,

Code: Select all

INSERT INTO tab1 values('',"field1","field2");

or is there another way of making the field auto-increment when inserting? another thing i want to know about these auto-inc fields is, how can i specify the starting value and range over which it valid? the ENUM and SET constraints require me to specify the complete set which could be extremely huge for e.g.

Code: Select all

SET(1,2,3..........65536);

or is there a simpler way? (i could'nt find anything else in the docs)

Also how much web space do you think i'll need to host this board? its going to be for just a few of my friends etc and others in the locality.
btw how much does this forum take up ?

[AGI1122]

Anyway i used the 'auto_increment' property (i was using the id as primary key) and it works nicely but i was just wondering if we always need to insert a blank entry ('') each time in that field as shown,

Well if you insert the data that way then yes. But if you specify fields you won't have to. For instance:

Code: Select all

INSERT INTO tab1 (field,another_field) VALUES ('field1','field2');

or is there another way of making the field auto-increment when inserting?

Well not when inserting... but you could select the largest id from the table useing MAX in the select... although there isn't much point in that if you can automatically make it increment...

another thing i want to know about these auto-inc fields is, how can i specify the starting value and range over which it valid?

Well it's range changes depending on the field type you put for the primary field. It's first value is always 1... and it goes up the max ammount of data the field type can hold. For instace if you used a tiny int the range would be 1 - 255. So basically the range is changed by the field type.

Also how much web space do you think i'll need to host this board? its going to be for just a few of my friends etc and others in the locality.
btw how much does this forum take up ?

This is extremely hard to determine, the ammount it takes up varies depending on ammount of members, posts, boards, logs, etc. Also different message board system use different ammounts of space... so this isn't something you can get a definate answer on.

For instance I have a dump of mega-tokyo from about a year ago which is 15MB in size. And that was back when this board had around 22,000 posts. Now it's posts has double from that in this year, so I am sure it's much larger. The agigames.com forums are 1.5MB(2000 posts) MB and my CBB board is 1MB(2000 posts). As you can see 1/2 a MB larger than the other board but with around the same ammount of posts.

[Neo]

@chris: Do you limit the number of posts in your board? I mean you say that your boards have about 2000 posts each is this an upper limit imposed by you? If so what do you do on hitting the upper limit? delete old threads?

[AGI1122]

The limit of my board is based on the field size. Since I use int the most posts my board can have is 4,294,967,295. So as you can see it will be a really long time before the posts have to be cleared out. 2,000 is just around the ammount I have on the board now... not the limit.

I don't limit it... but the field I chose limits it. 4,294,967,295 is the largest int you can have... but if you go with something bigger than an int your limit will be that much larger.

Also if you use unsigned fields it will double the ammount of id's you can have for a primary key. Basically turning the field into unsigned makes it so you can't have negative values... but id's can't have negative values anyway so you don't lose any id's so putting unsigned doubles the ammount of available id's you can use.

[Neo]

Some design questions now,
i was thinking of how to store separate threads and have come up with 2 ideas i was wondering which would be better, or if i should use something else altogether. Here are the 2 ideas

1 ) Use a single 'thread' table which assigns ID's and stores the topics only, the messages would then be stored in another common 'message' table with this 'thread_id' to identify messages belonging to a thread.

2 ) Use the same 'thread' table as above but create a table for each thread using the 'thread_id' or some other unique key. In each thread table store all the messages/replies for that message.
This second method seems nice (as searching would be very fast IMHO) but i was wondering if there would be any overheads etc. in this if i'm going to create a new table for each thread.

I was also wondering about message storing in another way. If i store the name and email of the person who posted the reply with each message would'nt this be a waste of space? instead i thought of storing a 'user_id' with each reply. If i do this then how do i store messages posted by 'guests'?
Or is there a better way?

[AGI1122]

The first idea is better because loading a page with alot of topics would pretty much kill(by kill I mean extremely slow... not kill the board/site) the board having to read that ammount of tables in 1 page load. And the first method is the way my message board and most other message boards work.

Well the way my message board stores the user info is that if the user id is 1 then the post is by a guest... if it's higher then 1 I then load the member who's id it belongs to.

You can store their info in a seperate table... although that means you will have to have an extra query to load the guest name/email.

[Neo]

The first idea is better because loading a page with alot of topics would pretty much kill(by kill I mean extremely slow... not kill the board/site) the board having to read that ammount of tables in 1 page load. And the first method is the way my message board and most other message boards work.

Why would i need to load a page with a lot of topics?? (I can't think of any reason i would need to load several topics at once). I think the only time many topics have to be displayed will be when the user just enters the board, and at this time the 'thread' table (with only the topics) need be displayed. What do you think?

Well the way my message board stores the user info is that if the user id is 1 then the post is by a guest... if it's higher then 1 I then load the member who's id it belongs to.

You can store their info in a seperate table... although that means you will have to have an extra query to load the guest name/email.

If its '1' then how do i know which guest's post it is? How do i identify the right guest from the 'guest' table?

[AGI1122]

Why would i need to load a page with a lot of topics?

On a topic index where it lists the topics...

Like: http://www.mega-tokyo.com/forum/index.php?board=6

That lists all topics in that board... and if it where to use a seperate table for each topic it would cause it to be extremely slow.

If its '1' then how do i know which guest's post it is? How do i identify the right guest from the 'guest' table?

Well my board doesn't have a guest table, it stores their username and email in the post itself. If it's 1 then it uses that info as the guest info... otherwise it uses what's in the members table.

[Neo]

Oh! so you have a name, email and id field for each post, i was thinking of some way of avoiding these name and email fields from the 'post' table and instead using the values from the 'user_info' table but couldn't think of any way to do this for guest posts.
One possible method is to just display the name "Guest" with no email address for any post by a guest. (Obviously this is not a very good idea)
So does anyone have any better ideas to handle this ??

[Candy]

Oh! so you have a name, email and id field for each post, i was thinking of some way of avoiding these name and email fields from the 'post' table and instead using the values from the 'user_info' table but couldn't think of any way to do this for guest posts.
One possible method is to just display the name "Guest" with no email address for any post by a guest. (Obviously this is not a very good idea)
So does anyone have any better ideas to handle this ??

add a bool in your user table that identifies whether the user is a real user or a guest, and clean up the guest list occasionally. In other words, treat each guest as a new user, but don't give them user status (no logging in on a guest account etc).

[AGI1122]

Well if you plan to use a seperate table for guests... then you will need another field on the posts table called guest id to grab guest id's if the member is a guest.

If you want to store the guests in the users table with the users... you could just make another field on the table stating whether the user is a guest or a user... then just whatever id is there and display it differently based on whether they are a guest or user.

Either way... you still have to have another field.

[df on trail]

well, i wouldnt store a guest name in the user table. bleargh!!! bad design.

id have

tblUser
id
blah

tblGuest
id
blah

tblMessagePost
ID
lngUserId
lngGuestID

and i'd use a union join across the two tables on an inner joins and bring it all back in a query, or use a stored procedure to return a cursor.

that or you can just store the guest name in a varchar field in the message.....