Jump to content

News Feed Design


derekshull

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?

Link to comment
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?

Link to comment
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.

Link to comment
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.

Link to comment
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.

Link to comment
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

Link to comment
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. 

Link to comment
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......

Link to comment
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.

Link to comment
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.

Link to comment
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?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

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.