Help with joining multiple tables, facebook-like activity wall

[ncovill]

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 are: 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):

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!