CODEWITHSUNDEEP
mysqlsql
sudeep
sudeep

Reputation: 170

mysql query: display all entries from guestbook_comments for a specific owner_id

I need some help with query from multiple tables.

My database: I have following tables:

1. users (user_id, user_name, ..) //user_name is unique.
2. guestbook_comments(owner_id, comment_author_id, comment ..)
3. profile_photo(profile_photo_id, user_id, path)

My problem: I want to build a following query:

  1. I have url like guestbook.php?user=sudeep. So i get user_name by $_GET['user']; user_name is unique (in the users table).
  2. I want to display all entries from guestbook_comments for a specific owner_id (but i only know user_name, so i will need to find user_id from users table).
  3. For each of the comment_author_id in guestbook_comments table, I want to get the his user_name from table users and path from profile_photo table.

My approach: First I join users and guestbook_comments table to get all guestbook comments for a specific user_id. Then In the while loop I join users and profile_photo table to get user_name and photo path respectively.

I highly doubt if my approach is any efficient. Can you tell me if there is a right way to do that?

 $sql = "SELECT u.user_id, g.owner_id, g.comment_author_id
FROM guestbook g
INNER JOIN users u ON u.user_id = g.owner_id
WHERE u.user_name = $user_name";

    while($row = mysql_fetch_array( $result )) {        
        $author_id = $row['comment_author_id'];
        $sql = "SELECT u.user_name, p.path
                FROM profile_photo p
                INNER JOIN users u ON u.user_id = p.user_id
                WHERE u.user_id = $author_id;

                //display comment text from guestbook, user_name from users, and photo from profile_photo
    }

Upvotes: 1

Views: 123

Answers (2)

Cade Roux
Cade Roux

Reputation: 89711

Try a single query:

SELECT u.user_id, g.owner_id, g.comment_author_id, g.comment_text, c.user_name, p.path
FROM users u
LEFT JOIN guestbook g
    ON u.user_id = g.owner_id
LEFT JOIN users c
    ON c.user_id = g.comment_author_id
LEFT JOIN profile_photo p
    ON p.user_id = g.comment_author_id
WHERE u.user_name = $user_name

From user, find guestbook entries, find commenters, find commenters' photos

Upvotes: 1

taylonr
taylonr

Reputation: 10790

Am I missing something, or could you combine both joins at once and get all the data with 1 call to the database, rather than 2 calls per user?

 $sql = "SELECT u.user_id, g.owner_id, g.comment_author_id, p.path
FROM guestbook g
INNER JOIN users u ON u.user_id = g.owner_id
INNER JOIN profile_photo p on u.user_id = p.user_id
WHERE u.user_name = $user_name";

Upvotes: 2

Related Questions