CODEWITHSUNDEEP
phpmysqlsql
user3219007
user3219007

Reputation: 7

Query inside of another query?

I've searched and found ways to do queries inside queries, but I have not found the right answer.

What I am trying to do is to fetch comments from the table called users_profiles_comments and by doing that I will get the field of '.$member["author"].'. Then I want to show who made the comment with their rank and a display picture.

But because there is no user details stored in the comments table because its for comments, I need to create a query inside of my query result where I can have this: 

"SELECT * FROM users WHERE username = $member['author'] ORDER BY `id` ASC"

The '.$member["author"].' value comes from the main query but I need it so it can find fields in my users table. 

$sql = "SELECT * FROM users_profiles_comments WHERE postid = '1' ORDER BY `id` ASC";
$stm = $dbh->prepare($sql);
$stm->execute();
$users = $stm->fetchAll();

foreach ($users as $row) {
    print '

    <img src="'.$member["profilepic"].'" style="float:left;margin-right:10px;" width="80px" height="85pxm">

    <h4>'. $row["author"] .'
                    <small>'. $row["date"] .'</small>
                </h4>
                <p>'. $row["content"] .'</p><hr> </a></li>';
}
echo '
</div>  


  ';

I hope you understand.

Edit: The database.

users Table http://img855.imageshack.us/img855/4387/qyjl.png

users_profiles_comments table https://i.sstatic.net/3JKi2.png

Upvotes: 0

Views: 109

Answers (3)

AlexP
AlexP

Reputation: 9857

But because there is no user details stored in the comments table because its for comments

This is good. You do not want to have your database information duplicated in both tables (de-normalized data) - there is no need.

Generally a foreign key is added to inverse side of the relationship. This would mean you have a user_id column within your comments table, allowing you to select the data in one query using a JOIN

SELECT comment.*, user.name AS comment_author
FROM comment
INNER JOIN user ON user.id = comment.user_id
WHERE user.id = 123

Edit - I flipped to JOIN to return comments rather than users, (as you said "to fetch comments") however both ways will work, it just depends which side of the relationship you are on.

Edit 2 To respond to your comment;

How would I be able to show this on my PHP code on the result?

you can be more specific with the above query (not use *) and render it as follows :

$sql = "
  SELECT 
    comment.`date` as comment_date,
    comment.content, 
    user.name AS author_name, 
    user.profilepic as author_pic 
  FROM
    users_profiles_comments as comment
  INNER JOIN 
    user ON user.id = comment.user_id
  WHERE 
    comment.postid = 1
"; 
//...
$comments = $stmt->fetchAll();

foreach ($comments as $comment) {
  echo '<img src="' . $comment['author_pic'] . '"/>';
  echo '<h4>' . $comment['author_name'] . '<small>' . $comment['comment_date'] .'</small></h4>';
  echo '<p>' . $comment['content'] . '</p>';
}

Upvotes: 2

user2160110
user2160110

Reputation:

You can select member name in SQL statement. You can try this query, this query will bring all the information from two table if users.username = users_profiles_comments.author

WAY 1:

SELECT t1.*, t2.* FROM
(
      SELECT * FROM users WHERE username = '.$member["author"].' ORDER BY `id` ASC
) AS t1
LEFT JOIN
(
      SELECT * FROM users_profiles_comments WHERE postid = '1' ORDER BY `id` ASC
) AS t2
ON t1.username = t2.author

Finally, do your foreach to print

===================================================================

WAY 2:

You can try this also-

SELECT *,
(SELECT profilepic FROM users WHERE users.username = users_profiles_comments.author) AS profilepic
FROM users_profiles_comments WHERE postid = '1' ORDER BY `id` ASC

And print profilepic using $row['profilepic'] in your foreach loop

=====================================================================

WAY 3:

SELECT t1.*, t2.* FROM
(
      SELECT * FROM comments WHERE postid = '1'
) AS t1
LEFT JOIN
(
      SELECT * FROM users
) AS t2

ON t1.author = t2.username

Upvotes: 0

Ruben
Ruben

Reputation: 343

What I understand is that you need to get information about the user from another table called user_profile_comments, based on the user id (correct me if wrong: :-)). Use a left outer JOIN to fetch data from another table, limited on user id.

See http://www.w3schools.com/sql/sql_join.asp for more info.

Upvotes: 0

Related Questions