Selecting statuses from friends only?

[Fenhopi]

Hi, I have a database that holds friendships. I've created a database called statuses. Everytime a user makes a status it's added in to the status database. Now I need to make a system that retrieves statuses of friends only.. How would I do that? Thank you!

[Fenhopi]

So this is what I have:

  if($session->logged_in){
		echo "<img src=\"images/oodlestreamsign.jpg\">";
		$username = $_SESSION['username']; 
		$friends = "SELECT user1 FROM friends2 WHERE user2='$username'";

		$result = $database->query($friends);

		// Loop
		While($myrow = mysql_fetch_array($result))
		{
			//VARS
			$user = $myrow['user1'];
			$status = "SELECT * FROM status WHERE byuser='$user' ORDER BY dtime DESC";
			$statusresult = $database->query($status);
			$myrow2 = mysql_fetch_array($statusresult);

			//Echoes
			echo "By <a href=\"userinfo.php?user=$user\">$user</a>";
			echo "<h3>";
			echo $myrow2['status'];
			echo "</tr></td>";
			echo "<br><tr><td>On: <i>";
			echo $myrow2['dtime'];
			echo "</b></td></tr><tr><td><br>";
			echo "</i></td></tr><hr><br>";
		}

	}
	else{
		echo "Not a lot of Statuses";
	}

 

This works, the only problem is that the statuses by users are displayed in the order I have added my friends, instead of in the order my friends posted their statuses..

 

Help please?

[gizmola]

Inner join status and friends2, so you have a single query, ordered by dtime desc. 

[kratsg]

$status = "SELECT * FROM status WHERE byuser='$user' ORDER BY dtime DESC";

$friends = "SELECT user1 FROM friends2 WHERE user2='$username'";

 

Here's a better query that might suit you: (I haven't tested it yet, but it should work)

 

$friend_statuses = "SELECT * FROM status WHERE byuser=(SELECT user1 FROM friends2 WHERE user2='$username') ORDER BY dtime DESC";

[gizmola]

$status = "SELECT * FROM status WHERE byuser='$user' ORDER BY dtime DESC";

$friends = "SELECT user1 FROM friends2 WHERE user2='$username'";

 

Here's a better query that might suit you: (I haven't tested it yet, but it should work)

 

$friend_statuses = "SELECT * FROM status WHERE byuser=(SELECT user1 FROM friends2 WHERE user2='$username') ORDER BY dtime DESC";

 

A join is preferable to a subquery, but your query won't work regardless, because DTIME is not a column in the friend2 table, which is why a join is needed.

[Fenhopi]

Could you please provide me with an example how I would make a join.. I've looked it up but I don't seem to understand how to put it together right..

[kratsg]

And example of a join query:

 

$friend_statuses = "SELECT status.*, friends2.user1 FROM status, friends2 WHERE byuser=user1 AND user2='$username' ORDER BY status.dtime DESC";

[Fenhopi]

Alright, thanks a lot! If I wanted to add myself/$username to the list of statuses, how would I do that?

[kratsg]

There are two ways:

 

1.) Just retrieve your own status regardless and just add that to the list.

2.) Re-write the query to be something like...

 

$friend_statuses = "SELECT status.*, friends2.user1 FROM status, friends2 WHERE status.byuser IN (friends2.user1,'myself') AND friends2.user2='$username' ORDER BY status.dtime DESC";

[Fenhopi]

I couldn't get the query working, it kept echoing my status twice.. here is what I got:

	echo "<img src=\"images/oodlestreamsign.jpg\">";
		$username = $_SESSION['username'];
		$friend_statuses = "SELECT status.*, friends2.user1 FROM statuses, friends2 WHERE statuses.byuser=friends2.user1 or '$username' AND friends2.user2='$username' ORDER BY statuses.dtime DESC";


		$result = $database->query($friend_statuses);


		// Loop
		While($myrow = mysql_fetch_array($result))
		{
			//VARS
			$user = $myrow['byuser'];


			//Echoes
			echo "By <a href=\"userinfo.php?user=$user\">$user</a>";
			echo "<h3>";
			echo $myrow['status];
			echo "</tr></td>";
			echo "<br><tr><td>On: <i>";
			echo $myrow['dtime'];
			echo "</b></td></tr><tr><td><br>";
			echo "</i></td></tr><hr><br>";
		}
		echo mysql_error();
	}

[kratsg]

Can you do the following? After the appropriate variable, write

 

var_dump($friend_statuses)

 

Copy and paste the query that is outputted on the page, go to phpMyAdmin (or whatever you have), run the query, and view the outputted results.

[Ruzzas]

Can you do the following? After the appropriate variable, write

 

var_dump($friend_statuses)

 

Copy and paste the query that is outputted on the page, go to phpMyAdmin (or whatever you have), run the query, and view the outputted results.

 

you forgot ; after var_dump($friend_statuses)