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";
I'm not gonna paste the entire table structure, cuz it comes to rather many
rows those 4 tables make up. But it's pretty basic really: hf_posts contain
the posts in the forum. hf_users contains the static userinfo = the user's
profile, level assignment and a few control values. hf_user_stats contains
the dynamic user info - number of posts & threads, last login and logout,
last IP, and things like that. hf_levels contains information and general
perms for the user levels.
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).
The way it's made, when a post is made, the poster's userID and displayname
is stored in hf_posts under 'userID' and 'name' respectively.
But the poster's current displayname is always in hf_users.nickname. Posts
by unregistered users (or those not logged in) are stored with userID 1.
Since the system allows unregistered users to post, I've got a guest user
"implanted" into the hf_users table on ID 1, just so the join won't fail to
display those posts. When the script outputs the posts, it checks the userID
of the posts. If the userID = 1, then it prints the displayname stored with
the post, and skips the userdata for that post, otherwise it uses the
displayname from the users table.
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
--
Rene Brehmer
aka Metalbunny
~ If you don't like what I have to say ... don't read it ~
http://metalbunny.net/
References, tools, and other useful stuff...
Check out the new Metalbunny forums @ http://forums.metalbunny.net/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
