HELP With mysql PLEASE!!

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
SHADOWREAPR
Forum Newbie
Posts: 4
Joined: Tue Jul 12, 2011 4:18 pm

HELP With mysql PLEASE!!

Post by SHADOWREAPR »

This is driving me crazy!!!!!!!!! :evil: I have a website, coded in php, and with a working login system implemented. I am trying to add a way for my users to add each other as a friend and their friends would be displayed on their account page. I know how to fetch the table contents, so the friend requests and displaying the friends isn't the problem. so how would i go about adding the data to the database? I can't just add all the names into a field called "friends" because i need to be able to link to the account page (which adjusts to each user via a user variable) . please include sql/php code if possible. Thanx for your time.



SHADOWREAPR
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: HELP With mysql PLEASE!!

Post by califdon »

Your database needs to contain 2 relational tables, then your queries that involve friends will require joins--that is, one query returns related rows from both tables.

Your existing table of users probably won't require any changes, assuming that it includes a primary key field, preferably an auto-increment number that is guaranteed to be unique (no duplicates). The new table (which you might name "friends") must contain 2 fields: one will be the foreign key to the person who is friending someone else and the other will be the foreign key to the person being friended. That means that for every friend relationship there will be 2 rows in the "friends" table. Here is how it might look:

Code: Select all

Users table:                          Friends table:
--------------------------------      ------------------
ID    username  datejoined  etc.      ID1       ID2

0001  Sally     2011-02-14  xxx       0003      0001
0002  John      2011-02-20  yyy       0001      0003
0003  George    2011-03-02  zzz       0002      0001
0004  Betty     2011-03-03  aaa       0001      0002
0005  --etc.
That establishes that Sally has friended John and Betty, both of which were accepted. John hasn't been friended, nor friended anyone else.

Then if you want to show all of Sally's friends, your query will be like:

Code: Select all

$sql = "SELECT users.username, friends.username FROM users INNER JOIN friends ON ID1 = ID";
There's more to it than that, but that's the approach. Maybe you could do it with only 1 row per "friendship". I would have to give it more thought. But in any case, it is a classic case for relational tables.
SHADOWREAPR
Forum Newbie
Posts: 4
Joined: Tue Jul 12, 2011 4:18 pm

Re: HELP With mysql PLEASE!!

Post by SHADOWREAPR »

Thanx 4 the reply!!! I thought about that, but i really didn't want to use joins since im totally new to them. I am going to, though, because i see no other way.
I do have a few more questions though.

1.)my id field in the user table is named "userid" . does the id1 and id2 fields in the "friends" table need to be named "ID1" and "ID2" or "userid1" and "userid2"?

2.)Does the type/collation have anything to do with the effectiveness of joins?

3.)how would i do the "Add as a Friend button" so that It goes from Add to Pending and then disappearing if an when the users are friends?

4.)Do i use a mysql array like this:

Code: Select all

$result = mysql_query("SELECT users.username, friends.username FROM users INNER JOIN friends ON userid1 = userid");

while($row = mysql_fetch_array($result))
  {
//enter code here
}
Please help, I don't normally go this far into mysql, i'm more of a php/html/asp guy



SHADOWREAPR
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: HELP With mysql PLEASE!!

Post by califdon »

SHADOWREAPR wrote:Thanx 4 the reply!!! I thought about that, but i really didn't want to use joins since im totally new to them. I am going to, though, because i see no other way.
It's not a matter of choice. When you need to represent a one-to-many relationship between entities (each user may have an unlimited number of friends), the only way to represent that is in a relational database with 2 properly structured tables. You may not want to get bogged down with theory, but it really would help you to look for a couple of relational database tutorials. There are lots of them online. It's not really that hard, but it IS DIFFERENT from other kinds of programming and web design, and to do it successfully requires that you learn new ways of thinking about data.
I do have a few more questions though.

1.)my id field in the user table is named "userid" . does the id1 and id2 fields in the "friends" table need to be named "ID1" and "ID2" or "userid1" and "userid2"?
The field names can be anything you want. You do the mapping in the join.
2.)Does the type/collation have anything to do with the effectiveness of joins?
Yes. Primary key fields should ordinarily be integers. In every case, the corresponding foreign fields (in the other table) MUST be the same data type as the primary key.
3.)how would i do the "Add as a Friend button" so that It goes from Add to Pending and then disappearing if an when the users are friends?
There is no single way to do this, but making these decisions is what designing a database consists of. In re-thinking your application, my inclination is to suggest that you may need only one row in the friends table to represent a friendship, but you might need to add a 3rd field to the table, indicating that the person has accepted the friend request, such as the date it was accepted. If this field is Null, it means that the request hasn't been accepted yet. This will make the query somewhat more complex, but once you've worked it out, it is probably the better solution.

So I would revise my earlier description of how the tables would look:

Code: Select all

Users table:                          Friends table:
--------------------------------      -------------------------------------
ID    username  datejoined  etc.      ID1       ID2       acceptdate

0001  Sally     2011-02-14  xxx       0001      0003      2011-02-15
0002  John      2011-02-20  yyy       0001      0004      2011-02-21
0003  George    2011-03-02  zzz       0002      0004
0004  Betty     2011-03-03  aaa 
0005  --etc.
This would represent that Sally has sent friend requests to George and to Betty and both have accepted, and John has sent a friend request to Betty, who has not replied (no date). One row in friends for each friend request. Don't get confused and read any meaning into what is lined up between the 2 tables.

But try to approach the design this way: first, design the database. You just can't write the code logic until you've settled on how the data will be stored. If you were to use the approach I described in the previous paragraph, each time a user sends a friend request to another user, I would INSERT a new row in the friends table, with the 2 foreign keys and a 3rd field left blank (Null). When the request is accepted by the 2nd user, your PHP logic would use an UPDATE query to set the 3rd field to the current date for the row that has the appropriate foreign key values.

When listing friends of a user, your query would have a WHERE predicate that returns only those rows where one of the foreign keys matches the main user's ID AND the other matches the friend user's ID AND the 3rd field contains a date. It might look something like this (but I haven't spent any time really designing this, so this is only a general idea of what it might require):

Code: Select all

$sql = "SELECT username FROM friends, users WHERE ((friends.ID1 = $searchID) OR (friends.ID2 = $searchID)) AND (friends.acceptdate IS NOT NULL)";
That is the other syntax for a join, somewhat easier in this example than using ... JOIN ON ... It means that you are selecting rows from 2 tables (friends and users) that meet the conditions expressed in the WHERE predicate. Assuming that you have previously assigned the value of the ID of the user for whom you are searching for friends to the variable $searchID, the conditions are that each returned row (from which you return only the username) must be that either ID1 OR ID2 matches the $searchID (this includes friend requests that were originated by either party) AND that the accepted field is True (or with the other option, that the acceptdate field is not Null).

As I said, there's a lot more to it than this. You still have to design the logic for requesting a friend, for accepting a friend request and updating the appropriate row, etc.
4.)Do i use a mysql array like this:

Code: Select all

$result = mysql_query("SELECT users.username, friends.username FROM users INNER JOIN friends ON userid1 = userid");

while($row = mysql_fetch_array($result))
  {
//enter code here
}
It's not a MySQL array. You can fetch a row from a database as a PHP array or a PHP object. The main difference is in what you're going to do with the data returned by your query. You have the general idea there, after connecting to the MySQL server and selecting the database, you assign a handle to the results of a query ($result or whatever you like to name it), then in a WHILE loop, you use one of the mysql_fetch_...() functions as long as there are rows left in the results and do something with what is returned. If you want to list all the friends of a particular user, you use PHP to build the HTML to be sent to the browser, perhaps something like:

Code: Select all

while($row = mysql_fetch_assoc($result))
  {
      echo $row['username']."<br />";
  }
Please help, I don't normally go this far into mysql, i'm more of a php/html/asp guy
Well, this really isn't much different from ASP, it's just the database aspects that are different.
SHADOWREAPR
Forum Newbie
Posts: 4
Joined: Tue Jul 12, 2011 4:18 pm

Re: HELP With mysql PLEASE!!

Post by SHADOWREAPR »

thanx4 all ur help!!!! i figured out how to do it using the usernames. when a user (bob) logs in to my site, his username is set as a variable in a session ($session->username) i just set up the 2-field system (username and friend) and each name goes in each field once
ex.)

username friend
sally bob
bob sally

then i just query the table and display every entry that has bob at the username and use mysql rows to display the results!
use a little css and javascript for cosmetics and BOOM!

i no its not the best way to do this, but, seeing as this is just a site for my friends and i, as long as it works, it works!

i used a separate table (friend_fequests) 4 the requests.
I have everything working, so thank you for devoting time to help my problem
that use 2 fields suggestion really jogged my mind



SHADOWREAPR
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: HELP With mysql PLEASE!!

Post by califdon »

You're welcome and I'm glad you got something working. The weakness of using names is that it prohibits you from using the same name for more than one user. It may work in your limited situation, though. Just be aware that if some day you want to expand the concept to another site, you will probably not be able to use that technique. A good rule of thumb for practical databases is that nearly every table should have a unique identifier Primary Key. It saves you a lot of redesign time to just always put an auto-increment ID field in to begin with, even if you don't think you need it, then you won't have to go back later and modify your table structure when you find out that you need it. But as your first step into databases, you can enjoy the satisfaction of getting something to work.
SHADOWREAPR
Forum Newbie
Posts: 4
Joined: Tue Jul 12, 2011 4:18 pm

Re: HELP With mysql PLEASE!!

Post by SHADOWREAPR »

considering that my site prohibits two users from having the same username, its not much of a problem since their actual names can match!
I will be headed to college this fall to take a class in web design and database opperation/management so i will probably learn how do set up my structure as you have described, but 4 now im content thanx again





SHADOWREAPR
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: HELP With mysql PLEASE!!

Post by califdon »

SHADOWREAPR wrote:considering that my site prohibits two users from having the same username, its not much of a problem since their actual names can match!
I will be headed to college this fall to take a class in web design and database opperation/management so i will probably learn how do set up my structure as you have described, but 4 now im content thanx again
First of all, good luck with college! It will be an exciting and challenging time in your life. Having taught web and database courses in college for years, before my retirement, I have a good idea of what you will get into, although perhaps not in your first year. One of the things you will learn is to design software in standard ways, rather than just to meet what you think are the initial minimum requirements, so that when conditions change (and they always will), you will be able to modify your software, not start all over again from scratch. For now, it's fine that you got something to work that meets your immediate needs, but for you to go further in preparing for a career, you will need to develop the mind-set that anticipates future changes. But for now, you've made a good start and deserve to enjoy it. I wish you well at college.
Post Reply