Hi!
I think it'd be good to add in hf_users column status - ENUM('Active',
'Blocked', 'Deleted'), and when user
will be deleted you should set this status to Deleted. Value 'Blocked'
may give you a simple way to ban user :)
Yes, this method of deletion take more database space, but it'd be
better, I think...
Wednesday, April 21, 2004, 1:22:30 PM, -{ wrote:
RB> Working with PHP 4.3.0 and MySQL 4.0.14 (but code MUST be compatible with
RB> 3.23.56).
RB> For my forums, I've got this 4-way join to load (and output) the posts with
RB> info on the poster:
RB> $fullthread = "SELECT
RB> postID,hf_posts.userID,name,time,post,edit,nickname,joined,location,posts,levelname
RB> FROM hf_posts,hf_users,hf_user_stats,hf_levels
RB> WHERE hf_posts.threadID='$threadID' AND
RB> hf_posts.userID=hf_users.userID AND hf_user_stats.userID=hf_users.userID AND
RB> hf_levels.levelID=hf_users.levelID
RB> ORDER BY hf_posts.time ASC";
RB> I'm not gonna paste the entire table structure, cuz it comes to rather many
RB> rows those 4 tables make up. But it's pretty basic really: hf_posts contain
RB> the posts in the forum. hf_users contains the static userinfo = the user's
RB> profile, level assignment and a few control values. hf_user_stats contains
RB> the dynamic user info - number of posts & threads, last login and logout,
RB> last IP, and things like that. hf_levels contains information and general
RB> perms for the user levels.
RB> hf_posts has postID as primary key, and threadID and userID as foreign keys.
RB> hf_users has userID as primary key, and levelID as foreign key.
RB> hf_user_stats is basically an extension of hf_users, and thus only have
RB> userID as primary key, but it also functions as foreign key.
RB> hf_levels has levelID as primary key, and no foreign keys (it's a top-level
RB> table in the relations).
RB> The way it's made, when a post is made, the poster's userID and displayname
RB> is stored in hf_posts under 'userID' and 'name' respectively.
RB> But the poster's current displayname is always in hf_users.nickname. Posts
RB> by unregistered users (or those not logged in) are stored with userID 1.
RB> Since the system allows unregistered users to post, I've got a guest user
RB> "implanted" into the hf_users table on ID 1, just so the join won't fail to
RB> display those posts. When the script outputs the posts, it checks the userID
RB> of the posts. If the userID = 1, then it prints the displayname stored with
RB> the post, and skips the userdata for that post, otherwise it uses the
RB> displayname from the users table.
RB> But here comes the problem: If a user is deleted, the records from hf_users
RB> and hf_user_stats will be gone. On the join, this means that any posts made
RB> by the deleted users will not be included, and thus not displayed -- they
RB> become dangling posts in the database ...
RB> I haven't actually written the code to delete users yet, but my current idea
RB> for a workaround is that on deleting the users, changing all their posts to
RB> userID 1 (the system guest level). This would atleast let the posts be
RB> displayed, although make those posts display as posted by a guest, and
RB> because the displayname is stored with the post, it would not list as
RB> Visitor/guest.
RB> What I have problem with is figuring out whether this is a kludge or the
RB> only way to do it. Or is there a better way to make the join so it will
RB> include the posts even if it cannot find anything to join with in hf_users
RB> and hf_user_stats ???
RB> I know it looks like deleting levels will cause problems as well, but the
RB> code for that has been made so deleting a level will move all users assigned
RB> to that level to a different one.
RB> Any suggestions for handling this better would be appreciated ...
RB> TIA
RB> Rene
RB> --
RB> Rene Brehmer
RB> aka Metalbunny
RB> ~ If you don't like what I have to say ... don't read it ~
RB> http://metalbunny.net/
RB> References, tools, and other useful stuff...
RB> Check out the new Metalbunny forums @ http://forums.metalbunny.net/
--
Best regards,
Mikhail U. Petrov
mailto:mikhail@xxxxxxxx
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
