PHP - Help With Joining Multiple Tables, Facebook-like Activity Wall

Hey all,

Quick sum up: I have a social website where users can follow/post/vote etc. I am adding a new facebook-like activity wall feature to each users profiles.

I have worked on this bit for a few days now and can't seem to get it to work properly. What I want is, well, exactly like the facebook wall as I mentioned...When a visitor visits a user's profile, that user's activity will display, whether they recently "voted" on a post, commented, or posted something of their own, I would like it to display on their profile and of course, have the most recent entries on top.

The problems I am having right now a it's displaying some things twice and it's not sorting them by the most recent.

I am working with 3 tables here (I only posted the columns that I am using):
Code: [Select]
Table structure for table votes_posts
Field Type Null Default
indexer int(11) No
post_id int(11) No
vote int(11) No
user_name varchar(250) No
date varchar(255) No

Table structure for table posts
Field Type Null Default
id int(11) No
name varchar(30) No
content text No
datetimerss varchar(255) No
category int(11) No

Table structure for table comments
Field Type Null Default
id int(12) No
post_id int(12) No
name varchar(30) No
content text No
type varchar(30) No
datetimerss varchar(255) No

$users is just $_GET['user']
The timeAgo() function, simply displays the very popular "posted xx ago" date time.
<ul class="streamActivity">
<?php
$checkActivity = "SELECT votes_posts.post_id, votes_posts.user_name, votes_posts.vote, votes_posts.date, 

posts.id, posts.content, posts.name, posts.datetimerss, categories.category, 

comments.post_id AS comm_postid, comments.content AS comm_content, comments.name AS comm_name, comments.datetimerss AS comm_date, comments.type 

FROM `votes_posts` 

LEFT OUTER JOIN `posts` ON votes_posts.post_id = posts.id 

LEFT OUTER JOIN `comments` ON posts.id = comments.post_id 

LEFT OUTER JOIN `categories` ON posts.category = categories.cat_id

WHERE (votes_posts.post_id = posts.id AND user_name = '$user')

OR (comments.post_id = posts.id AND comments.name = '$user')

OR (posts.name = '$user') ORDER BY votes_posts.date, posts.datetimerss, comments.datetimerss ASC";
$checkActivityResult = mysql_query($checkActivity);

if (mysql_num_rows($checkActivityResult) > 0) {

while ($sessionAct = mysql_fetch_array($checkActivityResult)) {

$category = strtolower($sessionAct['category']);

$dateVote = timeAgo($sessionAct['date']);

$datePost = timeAgo($sessionAct['datetimerss']);

$dateComm = timeAgo($sessionAct['comm_date']);

$namePost = $sessionAct['name'];

$nameComm = $sessionAct['comm_name'];

$nameVote = $sessionAct['user_name'];

$idVote = $sessionAct['post_id'];

$idPost = $sessionAct['id'];

$idComm = $sessionAct['comm_postid'];

$contentPost = $sessionAct['content'];

$contentComm = $sessionAct['comm_content'];

$typeComm = $sessionAct['type'];

if ($namePost == $user) {

$classList = 'storyPost';

$classIcon = 'iconMuttr';

$name = $namePost;

$content = $contentPost .' ... read more';

$datetime = $datePost;

}

elseif ($nameComm == $user && $typeComm == "Comment") {

$classList = 'actionPost';

$classIcon = 'iconComment';

$name = $nameComm;

$content = 'Commented "'. $contentComm .'"';

$datetime = $dateComm;

} elseif ($nameComm == $user && $typeComm == "Advice") {

$classList = 'actionPost';

$classIcon = 'iconAdvice';

$name = $nameComm;

$content = 'gave Advice "'. $contentComm .'"';

$datetime = $dateComm;

}

elseif ($nameVote == $user) {

if ($sessionAct['vote'] == "1") {

$classList = 'actionPost';

$classIcon = 'iconLaughed';

$name = $nameVote;

$content = 'Laughed at "'. $contentPost .'"';

$datetime = $dateVote;

} elseif ($sessionAct['vote'] == "2") {

$classList = 'actionPost';

$classIcon = 'iconLoved';

$name = $nameVote;

$content = 'Showed Love on "'. $contentPost .'"';

$datetime = $dateVote;

} elseif ($sessionAct['vote'] == "3") {

$classList = 'actionPost';

$classIcon = 'iconIdiot';

$name = $nameVote;

$content = 'called '. $namePost .' an Idiot for "'. $contentPost .'"';

$datetime = $dateVote;

}

}
?>

<li class="row-activity <?php echo $classList; ?>">

<a href="" class="avatar"><img src="/images/avatars/default_male.jpg" /></a>

<div class="info">

<div class="userName">

<a href=""><?php echo $name; ?></a>

</div>

<span class="msgBody"><?php echo $content; ?></span>

<div class="imgTimeStream">

<span class="img <?php echo $classIcon; ?>"></span>

<span class="source"><?php echo $datetime; ?></span>

</div>

</div>

</li>
<?php

}
} else {

echo '<p>This user has no recent activity.</p>';
}
?>
</ul>


I am by no means an expert programmer...everything I know I have pretty much taught myself over the past year or two, so I can only imagine how ugly this truly is lol. I am learning each and everyday though, and I hope to continue

Also, within each type of activity, there will be slightly different CSS classes on some things.
Example:
if the result row is a post then the css class of the list item would be "post"
if the result row is a vote then the css class of the list item would be "vote"
etc.

Any help would be appreciated!