Join Tables Relationships with SQL
Wall Script
Follow Me:
Thursday, May 13, 2010

Join Tables Relationships with SQL

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.

database design

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 ;
labs.9lessons friends
PHP Code
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;

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 ("&lt;","&gt;");
$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;

Hope you like this. Thanks!
Was this article helpful?
Thanks! Your feedback helps us to improve 9lessons.info


22 comments:

  1. Looks nice idea. thanks for sharing.

    ReplyDelete
  2. Gracias, por publicar articulos de calidad!! eres el amo

    ReplyDelete
  3. very best information.give me more on this ...

    ReplyDelete
  4. thank you for fulfilling my request! i really appreciate it

    ReplyDelete
  5. Thank you so much.
    Does you will post the voting part?

    ReplyDelete
  6. Please Help!
    How can I add time and user_id in my SQL Table?
    Please reply I need this!

    ReplyDelete
  7. you can add time using the datatype datetime and user_id using either an int or tinyint datatype.

    ReplyDelete
  8. this 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 ]

    ReplyDelete
  9. Is it really important to have different tables (freinds table, username table)instead of one ?

    ReplyDelete
  10. How do you add new friends? Is it a button with some sql behind it?

    ReplyDelete
  11. yea..How do you add new friends? Is it a button with some sql behind it??

    ReplyDelete
  12. I'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?

    friend_one = 6, friend_two = 10
    friend_one = 10, friend_two = 6

    what if your database grows enormously? you will have duplicate data.

    ReplyDelete
  13. Following and Follower concept. If you have better solution for this please suggest.

    ReplyDelete
  14. No sabes lo que me sirvio este tuto!!! mil gracias y felicitaciones ... tenes ejemplos muy copados

    ReplyDelete
  15. hello srinivas. i got some problem

    my 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?

    ReplyDelete
  16. Hi..some one,, can u help me??
    how 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

    ReplyDelete
  17. Hi Srinivas, how to get all the votes for each status update?

    ReplyDelete
  18. Hi Srinivas, how to get all the votes for each status update?

    ReplyDelete
  19. As always a great post, friends and relationships between database tables can get confusing...

    Thank You

    ReplyDelete
  20. i face a error

    Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in
    friends.php on line 11

    ReplyDelete
  21. Thank you! It was helpful. And previous lesson too.

    ReplyDelete

Make in India