Jump to content
derekshull

News Feed Design

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

  • Like 1

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.