I am trying to set up a forum where there are main posts with replies and
replies to replies. Rather than using nested queries in my middleware I am
thinking there has to be some kind of recursive query where I can output the
results in a format like so:
MAIN
Reply to main
reply to reply to main
reply to main
reply to 2nd reply to main
MAIN
Reply
reply
....
The table structure is like so:
+-------------------+--------------+------+-----+-------------------+-------
---------+
| Field | Type | Null | Key | Default | Extra
|
+-------------------+--------------+------+-----+-------------------+-------
---------+
| forum_id | bigint(11) | NO | PRI | NULL |
auto_increment |
| project_id | bigint(11) | YES | MUL | 0 |
|
| forum_reply_id | bigint(11) | YES | MUL | 0 |
|
| forum_dev_id | bigint(11) | YES | MUL | 0 |
|
| forum_type | varchar(255) | YES | | |
|
| forum_subject | varchar(255) | YES | | |
|
| forum_message | longtext | YES | | NULL |
|
| forum_date_posted | timestamp | NO | | CURRENT_TIMESTAMP |
|
+-------------------+--------------+------+-----+-------------------+-------
---------+
Test data is like so
mysql> select * from forums;
+----------+------------+----------------+--------------+------------+------
---------+-------------------------------------------------+----------------
-----+
| forum_id | project_id | forum_reply_id | forum_dev_id | forum_type |
forum_subject | forum_message |
forum_date_posted |
+----------+------------+----------------+--------------+------------+------
---------+-------------------------------------------------+----------------
-----+
| 1 | 42 | 0 | 1 | | First
Post | I am the First! | 2007-07-08
15:09:41 |
| 2 | 42 | 1 | 1 | |
| I am a reply to the first | 2007-07-08 15:30:36 |
| 3 | 42 | 0 | 1 | |
sample data | this is some sample data in a new thread | 2007-07-08
15:10:03 |
| 4 | 42 | 2 | 1 | |
| this is a reply to the reply of the first post. | 2007-07-08 15:33:54 |
+----------+------------+----------------+--------------+------------+------
---------+-------------------------------------------------+----------------
-----+
4 rows in set (0.00 sec)
I am figuring that if the reply_to_id is 0 then it is a parent thread
otherwise it is a child or child of a child etc.
Any way of doing this?
Thanks
Steffan
---------------------------------------------------------------
T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4
Steffan A. Cline
[EMAIL PROTECTED] Phoenix, Az
http://www.ExecuChoice.net USA
AIM : SteffanC ICQ : 57234309
Lasso Partner Alliance Member
---------------------------------------------------------------
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
