Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trial
Adam Duffield
30,494 PointsSocial Media Project PHP/SQL - I want to Display posts by date, for each user that user logged in is friends with?
Hi Treehouse,
For any PHP/SQL wizards out there, I apologize for the lack of code, theres a lot of it and I've been turning gray over this issue, spent approx 20 hours over the last 3 days trying to sort it.
I'm creating a small time social network for fun with friends which would also make a nice portfolio piece. Everything works perfect so far apart from I can't display posts by date because I loop through to check if the logged in user is friends with them first. My posts actually come by through A-Z user if friends and then by date. I don't know whether my database design could be better or i'm not handling this in PHP correctly.
$selectFriendsQuery = $db->query("SELECT * FROM friends WHERE friend_one='$username_id' OR friend_two='$username_id' AND status=2");
$friendCount = $selectFriendsQuery->rowCount();
$friend = $selectFriendsQuery->fetch();
$friend_one = $friend['friend_one'];
$friend_two = $friend['friend_two'];
if($friendCount != ""){
$getFriendQuery = $db->query("SELECT * FROM users WHERE id='$friend_one' OR id='$friend_two'");
$getFriendRow = $getFriendQuery->fetch(PDO::FETCH_ASSOC);
$id = $getFriendRow['id'];
// Get posts on wall if friends
$getposts = $db->query("SELECT * FROM posts WHERE added_by='$id' OR added_by='$id' ORDER BY timestamp DESC");
As you can see from the small snippet of code below, I loop through the friends first, say for e.g. Adam is friends with Courtney and Jackie it will loop through Courtney and display her posts by the date added, then it will loop through the next friend. So i end up with 10 posts perfectly ordered by date from Courtney then another 10 below it perfectly ordered by date from Jackie.
I have tried joins on my sql table to get the data all in one while loop but the tables can't be matched up by individual id, I end up with a lot of repeat loops so I end up looping the posts by date correctly but far too many times.
Here are some relevant columns from my sql tables if you may need it :
users table = id, and username that I need friends table = friend_one, friend_two that I need posts table = post_ id, added_by, user_posted_to
added_by, user_posted_to, friend_one and friend_two all relate to the individual users id number in the users table
I desperately need this one fixing and can't find answers or ideas on google so if you need more code or data to help point me in the right direction I will deliver!
Any help or pointing in the right direction would be a massive help!
MANY THANKS! Adam
Adam Duffield
30,494 PointsThanks for a bit of direction and support Sean. Need a good project to finally land a job!
1 Answer
thomascawthorn
22,986 PointsAre you willing to slightly refactor your database?
They way you've managed friendships is interesting! But it isn't easily expandable. A common way to manage this relationship is by a 'pivot' table or 'many-to-many' relationship.
You might have:
users table -> id, firstName, lastName etc..
friendships table-> tableID, userID, friendID
posts table -> id, added_by_user, posted_to_user, content
This way, you JOIN two users (make a friendship) in the friendships table by creating a row that contains two user ID's. This is a many to many relationship each user can HAVE MANY friends and BE FRIENDS with many people - but these two lists have the flexibility to be different.
A nice easy relationship would be to say a friendship between user1 and user2 looks like this:
friendships table-> table_id, user_id, friend_id
friendships table -> 1, 1, 2
friendships table -> 1, 2, 1
User1 is friends with user2 and user2 is friends with user1.
It looks like you're trying to create a facebook-type news feed? If this is correct, you'll need a statement similar to below (my MySQL isn't great and this is untested so you may need to fiddle!)
Friend ID's:
SELECT * FROM friendships WHERE user_id = $userID
Friends:
SELECT * FROM users
LEFT JOIN friendships on friendship.user_id = users.id
LEFT JOIN users on users.id = friendships.friend_id
WHERE user.id = $userID
ORDER BY date_added
Friends Posts (this is a bit of a stretch.. and probably not right)
SELECT * FROM users
LEFT JOIN friendships on friendship.user_id = users.id
LEFT JOIN users on users.id = friendships.friend_id
LEFT JOIN posts on added_by = friendships.friend_id
WHERE user.id = $userID
ORDER BY date_added
Essentially once you have that magic list of all friend ID's for one user, you can play with the query to join other tables and extract more information. You should be able to merge all of this into one database query :-)
This might not be a great answer... but will hopefully point you in the right direction!
Adam Duffield
30,494 PointsI definitely had to re-read this a few times but you may have sparked some inspiration for an idea that will work! I knew the database had to change but I wasn't 100% sure how, I'm gonna give this idea a try and get back to you, seriously though... thanks for the help Tom, it means a lot!
Adam
thomascawthorn
22,986 PointsNo worries man, if in doubt, check out this Ruby on Rails Course. The first and second badges are all about creating friendships. Obviously this might not be totally relevant to your application, but there are a couple of videos on the pivot tables I mentioned and how to create that relationship.
Hope that helps!
Adam Duffield
30,494 PointsMay be a tad bit different to your option but I cracked it with your help! So happy about this I spent days working on what seems like such a simple bug!
Incase it might help you out in future or anyone else that browses this forum post heres the SQL and the changes made to the DB to give you an understanding...
I added a friendship_id to the end of the friendship table and where friend_one = 4(Lets say me), basically if friend_one=4 then friendship_id is a specific id like 123, whereas if friend_one is another users friendships to others it's ID may be 124.
I added two more columns to the posts table called post_friendship_id_from and post_friendship_id_to which followed a similar patter to the added_by and user_posted_to columns in the posts table.
My SQL query to join them all up was this...
```SQL SELECT * FROM posts INNER JOIN friends ON (posts.post_friendship_id_to = friends.friendship_id OR posts.post_friendship_id_from = friends.friendship_id) WHERE (friends.friend_two=posts.added_by OR friends.friend_two=posts.added_by) AND friendship_id='$friendship_id' AND friends.status=2 ORDER BY timestamp DESC
$friendship_id comes from a previous query for checking the friends table if the user logged in id is equal to both friend_one and friend_two(friends with self so can see own posts) then display that friendship_id.
Hope this answers any future questions students may have for a similar problem
Sean T. Unwin
28,690 PointsSean T. Unwin
28,690 PointsI noticed in your last line you have
WHERE added_by='$id' OR added_by='$id'which seems redundant.I'm sorry I can't be more helpful at the moment, although here are some links where you could potentially get some help from:
Reddit:
StackOverflow:
Best of luck. It sounds like an awesome project. :)