News Feed Design

[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.

 

Can anyone help me in started to code this?

[derekshull]

There will be many people that the user follows too. The goal is to have each post shown according to the time it was posted, with the newer posts being on top.

[derekshull]

I know this is quite barbaric but it's a step in the right direction. I've coded something that works but it's not in the right order:

 

$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 "<b>User:</b> $needsusername<br><br><b>Description:</b><br>$description<br><br><b>Date Submitted:</b>  $datesubmitted<br><br>----------------------------------------<br><br>";

 

It shows it in order of username THEN timestamp, but I need it by just timestamp. Any suggestions?

[Jessica]

Never do queries in loops. Join the two tables, and use an ORDER BY clause.

[derekshull]

I can't join the two tables. One is for linking accounts as followers/following and the other is for user data like emails passwords. How would I join the two?

[Jessica]

...

Google "mysql join"

 

[derekshull]

Ok I'm on the right track! Thanks for the help but I'm still stumped, could you look at this and see what's wrong?

 

$following = mysql_query("SELECT * FROM follow WHERE username='$username'");
$followrow = mysql_fetch_array($following);
$followname = $followrow['followname'];



$lookupposts = mysql_query("SELECT * FROM follow a, needs b WHERE a.username='$username' AND b.needsusername='$followname' AND b.status='posted' ORDER BY b.datetime");
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>";



}

 

It's only showing one user's posts that I'm following and I'm not quite sure why. It also shows the same posts multiple times.

[Jessica]

Specify what columns you join on. 

 

If you're going to use table aliases make them make sense. a and b are meaningless.

 

You also should be using a primary key ID instead of the username. Username should only be stored in the user table, everything else should use their user id. 

 

 

You are running one query to get a user, and then selecting the information for that ONE USER. You need to do just ONE query joining the two tables only limiting it to the logged in user, not BOTH users.

[derekshull]

All that went over my head...still confused.

[derekshull]

I've looked up examples of Joining and this is the best I can see.

[Jessica]

You want all the users you follow right? Right now you select all the users in your SQL, but your PHP only pulls the first row. Then you do ANOTHER query to get that users posts. You're doing the same thing you were before.

 

Do only ONE query.

[derekshull]

This is what I have now:

 

$lookupposts = mysql_query("SELECT * FROM follow a, needs b WHERE a.username='$username' AND b.username='$username' b.status='posted'");
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>";



}

 

I don't feel like it's accomplishing what I want but it gives me this error:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given on line 46

[Jessica]

You have a syntax error in your query. Read the link in my signature on debugging SQL.

[derekshull]

I understand why the error is being caused. How to fix it is the reason that I come on here for help.

[Jessica]

You have a syntax error in your query. Read the link in my signature on debugging SQL.

 

You're the fastest reader I've ever seen! 

 

OR there's no way you read the post, implemented proper error checking and got the actual error. 

[derekshull]

And you're the most sarcastic person I've ever seen!

 

I know it's because I'm reusing the variables in my while loop, doesn't change the fact that I still don't know how to fix the issue.

[Jessica]

It has nothing to do with that. 

 

[derekshull]

Look I don't know the answer, I've searching forums and looked at debugging crap all day and I need answers...that's why I've come to a forum. Answers that I can understand because I'm not as advanced at this than you. But instead I get snide remarks. I'll look elsewhere.

[derekshull]

here is what my php is after trying to fathom what the heck you're talking about:

 

$sql = "SELECT * FROM follow, needs WHERE follow.username=needs.username ORDER BY needs.datetime";
$lookupposts = mysqli_query($sql) or trigger_error("Query Failed! SQL: $sql - Error: ".mysqli_error(), E_USER_ERROR);
while ($postrow = mysql_fetch_array($lookupposts)) {
$description = $postrow['needs.description'];
$needsusername = $postrow['needs.needsusername'];
$datesubmitted = $postrow['needs.datesubmitted'];


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


}

 

 

 

and here what your debug code showed me:

Warning: mysqli_query() expects at least 2 parameters, 1 given in /home/content/17/9932517/html/ProjectX/profile.php on line 45

 

Warning: mysqli_error() expects exactly 1 parameter, 0 given in /home/content/17/9932517/html/ProjectX/profile.php on line 45

 

Fatal error: Query Failed! SQL: SELECT * FROM follow, needs WHERE follow.username=needs.username ORDER BY needs.datetime - Error: in /home/content/17/9932517/html/ProjectX/profile.php on line 45

 

 

Still no clue how to solve this issue......

[Jessica]

You can't mix mysqli and mysql. You need to use the mysql version of the error function when you're using a mysql connection instead of mysqli. 

 

However, you did actually fix the syntax error, so good job. You still need to specify what column to do your join ON, but otherwise good.

[Jessica]

Oh and by the way - I don't come here to "help" ungrateful whiny babies. So stop acting like a child and remember this is a FREE Help forum. (For the record, if someone paying me spoke to me in such an ungrateful manner I'd fire them, so I especially won't put up with it from someone I am donating my time to.) Grow up.

[derekshull]

Ok so I think I get it, let me know if I'm wrong. The WHERE should have been the ON? And I needed to do a JOIN so I did a LEFT JOIN although I'm not sure of the differences. So I put in this:

 

$sql = "SELECT * FROM follow LEFT JOIN needs ON follow.username=needs.needsusername 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['needs.description'];
$needsusername = $postrow['needs.needsusername'];
$datesubmitted = $postrow['needs.datesubmitted'];

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

}

 

 

I didn't get an error but all it shows is

 

User:

 

Description:

 

Date Submitted:

 

but it doesn't show any info. stumped again. But I'm learning. Any direction?

[Jessica]

You don't need the table alias when you print the row. Print_r($postrow)

[derekshull]

Works great except one thing now.

 

For some reason it's showing my posts and the posts of one other user named "dvdowns" but not the posts of everyone I follow. It's strange.

[derekshull]

I've messed around and still can't figure out why it's showing my posts and the posts of only one person I follow. I don't think it's understanding that I'm following others besides that one user. So is it pulling all the people I follow?