At 14:56 19.02.2003, Jono Bacon spoke out and said:
--------------------[snip]--------------------
>The code that you posed is the technique I have used at the moment. This
>technique works fine, but like my mate said, this limits me to a single
>parent for a comment/message. I admit that is unlikely that I would need
>more than one parent, but what would happen if I changed the parent -
>would this model still work fine?
--------------------[snip]--------------------
As long as you have one-to-many relationship this model will be fine. You
can easily change the "parent" of a message by replacing the "id_topic"
column with another (hopefully valid) topic id.
If you have many-to-many you need an intermediate table to resolve:
+-----+ +----------+ +-------+
|TOPIC| |MSG2TOPIC | |MESSAGE|
+-----+ +----------+ +-------+
| id | <- |id_topic | | id |
| ... | |id_message| -> | ... |
+-----+ +----------+ +-------+
To select all messages for a certain topic you would
select * from MESSAGE
where id in (select id_message from MSG2TOPIC where id_topic =
$id_topic);
or, using a join
select m.* from MSG2TOPIC rel inner join MESSAGE m on m.id = rel.id_message
where rel.id_topic = $id_topic;
To select all topics for a particular message:
select * from TOPIC
where id in (select id_topic from MSG2TOPIC where id_message =
$id_message);
--
>O Ernest E. Vogelsinger
(\) ICQ #13394035
^ http://www.vogelsinger.at/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
