Join Tables Relationships with SQL

{ 20 comments }

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 ;

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 ("<",">");
$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!