Some help with creating an imageboard.
Moderator: General Moderators
Some help with creating an imageboard.
Hi, this is also a mysql question aswell. I'm attempting to create some imageboard software but have stumbled aross a problem. Ok it's a little confusing but I'll try and explain. So first off I'm not sure whether to go with two tables (1 for threads, 1 for replies) or 1 table. If i go with the two table option (which would seem the most obvious) there's this problem. I would like each post whether it be a thread starter or a reply to have a unique no. but I have no idea how i could do this. For exapmple person 1 creates a thread, this thread is the 65th post ever. The post would then be no. 65. This goes into the threads table. So then someone replies to this thread (no one has replied since the thread started) this would be post no. 66. But I cant have an auto Increment in both tables because... eg. Just say there have been two threads started ever this means that the second thread ever's auto increment would be 2. And there has been 64 replies (the 64th being the one we just talked about) but this is not the 64th post this is the 66th because of the two thread starters. So any help on what I can do? Also for anyone that has a soultion, how can I implement this into my script? Thanks
- aceconcepts
- DevNet Resident
- Posts: 1424
- Joined: Mon Feb 06, 2006 11:26 am
- Location: London
Re: Some help with creating an imageboard.
First of all, your first post in this thread is far too convoluted and longwinded. I know your trying to get your point/problem across but it's baffling.
If i've read it correctly I would assume you'd need to use a relational database where no significance is on the id.
If i've read it correctly I would assume you'd need to use a relational database where no significance is on the id.
Re: Some help with creating an imageboard.
Here is one way to make a simple message board database. This is a rudimentary example. The message author should really have its own table and there is no timestamp.
Notice there is no table for threads. There are only messages.
Thread starters are those messages that reply to the imaginary message 0. To retrieve a list of threads, find the thread starters.
The next query retrieves the messages in the thread started by message 1. To allow this query to be simple, inserted messages must always reply to a thread starter. Notice that message 4 uses reply_to=1 even though it is a response to message 2.
The next queries demonstrate one way to insert a reply to message 4 while acknowledging the requirement to reply to the thread starter (message 1). Warning: Do not use a persistent connection. MySQL variables live as long as the connection. If multiple clients use the same script, they all use the same variables.
Edit: This post was recovered from search engine cache.
Notice there is no table for threads. There are only messages.
Code: Select all
DESCRIBE `message`
# +----------+------------------+------+-----+---------+----------------+
# | Field | Type | Null | Key | Default | Extra |
# +----------+------------------+------+-----+---------+----------------+
# | msg_num | int(10) unsigned | NO | PRI | | auto_increment |
# | reply_to | int(10) unsigned | NO | | 0 | |
# | author | varchar(32) | NO | | | |
# | subject | varchar(128) | NO | | | |
# | body | text | NO | | | |
# +----------+------------------+------+-----+---------+----------------+
SELECT * FROM `message`
# +---------+----------+--------+----------------+---------------+
# | msg_num | reply_to | author | subject | body |
# +---------+----------+--------+----------------+---------------+
# | 1 | 0 | Bill | First Post | Hello, World |
# | 2 | 1 | Amy | Re: First Post | Hi, Bill. |
# | 3 | 0 | George | Second Thread | Knock, knock. |
# | 4 | 1 | Bill | Re: First Post | Hi, Amy. |
# +---------+----------+--------+----------------+---------------+Code: Select all
SELECT * FROM `message` WHERE `reply_to` = 0
# +---------+----------+--------+---------------+---------------+
# | msg_num | reply_to | author | subject | body |
# +---------+----------+--------+---------------+---------------+
# | 1 | 0 | Bill | First Post | Hello, World |
# | 3 | 0 | George | Second Thread | Knock, knock. |
# +---------+----------+--------+---------------+---------------+Code: Select all
SELECT * FROM `message`
WHERE `msg_num` = 1 OR `reply_to` = 1
ORDER BY `msg_num` ASC
# +---------+----------+--------+----------------+--------------+
# | msg_num | reply_to | author | subject | body |
# +---------+----------+--------+----------------+--------------+
# | 1 | 0 | Bill | First Post | Hello, World |
# | 2 | 1 | Amy | Re: First Post | Hi, Bill. |
# | 4 | 1 | Bill | Re: First Post | Hi, Amy. |
# +---------+----------+--------+----------------+--------------+Code: Select all
SET @msg_num = (SELECT `reply_to` FROM `message` WHERE `msg_num` = 4)
# +---+
SET @subject = (SELECT `subject` FROM `message` WHERE `msg_num` = @msg_num)
# +---+
INSERT INTO `message` (`reply_to`, `author`, `subject`, `body`)
VALUES(@msg_num, 'Amy', CONCAT('Re: ', @subject), 'Hi, again, Bill.')
# +---+
SELECT * FROM `message`
WHERE `msg_num` = LAST_INSERT_ID()
# +---------+----------+--------+----------------+------------------+
# | msg_num | reply_to | author | subject | body |
# +---------+----------+--------+----------------+------------------+
# | 5 | 1 | Amy | Re: First Post | Hi, again, Bill. |
# +---------+----------+--------+----------------+------------------+