News Feed Social Network Help

[derekshull]

I’ve create a table of “followers” where users can follow other users. It’s set up as such:

ID username followname

username is the name of the person that is doing the action/following and followname is the person who is being followed.

I want to set up a news feed for each user on the profile.php page. The news feed should look at who the user is following (followname) and then fill the news feed with all the posts from those people in order of what time they were posted. The posts are in a different table called “needs” and each post is timestamped.

It’s barbaric but this is the code that I have so far:
[php]$following = mysql_query(“SELECT * FROM follow WHERE username=’$username’”);
while ($followrow = mysql_fetch_array($following)) {
$followname = $followrow[‘followname’];

$lookupposts = mysql_query (“SELECT * FROM needs WHERE needsusername=’$followname’”);
while ($postrow = mysql_fetch_array($lookupposts)) {
$description = $postrow[‘description’];
$needsusername = $postrow[‘needsusername’];
$datesubmitted = $postrow[‘datesubmitted’];

echo “User: $needsusername

Description:
$description

Date Submitted: $datesubmitted

----------------------------------------

”;

}

}[/php]

It works but there’s one problem. Instead of showing the posts by the timestamp only in the “needs” table it’s showing it by username THEN timestamp.

I know it’s because I have two while loops but I’ve been at this for hours and I really need some help figuring out how to get this working.

Thanks in advance!

[m11]

Your problem is somewhat because of the loops. Since you are outputting from within the secondary loop it must output in order of the first loop. You could resolve this by building an array, sorting it, then looping that array.

That’s still bad practice though. Queries within loops are bad. There’s almost always a way to prevent it.

Try this:

[php]
// create an array of all ‘followname’ rows
$follownames = array();
$following = mysql_query(“SELECT * FROM follow WHERE username=’$username’”);
while($followrow = mysql_fetch_assoc($following)) {
$follownames[] = $row[‘followname’];
}

// now you can query ‘needs’ for all ‘follownames’ in one query using IN()
// I believe you also need to add an order clause here
$lookupposts = mysql_query(“SELECT * FROM needs WHERE needsusername IN(’” . implode("’,’", $follownames) . “’) ORDER BY datesubmitted DESC”);
while($postrow = mysql_fetch_array($lookupposts)) {
$description = $postrow[‘description’];
$needsusername = $postrow[‘needsusername’];
$datesubmitted = $postrow[‘datesubmitted’];

echo "<b>User:</b> $needsusername<br><br><b>Description:</b><br>$description<br><br><b>Date Submitted:</b>  $datesubmitted<br><br>----------------------------------------<br><br>";

}
[/php]

[derekshull]

I put the code in and told it to produce errors if any and it didn’t show me anything. I did work with some stuff and got this:

[php]$sql = “SELECT * FROM follow LEFT JOIN needs ON follow.username=needs.needsusername WHERE needs.status=‘posted’ ORDER BY needs.datetime”;
$lookupposts = mysql_query($sql) or trigger_error("Query Failed! SQL: $sql - Error: ".mysql_error(), E_USER_ERROR);
while ($postrow = mysql_fetch_array($lookupposts)) {
$description = $postrow[‘description’];
$needsusername = $postrow[‘needsusername’];
$datesubmitted = $postrow[‘datesubmitted’];

echo “User: $needsusername

Description:
$description

Date Submitted: $datesubmitted

----------------------------------------

”;

}

[/php]

Problem being that it shows my posts and one of the users I follows. It shouldn’t be showing mine and it should show the 4-5 people I follow but it doesn’t. Any more suggestions on your code or the other code?