List:General Discussion« Previous MessageNext Message »
From:Steffan A. Cline Date:July 8 2007 11:24pm
Subject:Recursive queries
View as plain text  
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
Steffan@stripped                             Phoenix, Az
http://www.ExecuChoice.net                                  USA
AIM : SteffanC          ICQ : 57234309
                                  Lasso Partner Alliance Member
---------------------------------------------------------------



Thread
Recursive queriesSteffan A. Cline9 Jul
  • Re: Recursive queriesBaron Schwartz9 Jul
  • Re: Recursive queriesDavid T. Ashley9 Jul