-{ Rene Brehmer }- wrote:
Working with PHP 4.3.0 and MySQL 4.0.14 (but code MUST be compatible with

For my forums, I've got this 4-way join to load (and output) the posts with
info on the poster:

$fullthread = "SELECT
               FROM hf_posts,hf_users,hf_user_stats,hf_levels
               WHERE hf_posts.threadID='$threadID' AND
hf_posts.userID=hf_users.userID AND hf_user_stats.userID=hf_users.userID AND
               ORDER BY hf_posts.time ASC";

hf_posts has postID as primary key, and threadID and userID as foreign keys.
hf_users has userID as primary key, and levelID as foreign key.
hf_user_stats is basically an extension of hf_users, and thus only have
userID as primary key, but it also functions as foreign key.
hf_levels has levelID as primary key, and no foreign keys (it's a top-level
table in the relations).

But here comes the problem: If a user is deleted, the records from hf_users
and hf_user_stats will be gone. On the join, this means that any posts made
by the deleted users will not be included, and thus not displayed -- they
become dangling posts in the database ...

I haven't actually written the code to delete users yet, but my current idea
for a workaround is that on deleting the users, changing all their posts to
userID 1 (the system guest level). This would atleast let the posts be
displayed, although make those posts display as posted by a guest, and
because the displayname is stored with the post, it would not list as

What I have problem with is figuring out whether this is a kludge or the
only way to do it. Or is there a better way to make the join so it will
include the posts even if it cannot find anything to join with in hf_users
and hf_user_stats ???

I know it looks like deleting levels will cause problems as well, but the
code for that has been made so deleting a level will move all users assigned
to that level to a different one.

Any suggestions for handling this better would be appreciated ...



Maybe I haven't had enough coffee, yet, but my first inclination is to suggest adding a user_status flag to hf_users.


PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to