derekshull Posted February 25, 2013 Share Posted February 25, 2013 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? Quote Link to comment Share on other sites More sharing options...
derekshull Posted February 25, 2013 Author Share Posted February 25, 2013 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. Quote Link to comment Share on other sites More sharing options...
derekshull Posted February 25, 2013 Author Share Posted February 25, 2013 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? Quote Link to comment Share on other sites More sharing options...
Jessica Posted February 25, 2013 Share Posted February 25, 2013 Never do queries in loops. Join the two tables, and use an ORDER BY clause. 1 Quote Link to comment Share on other sites More sharing options...
derekshull Posted February 25, 2013 Author Share Posted February 25, 2013 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? Quote Link to comment Share on other sites More sharing options...
Jessica Posted February 25, 2013 Share Posted February 25, 2013 ... Google "mysql join" Quote Link to comment Share on other sites More sharing options...
derekshull Posted February 25, 2013 Author Share Posted February 25, 2013 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. Quote Link to comment Share on other sites More sharing options...
Jessica Posted February 25, 2013 Share Posted February 25, 2013 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. Quote Link to comment Share on other sites More sharing options...
derekshull Posted February 25, 2013 Author Share Posted February 25, 2013 All that went over my head...still confused. Quote Link to comment Share on other sites More sharing options...
derekshull Posted February 25, 2013 Author Share Posted February 25, 2013 I've looked up examples of Joining and this is the best I can see. Quote Link to comment Share on other sites More sharing options...
Jessica Posted February 25, 2013 Share Posted February 25, 2013 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. Quote Link to comment Share on other sites More sharing options...
derekshull Posted February 25, 2013 Author Share Posted February 25, 2013 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 Quote Link to comment Share on other sites More sharing options...
Jessica Posted February 25, 2013 Share Posted February 25, 2013 You have a syntax error in your query. Read the link in my signature on debugging SQL. Quote Link to comment Share on other sites More sharing options...
derekshull Posted February 25, 2013 Author Share Posted February 25, 2013 I understand why the error is being caused. How to fix it is the reason that I come on here for help. Quote Link to comment Share on other sites More sharing options...
Jessica Posted February 25, 2013 Share Posted February 25, 2013 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. Quote Link to comment Share on other sites More sharing options...
derekshull Posted February 25, 2013 Author Share Posted February 25, 2013 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. Quote Link to comment Share on other sites More sharing options...
Jessica Posted February 25, 2013 Share Posted February 25, 2013 It has nothing to do with that. Quote Link to comment Share on other sites More sharing options...
derekshull Posted February 25, 2013 Author Share Posted February 25, 2013 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. Quote Link to comment Share on other sites More sharing options...
derekshull Posted February 25, 2013 Author Share Posted February 25, 2013 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...... Quote Link to comment Share on other sites More sharing options...
Jessica Posted February 25, 2013 Share Posted February 25, 2013 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. Quote Link to comment Share on other sites More sharing options...
Jessica Posted February 25, 2013 Share Posted February 25, 2013 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. Quote Link to comment Share on other sites More sharing options...
derekshull Posted February 25, 2013 Author Share Posted February 25, 2013 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? Quote Link to comment Share on other sites More sharing options...
Jessica Posted February 25, 2013 Share Posted February 25, 2013 You don't need the table alias when you print the row. Print_r($postrow) Quote Link to comment Share on other sites More sharing options...
derekshull Posted February 26, 2013 Author Share Posted February 26, 2013 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. Quote Link to comment Share on other sites More sharing options...
derekshull Posted February 26, 2013 Author Share Posted February 26, 2013 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? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.