Getting news feed

[jbonnett]

Hi all I'm trying to get a news feed I have got the news feed working although the results are in order of each user then in order of timestamp, I am tryng to just order by the timestamp.

 

I also realize that it is because of running two queries, If you can make up a new query feel free I'm shit with mysql

 

Please help.

$friends = $database->query("SELECT `person_id` FROM `friends` 
WHERE `friend_id` = 'Admin' 
AND `status` = 'Active'
UNION SELECT `friend_id` FROM `friends` 
WHERE `person_id` = 'Admin' 
AND `status` = 'Active'");
$i = 0;
while($f = mysql_fetch_array($friends)){
	if($f['friend_id'] != $session->username or $f['person_id'] != $session->username){
		 if($av != "true") { $visible =  " AND `visibility` = 'true' "; }
		 $posts = $database->query("
		 SELECT * FROM `status` 
		 WHERE `person_id` = (
		 	SELECT `person_id` FROM `friends` 
			WHERE `friend_id` = 'Admin' 
			AND `status` = 'Active' 
			UNION SELECT `friend_id` FROM `friends` 
			WHERE `person_id` = 'Admin' 
			AND `status` = 'Active' 
			LIMIT ".$i.", 1) 
		OR `friend_id` = (
			SELECT `person_id` FROM `friends` 
			WHERE `friend_id` = 'Admin' 
			AND `status` = 'Active' 
			UNION SELECT `friend_id` FROM 
			`friends` WHERE `person_id` = 'Admin' 
			AND `status` = 'Active' 
			LIMIT ".$i.", 1) ORDER BY `timestamp` DESC");
		 $i++;
			while($p = mysql_fetch_array($posts)) { 
				if($p['post_type'] == "status"){ 
					include("status.php"); 
				}elseif($p['post_type'] == "photo") { 
					include("photos.php");	
				} 
			} 	
	}
}

[jbonnett]

Anyone?

[Jessica]

Your query looks awful. Do you know about JOINS?

[jbonnett]

Yes but I don't know how use them properly I have tried but the results are too unpredictable as I need to check names "friends against statuses" with 4 possible combinations first:

 

"status" person = "friend" person

"status" friend = "friend" person

"status" person = "friend" friend

"status" friend = "friend" friend

 

then get statuses without repeating posts

 

the closest I have come is

SELECT `status`.*, `friends`.`person_id`, `friends`.`friend_id`
FROM `status`, `friends`
WHERE 
(
	`friends`.`person_id` = 'Admin' 
	AND `friends`.`person_id` = `status`.`friend_id`
)
OR
(
	`friends`.`friend_id` = 'Admin' 
	AND `friends`.`friend_id` = `status`.`friend_id`
)
OR
(
	`friends`.`friend_id` = 'Admin' 
	AND `friends`.`friend_id` = `status`.`person_id`
)
OR
(
	`friends`.`person_id` = 'Admin' 
	AND `friends`.`friend_id` = `status`.`person_id`
)

GROUP BY `status`.`id`
ORDER BY `status`.`timestamp` DESC