DonaldFaulknor Posted January 31, 2012 Share Posted January 31, 2012 Ok, something I haven't done yet, I've programmed the majority of my site so far and have yet needed to utilize my relationships. Hopefully I set the tables up correct with foreign keys, primary keys, constraints, and references. This is what I need to do, should be relatively easy for most people here. I have one table... Friends with columns... friend_id - id of the friend users_id - id of the user who added the friend I have another table Statuses with columns... users_id - id of the user who posted the status statuses - the status posted whens - the time the status posted ------ I have statuses properly posting to a person's profile.php page but, I want the homepage of the site (same concept as Facebook) to post the status of your friends. I mean, what's the purpose of a status if your friends can't see them. ------ So, I need to somehow extract all of a users friends from the friends table, and use those friends to insert the statuses of friends on someone's homepage. Much help is greatly appreciated. If you wish to see how my tables are set up and if they are correct, that would be appreciated, just ask, and I'll go get my sql query of those tables for you to look at. THANKS! By the way, my site is at... http://inyoursocialnetwork.netne.net - the domain name and the site name are just temporary fill ins. If you have any ideas or suggestions for a good name, please, please, please let me know. It's driving me nuts I can't come up with a good name. I would prefer a short one. Shorter is easier to type and generally easier to remember. Quote Link to comment Share on other sites More sharing options...
AyKay47 Posted January 31, 2012 Share Posted January 31, 2012 have you attempted to write an sql statement for this yet? if so, please post the code so we can help. Quote Link to comment Share on other sites More sharing options...
DonaldFaulknor Posted January 31, 2012 Author Share Posted January 31, 2012 No I haven't, I've thought about it in my head, thinking, how's this going to work. $query = "SELECT * FROM Friends WHERE users_id='$_SESSION[user_id]"; $result = mysql_fetch_assoc($query); <-- not quite sure if assoc is appropriate. Then what? use $result from this query in the WHERE clause of the Statuses table? Not even sure if I'm on the right track. Thanks for any help Quote Link to comment Share on other sites More sharing options...
AyKay47 Posted January 31, 2012 Share Posted January 31, 2012 well, you will definitely want to use a mysql JOIN to connect the data from both tables, perhaps something like: select status.statuses, status.when, status.user_id from friends join status on status.user_id = friends.friend_id where friend_id = $_SESSION['user_id'] to get you started Quote Link to comment Share on other sites More sharing options...
Adam Posted January 31, 2012 Share Posted January 31, 2012 So' date=' I need to somehow extract all of a users friends from the friends table, and use those friends to insert the statuses of friends on someone's homepage.[/quote'] You're approaching this backwards. Don't store the same content for every one of their friends - what if they had thousands? And then deleted it straight after? Write operations would take far too long. You want to look-up their friends statuses as you select the data, using a JOIN to link together the tables. Do you have any experience with JOINs? If not I would recommend practising some simpler queries to start with and building up to this, as you're going to need to join several tables together. Whatever you do though, just don't perform queries within a loop of their friends IDs. Quote Link to comment Share on other sites More sharing options...
DonaldFaulknor Posted January 31, 2012 Author Share Posted January 31, 2012 ahh, that's right. I heard something about needing the JOIN, I'll try to work with that. Thanks very much! Just to verify, how should my tables be setup for that to work? This is from how I think I remember setting them up... CREATE TABLE `Friends` ( `friend_id` int(11) NOT NULL, `users_id` int(11) NOT NULL DEFAULT '0', `friend_name` varchar(80) COLLATE latin1_general_ci DEFAULT NULL, PRIMARY KEY (`friend_id`,`users_id`), KEY `users_id` (`users_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; CREATE TABLE `Statuses` ( `users_id` int(11) NOT NULL DEFAULT '0', `statuses` text COLLATE latin1_general_ci, `whens` datetime DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; It doesn't look like I set up a relationship with these two tables, if I remember correctly, I was having problems with syntaxing and stuff. I'm relatively new to setting up tables through sql query containing constraints and relationships. Quote Link to comment Share on other sites More sharing options...
Adam Posted January 31, 2012 Share Posted January 31, 2012 You don't necessarily need a defined foreign link between two tables in order to JOIN them, but it is highly encouraged to keep your tables in good shape. Personally I would get the foreign keys working before proceeding -- you'll need to use the InnoDB engine, not MyISAM. Are those all of the tables? You're missing a 'users' table. Storing the friend's name within the relationship table is very bad practise - what if they change their name later? You're going to have to do a - possibly very large - update on the table. Large updates cause a bottleneck due to table locks and will slow down your application. Instead you should use a JOIN to pull in their name from the alluded 'users' table, as you query for the data. That way you're "normalising" that piece of data, by only storing it once. Also you should store the user content in UTF-8, especially since you may have users from around the world posting statuses. Quote Link to comment Share on other sites More sharing options...
DonaldFaulknor Posted January 31, 2012 Author Share Posted January 31, 2012 yes, I have several more tables, including a user's table. But how would I add friends to the user's table? If I add a column "friends" I'm only going to have 1 field for each user, meaning only one friend. Unless I misunderstood. And the users_id is auto incremented on registration, that's the number I use for any relationships, that number can't be changed, so will always remain the same. How would I change to InnoDB? I just need the basic query, no long explanation, I'll understand the concept of the query just by looking at it and know how to do it in the future. Help is greatly appreciated, Thank You. Quote Link to comment Share on other sites More sharing options...
DonaldFaulknor Posted January 31, 2012 Author Share Posted January 31, 2012 And I set up a foreign key, if I'm not mistaken, for this purpose the foreign key would go from the friends table to the statuses table correct? ALTER TABLE Friends ADD FOREIGN KEY(friend_id) REFERENCES Statuses(users_id) Quote Link to comment Share on other sites More sharing options...
DonaldFaulknor Posted January 31, 2012 Author Share Posted January 31, 2012 oh... I see what you mean about the friends name. Well, I learned quite a bit since I had set that up and at the time, I couldn't get the friend's name to display on the friends page of someone's profile, so I had it insert into the database. Thinking about it, that would be a big pain in the butt. Quote Link to comment Share on other sites More sharing options...
Adam Posted January 31, 2012 Share Posted January 31, 2012 yes, I have several more tables, including a user's table. But how would I add friends to the user's table? If I add a column "friends" I'm only going to have 1 field for each user, meaning only one friend. Unless I misunderstood. And the users_id is auto incremented on registration, that's the number I use for any relationships, that number can't be changed, so will always remain the same. I was referring to the Friends.friend_name column; keep the relationship table, but remove this column and use a JOIN to retrieve the friend's name. How would I change to InnoDB? The MySQL manual has a page dedicated to converting tables to InnoDB. I just need the basic query, no long explanation, I'll understand the concept of the query just by looking at it and know how to do it in the future. I appreciate that, but it's worth understanding first why you should do something in a particular way, rather than just doing it. This is roughly the query you would need: select s.statuses, s.whens, u.id, u.name from Statuses s join Users u on (s.users_id = u.id) join Friends f on (u.id = f.friend.id) where f.users_id = 123 order by s.whens; Had to guess the users' table column names, and obviously couldn't run this myself to make sure it worked, but should do the trick. Replace 123 with the user's ID and add a LIMIT clause. Quote Link to comment Share on other sites More sharing options...
DonaldFaulknor Posted January 31, 2012 Author Share Posted January 31, 2012 Thanks very much guys. I actually got the JOIN to work. I tend to understand things very fast, I took the SELECT statement shared from above and had to modify it... "SELECT Statuses.users_id, Statuses.statuses, Statuses.whens FROM Friends JOIN Statuses on Statuses.users_id = Friends.friend_id WHERE friend_id=Friends.friend_id"; it worked. the WHERE clause wasn't meant to be the session id (person logged in), it was meant to be the friend, which is represented by friend_id in the Friends table. Also, there was suppose to be commas between the tables, an easy to notice syntax error. I got it working and tested. I posted to my profile and my mother's homepage received the update. I'm on my way to a halfway descent social networking site. Wish me luck. I'll probably be back soon for something or other. I'll come and participate in answering questions when the main part of my site is up and running (a few more days). And when I get my first $500 million I'll come and share some with ya'll. lol Quote Link to comment Share on other sites More sharing options...
Adam Posted January 31, 2012 Share Posted January 31, 2012 I'm glad you're making progress, but you realise that will just return a list of *all* statuses? [...] WHERE friend_id=Friends.friend_id You're comparing Friends.fiend_id to Friends.friend_id there, which will always be true. Quote Link to comment Share on other sites More sharing options...
AyKay47 Posted January 31, 2012 Share Posted January 31, 2012 the where clause is what row from the left table the query will actually grab, the join simply joins columns onto the main search however you tell it to with the ON clause. I am assuming that you would want friend_id equal to the user of the current session. Quote Link to comment Share on other sites More sharing options...
DonaldFaulknor Posted February 1, 2012 Author Share Posted February 1, 2012 Well, what I have set now currently works. It returns all statuses of that particular friend UNTIL it reaches the last status, I also added the ORDER BY whens Desc as well so they're ordered by date (most recent up top), now, I'm not exactly going to want 4,000 messages on one page, so... I plan on having the LIMIT option. What do you think is a good number? limit to 30 results? Quote Link to comment Share on other sites More sharing options...
Adam Posted February 1, 2012 Share Posted February 1, 2012 There's a fault in your testing, I guarantee you. You say it matches status updates of all the user's friends right? How does it know who the current user is? If it doesn't know that, then there's no way of finding that user's friends, and so their status updates. All the query is doing right now is retrieving everyones status updates that has a record within 'friends'. So anyone that doesn't have a friend will see nothing, as expected. Anyone that has at least one friend, regardless of who it is, will see everyone's status updates. Quote Link to comment Share on other sites More sharing options...
AyKay47 Posted February 1, 2012 Share Posted February 1, 2012 All the query is doing right now is retrieving everyones status updates that has a record within 'friends'. So anyone that doesn't have a friend will see nothing, as expected. Anyone that has at least one friend, regardless of who it is, will see everyone's status updates. Exactly, OP I will say this one more time before leaving this thread, the query seems to be working as expected probably because you only have a few people in your db table. However once you add more members etc you will quickly find that this query is faulted. The main search needs to connect the statuses of the friends of the session owner, which means that needs to be included in the where clause. If you are going to do something, do it correctly the first time, don't just say "hey it seems to be working". Test it thoroughly. Quote Link to comment Share on other sites More sharing options...
DonaldFaulknor Posted February 1, 2012 Author Share Posted February 1, 2012 ok, so it wasn't working the way I thought it was... <?php $query4 = "SELECT Statuses.users_id, Statuses.statuses, Statuses.whens, Friends.friend_name FROM Friends JOIN Statuses on Statuses.users_id = Friends.friend_id WHERE friend_id=Friends.friend_id ORDER BY whens DESC"; $result4 = mysql_query($query4); while($row4 = mysql_fetch_assoc($result4)) { echo '<p style="padding:12px 12px 12px 6px; font-family:bookman old style; margin-left:198px"><strong><span style="color:#cc0000;">Posted By </span>'; echo $row4['friend_name']; echo '</strong><br />'; echo $row4['statuses']; echo '<br />'; echo $row4['whens']; echo '</p>'; } ?> I don't believe I have any problems within my while loop. The problem exists in the query. on the page this appears on (homepage when logged in) it is 'meant' to show status messages of all your friends and your own status messages. The friends statuses is working just right... but, your own status messages is repeated twice. Before the question comes up... no I am not on my own friends list, I double checked that and also made that not possible. Thanks for the help. Quote Link to comment Share on other sites More sharing options...
DonaldFaulknor Posted February 1, 2012 Author Share Posted February 1, 2012 OHHHH... it's pulling up every situation where I'm a friend of someone. I deleted the connection between me and my mother, only leaving the one connection of me and my friend, and it deleted the duplicate entries. That's the problem, I think to fix that, I need to include my session id in place of users_id, it doesn't make sense not to have my specific session id (me logged in) into the query. Quote Link to comment Share on other sites More sharing options...
AyKay47 Posted February 1, 2012 Share Posted February 1, 2012 WHERE friend_id=$_SESSION['user_id'] Quote Link to comment Share on other sites More sharing options...
Adam Posted February 1, 2012 Share Posted February 1, 2012 Quote Link to comment Share on other sites More sharing options...
AyKay47 Posted February 1, 2012 Share Posted February 1, 2012 lol.. Quote Link to comment Share on other sites More sharing options...
DonaldFaulknor Posted February 1, 2012 Author Share Posted February 1, 2012 ok, I'm close... $query4 = "SELECT Statuses.users_id, Statuses.statuses, Statuses.whens, Friends.friend_name FROM Friends JOIN Statuses on Statuses.users_id = Friends.friend_id WHERE Friends.users_id=$friend && Statuses.users_id=$uid OR Friends.users_id=$uid AND Statuses.users_id=$friend ORDER BY whens DESC"; This displays my statuses on my homepage once - an improvement from the duplicate entries. Now, if I replace the variables $friends I have above there, with a user id I know exists and has statuses (such as my mother's account), then all her statuses will show on my homepage as well. What kind of replacement would I make to get all my friends into that variable or where those variables exist? Quote Link to comment Share on other sites More sharing options...
Adam Posted February 1, 2012 Share Posted February 1, 2012 You know I posted a query that will do what you want (and even join on the users table) about 12 posts ago, right? Quote Link to comment Share on other sites More sharing options...
DonaldFaulknor Posted February 1, 2012 Author Share Posted February 1, 2012 I believe I tried it, let me go check that, I'll get back with you. 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.