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.