My previous post Database Design Create Tables and Relationships with SQL. This post is sequel how to join these tables and displaying proper data. I had used these SQL statements at labs.9lessons.info.
Friends
Data relations between users and friends tables. Take a look at the following SQL statement users table object a and friends table object b . Here friend_one = '1' refers to users table user_id value.SELECT a.username, a.email
FROM users a, friends b
WHERE a.user_id = b.friend_two
AND b.friend_one = '1'
AND b.role = 'fri'
ORDER BY b.friend_id DESC
LIMIT 30 ;
FROM users a, friends b
WHERE a.user_id = b.friend_two
AND b.friend_one = '1'
AND b.role = 'fri'
ORDER BY b.friend_id DESC
LIMIT 30 ;
Contains PHP code. Displaying username srinivas friends results
<?php
$user_id='1'; // User table user_id value$friends_sql=mysql_query("SELECT a.username, a.email FROM users a, friends b WHERE a.user_id = b.friend_two AND b.friend_one = '$user_id' AND b.role = 'fri' ORDER BY b.friend_id DESC LIMIT 30");
while($friends=mysql_fetch_array($friends_sql))
{
$title=$friends['username'];
$email=$friends['email'];
$lowercase = strtolower($email);
$image = md5($lowercase);
$avatar ='http://www.gravatar.com/avatar.php?gravatar_id='.$image;
?>
<a href="/<?php echo $title; ?>" title="<?php echo $title; ?>"><img src="<?php echo $avatar; ?>" border="0"/></a>
<?php
}
?>
Updates
Data relations between users, updates and friends tables. Take a look at the following SQL statement users table object a , updates table object b and friends table object c . Here friend_one = '1' refers to users table user_id value.SELECT a.username, a.email, b.update_id, b.update, b.time, b.vote_up, b.vote_down
FROM users a, updates b, friends c
WHERE b.user_id_fk = a.user_id
AND c.friend_one = '1'
AND b.user_id_fk = c.friend_two
ORDER BY b.update_id DESC
LIMIT 15;
FROM users a, updates b, friends c
WHERE b.user_id_fk = a.user_id
AND c.friend_one = '1'
AND b.user_id_fk = c.friend_two
ORDER BY b.update_id DESC
LIMIT 15;
PHP Code
Contains PHP code. Displaying username srinivas homeline updates
<?php
$user_id='1'; // User table user_id value$update_sql=mysql_query("SELECT a.username, a.email, b.update_id, b.update, b.time, b.vote_up, b.vote_down FROM users a, updates b, friends c WHERE b.user_id_fk = a.user_id AND c.friend_one = '$user_id' AND b.user_id_fk = c.friend_two ORDER BY b.update_id DESC LIMIT 15");
while($row=mysql_fetch_array($update_sql))
{
$username=$row['username'];
$email=$row['email'];
$update_id=$row['update_id'];
$update=$row['update'];
$time=$row['time'];
$up=$row['vote_up'];
$down=$row['vote_down'];
//Avatar
$lowercase = strtolower($email);$image = md5($lowercase);
$avatar ='http://www.gravatar.com/avatar.php?gravatar_id='.$image;
//Update HTML tags filter
$htmldata = array ("<", ">");$htmlreplace = array ("<",">");
$final_update = str_replace($htmldata, $htmlreplace, $update);
// Updates Results Display here
}
?>
Comments
Data relations between users and comments tables. Take a look at the following SQL statement users table object a and comments table object b . Here update_id_fk = '2' refers to updates table update_id value.SELECT a.username, a.email, b.comment_id, b.comment, b.time
FROM users a, comments b
WHERE b.user_id_fk = a.user_id
AND b.update_id_fk = '2'
ORDER BY b.comment_id;
FROM users a, comments b
WHERE b.user_id_fk = a.user_id
AND b.update_id_fk = '2'
ORDER BY b.comment_id;
Hope you like this. Thanks!
Looks nice idea. thanks for sharing.
ReplyDeleteGracias, por publicar articulos de calidad!! eres el amo
ReplyDeletevery best information.give me more on this ...
ReplyDeletethank you for fulfilling my request! i really appreciate it
ReplyDeleteThank you so much.
ReplyDeleteDoes you will post the voting part?
Please Help!
ReplyDeleteHow can I add time and user_id in my SQL Table?
Please reply I need this!
you can add time using the datatype datetime and user_id using either an int or tinyint datatype.
ReplyDeletethis is working fine for friends updates but if we want personal updates also mingled with friends updates to display how do it like twitter [ my+friends updates at a time what is query ]
ReplyDeleteExtraordinary master..!!
ReplyDeleteIs it really important to have different tables (freinds table, username table)instead of one ?
ReplyDeleteHow do you add new friends? Is it a button with some sql behind it?
ReplyDeleteyea..How do you add new friends? Is it a button with some sql behind it??
ReplyDeleteI've noticed your select statement on Friends assumes that friend_one is the user in question, so if 2 people are friends with each other, then there will be 2 entries into Friends?
ReplyDeletefriend_one = 6, friend_two = 10
friend_one = 10, friend_two = 6
what if your database grows enormously? you will have duplicate data.
Following and Follower concept. If you have better solution for this please suggest.
ReplyDeleteNo sabes lo que me sirvio este tuto!!! mil gracias y felicitaciones ... tenes ejemplos muy copados
ReplyDeletehello srinivas. i got some problem
ReplyDeletemy home feed sql query
SELECT M.msg_id, M.uid_fk, M.message, M.created, U.username FROM messages M, user U, friends F WHERE M.owner=U.uid and F.uid_fk = '$uid' and M.uid_fk = F.friend_id_fk order by M.msg_id desc
but it dont show home feed owner(owner of session) posts. friends posts are ok.
what can be wrong?
Hi..some one,, can u help me??
ReplyDeletehow to display SUGGEST FRIENDS with php and MYsql statement??
for the FRIENDS TABLE , you can see at this link http://www.9lessons.info/2010/04/database-design-create-tables-and.htm
Hi Srinivas, how to get all the votes for each status update?
ReplyDeleteHi Srinivas, how to get all the votes for each status update?
ReplyDeleteAs always a great post, friends and relationships between database tables can get confusing...
ReplyDeleteThank You
i face a error
ReplyDeleteWarning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in
friends.php on line 11
Thank you! It was helpful. And previous lesson too.
ReplyDelete