------------clipped--------------Working with PHP 4.3.0 and MySQL 4.0.14 (but code MUST be compatible with 3.23.56).
For my forums, I've got this 4-way join to load (and output) the posts with info on the poster:
$fullthread = "SELECT postID,hf_posts.userID,name,time,post,edit,nickname,joined,location,posts,levelname 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 hf_levels.levelID=hf_users.levelID ORDER BY hf_posts.time ASC";
--------------clipped------------------------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 Visitor/guest.
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 ...
TIA
Rene
Maybe I haven't had enough coffee, yet, but my first inclination is to suggest adding a user_status flag to hf_users.
Doug
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
