My web programming class

Yesterday we received another assignment. This is a group project that requires us to make a "basic" social networking site. If you'd like, you can look at the hand-out here: https://lh.rs/Z6X8Hx8xaUfS

I thought I'd make a thread about it to get some activity going in this section, and so you guys could test some stuff and give some feedback once I get started.

If you need any help with anything, just message me on IM or post in this forum and link me on IM, and I'll contribute input. I mean, not about aesthetics or functional concepts of social networking, but any of the programming aspects you may have questions about.

Sounds fun. Is this a college course?

How much time do you have to complete the assignment? I'm interested in seeing how you guys approach the assignment.

Quote:

---

Originally Posted by Riddle

Sounds fun. Is this a college course?

How much time do you have to complete the assignment? I'm interested in seeing how you guys approach the assignment.

---

It is a college course and we have 3 weeks to complete it before we have to do a presentation. I'll be meeting with my entire group on Friday to come up with a game plan.

Quote:

---

Originally Posted by Stapled

It is a college course and we have 3 weeks to complete it before we have to do a presentation. I'll be meeting with my entire group on Friday to come up with a game plan.

---

Sounds fun. Good luck, hopefully you don't have an incompetent group. :P

Maybe I'll post some stuff about my Senior Design Project once it's completed. :O

6 degrees of separation!

>social networking site
>3 weeks

I feel bad for your classmates who think that they can make a Facebook competitor. Not even in these 3 weeks, but just ever.

Quote:

---

Originally Posted by GAMEchief

>social networking site
>3 weeks

I feel bad for your classmates who think that they can make a Facebook competitor. Not even in these 3 weeks, but just ever.

---

I don't think any of my classmates think they'll even finish. So many people have already dropped the course and a lot of the people that are still in it do thik they can pass.

I thought the assignment stated that it would be a watered down version of a social network. Why would they even think they could make a FB competitor in 3 weeks? o-o

If you stick to the assignment, you should have enough time to finish it just by putting like 5 hours a week per person. To be honest, the hardest part in my opinion is the graphics. The graphics always take me forever. D:

Does anyone know how to go about storing a users friends? I thought about just using one column and storing all the friend id's separated by commas or something like that.

Quote:

---

Originally Posted by Stapled

Does anyone know how to go about storing a users friends? I thought about just using one column and storing all the friend id's separated by commas or something like that.

---

That's a terrible idea, and flies in the face of database normalisation principles :p You could do something like this:

http://i.minus.com/jTIZV7dE7nQJn.png

So friends is a separate table with the following properties:

• User Id: fk to userId in Users table (the user who initiated the friends request)
• recipientId: fk to userId in Users table (the user who is the recipient of the friends request)
• dateAdded: date the friend request was added
• status: status of the friends request (i.e. 0 = pending, 1 = accepted, 2 = declined)

There's therefore a one:many relationship between the two tables, and it's fairly easy and manageable to retrieve a users friends ;)

Quote:

---

Originally Posted by Artificial

That's a terrible idea, and flies in the face of database normalisation principles :p You could do something like this:

http://i.minus.com/jTIZV7dE7nQJn.png

So friends is a separate table with the following properties:

• User Id: fk to userId in Users table (the user who initiated the friends request)
• recipientId: fk to userId in Users table (the user who is the recipient of the friends request)
• dateAdded: date the friend request was added
• status: status of the friends request (i.e. 0 = pending, 1 = accepted, 2 = declined)

There's therefore a one:many relationship between the two tables, and it's fairly easy and manageable to retrieve a users friends ;)

---

Code:

---

function send_friend_req($sender,$target){
  $link = mysqli_connect('localhost', 'root','','social');
  $date = date("Y-m-d");
  $query = mysqli_prepare($link, "INSERT INTO `friends` (`id`, `recipientId`, `dateAdded`, `status`) VALUES (?,?,?,'0')");
  mysqli_stmt_bind_param($query,'iis', $sender, $target, $date);
  mysqli_stmt_execute($query);
  mysqli_stmt_close($query);
}

---

Something like that?

Yea, although it's a fairly bad idea to establish a new connection in every function. Does that mean you're going to establish a connection for every CRUD operation? Database connections are exhaustive, and they can only handle so many simultaneous connections. You're better off using the one connection throughout your application.

Also, do you know anything about constraints? If you don't, it's not a huge deal, but if you do you may want to set them up with your schema. i.e. the foreign key constraints, userId and recipientId should be a composite key, etc. Again, not absolutely required (especially for a small web systems class), but if you were building the application properly you'd want to ensure the database is set up properly.

That's the only function that has a separate connection because I was testing it on the side. This is also my first time doing anything with sql that required me to create the database so I have no clue how to set up foreign keys.

OK, that's not really an issue I guess. Have you had any exposure to JOINs? Your query will have to join the two tables to retrieve the necessary information.

No, I do not.

So, so say you wanted to retrieve the friends of a user with id 1. Obviously if you were to use something like:

Code:

---

SELECT recipient FROM `friends` WHERE `user_id` = 1 AND STATUS = 1;

---

You would get a list of user Ids of all of his accepted friends. However, say you wanted a list of their user IDs, and also their name. It may be tempting to use PHP to iterate through that list of user ids, and perform separate queries to retrieve those details from the users table. However, thats evidently quite inefficient (just 30 friends will require 30 extra queries). It's simpler to just use a join, which basically joins the two tables (with a foreign key relationship) and retrieves the required information. So something like this:

Code:

---

SELECT friends.recipient, users.first_name, users.last_name FROM `friends` LEFT JOINT `users` ON friends.recipient = users.user_id WHERE friends.user_id = 1 AND friends.status = 1

---

That's just a single query, but will return their friends user id, first and last name.

Quote:

---

Originally Posted by Artificial

So, so say you wanted to retrieve the friends of a user with id 1. Obviously if you were to use something like:

Code:

---

SELECT recipient FROM `friends` WHERE `user_id` = 1 AND STATUS = 1;

---

You would get a list of user Ids of all of his accepted friends. However, say you wanted a list of their user IDs, and also their name. It may be tempting to use PHP to iterate through that list of user ids, and perform separate queries to retrieve those details from the users table. However, thats evidently quite inefficient (just 30 friends will require 30 extra queries). It's simpler to just use a join, which basically joins the two tables (with a foreign key relationship) and retrieves the required information. So something like this:

Code:

---

SELECT friends.recipient, users.first_name, users.last_name FROM `friends` LEFT JOINT `users` ON friends.recipient = users.user_id WHERE friends.user_id = 1 AND friends.status = 1

---

That's just a single query, but will return their friends user id, first and last name.

---

Thanks, just one more question. Wouldn't I also have to check recipient seeing as this would only work for the sender?

You're absolutely right :p That was poor thinking on my part xD If you wanted a complete list of the user's friends, you could either alter the query (essentially merge two queries, one on the recipient, one on the sender), or redesign your database schema for that table.

edit: for instance, upon acceptance of a friends request, you may want to make another insert query on to that table, but this time swap the user id and recipient, in that way the above query would still stand. Though you could argue this may lead to data redundancy, and when you removed a friend you'd have to remove both entries. However, the choice is yours.

Quote:

---

Originally Posted by Artificial

So, so say you wanted to retrieve the friends of a user with id 1. Obviously if you were to use something like:

Code:

---

SELECT recipient FROM `friends` WHERE `user_id` = 1 AND STATUS = 1;

---

You would get a list of user Ids of all of his accepted friends. However, say you wanted a list of their user IDs, and also their name. It may be tempting to use PHP to iterate through that list of user ids, and perform separate queries to retrieve those details from the users table. However, thats evidently quite inefficient (just 30 friends will require 30 extra queries). It's simpler to just use a join, which basically joins the two tables (with a foreign key relationship) and retrieves the required information. So something like this:

Code:

---

SELECT friends.recipient, users.first_name, users.last_name FROM `friends` LEFT JOINT `users` ON friends.recipient = users.user_id WHERE friends.user_id = 1 AND friends.status = 1

---

That's just a single query, but will return their friends user id, first and last name.

---

That's not correct. You don't need 30 queries to get the list of 30 friends. You just need 2 queries.
That's what the 'IN' operator is for.

get the list of user ids:
SELECT recipientId FROM friends where userId = ?;

get the list of users with the data from the previous call:
SELECT *
FROM users
WHERE userId IN (id1, id2, id3);

Merging data on the PHP side is a lot more efficient than having the DB do the work. JOINs aren't cheap, and they only work when you have database tables on the same server. Most big sites like Facebook, and Google have 92830492834 web servers, but a _lot_ less DB servers. Doing the work on the web site is a lot easier to scale.

Also, 3 weeks seems like a pretty tight deadline for all that functionality. That must be some pretty bare bones UI.

Quote:

---

Originally Posted by ajaxer

Also, 3 weeks seems like a pretty tight deadline for all that functionality. That must be some pretty bare bones UI.

---

It's a pretty basic site and imo, in a group of 4 people 3 weeks is more then enough time.

I hated doing work in groups. Someone has to keep on top of things for everyone, and make sure that they're doing what they're supposed to. It's so much easier to do things in 2s, or by yourself. I guess I've never been much of a team player.

I was under the impression that provided the column in which you were joining on was UNIQUE, JOIN and IN would essentially resolve to the same execution plan in most database technology.

Also, in your query, it would surely suffice to write it as one query:

Code:

---

SELECT *
FROM users
WHERE userId IN (SELECT recipientId FROM friends where userId = ?);

---

Yes, it can be written with a subquery. But in some cases, that can be slower. In this assignment though, either way will work. I doubt the teacher expects anyone to be an SQL expert.

When it really counts, you can always add 'EXPLAIN' to the beginning of the query and see what the DB says about performance. There are times when the DB has to create temporary tables, and an explain will show that, and that's what you want to avoid. The method to use (join, in, subquery) will depend on the query and what executes faster.

At work, we have DBs spread out across a lot of servers, and the data is usually not on the same server. So we rarely use JOINs.

Quote:

---

Originally Posted by ajaxer

When it really counts, you can always add 'EXPLAIN' to the beginning of the query and see what the DB says about performance. There are times when the DB has to create temporary tables, and an explain will show that, and that's what you want to avoid. The method to use (join, in, subquery) will depend on the query and what executes faster.

At work, we have DBs spread out across a lot of servers, and the data is usually not on the same server. So we rarely use JOINs.

---

Yea, that's fair enough. If OP really was concerned with scalability though, there's any number of optimisations you can make, one of the most important of which is the database technology itself. I suppose a positive for MySQL is it is open source, and can be modified to suit different requirements, however it was never really built to be used across hundreds/thousands of servers. Have you had to make any actual changes to it at work?

@stapled: out of curiosity, what language are you using to build the application?

I'm using PHP.