Page 3 of 3

Re: friendlist database structure...any suggestions appreciated

Posted: Wed Sep 16, 2009 9:02 am
by superdezign
VladSun wrote:
Once upon a time ... a software developer was told by his wife:
- Go to the supermarket and take a piece of sausage. And if they have eggs take 10.

... developer is back from shopping with 10 pieces of sausage ...

Guess what - there were eggs in the supermarket.
;)
Lmao! Is it bad that I thought the exact same thing as the developer in your story?
VladSun wrote:I think a JOIN will always used ... maybe you should give us an example?

Code: Select all

// friendslist.php
echo '<h1>Friends List</h1>';
$data = $database->query("SELECT * FROM `users` LEFT JOIN....");// OMG! Vlad is right o.o
Wow... I guess so. I'm used to using my data object classes, so my code actually looks like this:

Code: Select all

$friendship = new Data_Friendship();
$friendship->userId = $userId;
$friendshipCollection = new Vol_Database_DataCollection($friendship);
$friendshipCollection = $friendshipCollection->fetchAll()->getCollection();
$friends = array();
 
foreach ($friendshipCollection as $friendship) {
    $friend = new Data_User($friendship->friendId);
    $friends[] = $friend->fetch();
}
I haven't actually used JOINs in a long time. Data objects are just too convenient. :3
So I guess that at this point... My argument is that my JOIN has less comparisons, making it marginally faster...? :lol:

Code: Select all

SELECT * FROM users LEFT JOIN friendships ON friendships.user_id = users.id WHERE friendships.user_id = $USER_ID;

Re: friendlist database structure...solved

Posted: Sat Sep 19, 2009 3:17 pm
by scarface222
LOL that was a pretty intense argument. I gotta be honest, I lost you guys once you started talking about sausages and eggs. Just one last question, Superdezign is that your query to select from your earlier suggested table design? Also what does LEFT JOIN and RIGHT JOIN do. After I can decide what I want to do.

Re: friendlist database structure...any suggestions appreciated

Posted: Sat Sep 19, 2009 5:50 pm
by superdezign
Well, from this debate, the consensus (the aspects that we have 2/3 agreement on) is that you should use 2 entries for each mutual friendship, and that you should use a status field instead of multiple tables. So, basically, the first post I made is the structure you want to use.

A LEFT/RIGHT JOIN adds the data of one table to another in a queries results. So:
[sql]SELECT * FROM `users`LEFT JOIN `friendships`ON `friendships`.`friend_id` = `users`.`id`WHERE `friendships`.`user_id` = $USER_ID;[/sql]

This query selects all of the data from the users table of the user with the friend_id that is related to the current user's user_id in the friendships table. This is how you use one table to get data from another. In this case, we are using the friendships table to get data from the users table.

Re: friendlist database structure...any suggestions appreciated

Posted: Sun Sep 20, 2009 11:18 am
by scarface222
So in that sense whether the user_id in question is user_id or friend_id in the friendships table, all the opposite corresponding friend entries will be selected? If so thanks man, for the help and thank you everyone else but if facebook does it then its good enough for me. This way is easier for me as well.

Re: friendlist database structure...last question superdezign

Posted: Tue Sep 29, 2009 1:20 pm
by scarface222
Hey superdezign I just have one more question and then this topic is done. I am trying to check before hand if a user is friends with another user (meaning there are two opposite entries in the friendship table) before the message is sent. What is the best way to query. I tried this but obviously it does not work. Is is possible to do it in one query or do I have to use mysql_fetch_assoc and do multiple queries?

Code: Select all

if (isset($_POST['from']))
{
$to=$_POST['to'];
$from=$_POST['from'];
$subject=$_POST['subject'];
$message=$_POST['message'];
$date=date("Ymd");
//seconds
 
$friend_check="SELECT * FROM friendships
LEFT JOIN users
ON users.id = friendships.friend_id 
WHERE users.username = '$from'";
$friend_result=mysql_query($friend_check) or die('Error, check query failed');
$count = mysql_num_rows($friend_result);
    
    
if ($count>=1){
    
 
 
$friend_check="SELECT * FROM friendships
INNER JOIN users
ON users.id = friendships.friend_id
WHERE users.username = '$to'";
$friend_result=mysql_query($friend_check) or die('Error, check query failed');
$count1 = mysql_num_rows($friend_result);
 
if ($count1>=1){
 
 
    print "OK";
$query= "INSERT into messages VALUES ('', '$to', '$subject', '$message', '$from', '$date')";
$query1=mysql_query($query);
}
}
else{
    print 'You and this user are not friends';
return;
}
}
 
?>

Re: friendlist database structure...last question superdezign

Posted: Tue Sep 29, 2009 3:16 pm
by VladSun
Don't use usernames in your program flow - use the user IDs instead...

Re: friendlist database structure...last question superdezign

Posted: Tue Sep 29, 2009 4:54 pm
by scarface222
Thanks for the response man but I am not quite sure what you mean. Do you mean replace the $to, $from with the corresponding ids? But then wouldn't I have to query to get those ids and even if I did that, is there a way to query only one time to check if the $to and $from users are friends? Finally what is the difference between inner join, left join, and right join, I looked it up but did not understand plus they all seem to do the same thing.

Re: friendlist database structure...last question superdezign

Posted: Tue Sep 29, 2009 5:47 pm
by VladSun
I mean that you must redesign your application so it will use only user IDs - user ID is your primary key in users table and foreign key in your friendship table. That's why you must not have $from $to as usernames, but as IDs.

Then your query is very easy to build:

Code: Select all

$friend_check="SELECT * FROM friendships
where friendships.user_id = '$from' and friendships.friend_id = '$to'

Re: friendlist database structure...solved

Posted: Tue Sep 29, 2009 8:56 pm
by scarface222
Thanks man, good point. Silly me.