Well, my first impulse is to have SEPARATE Queries and then splice things back together, but that seems challenging when it comes to PHP’s Prepared Statements.
pseudocode (this is the mysql forum) …
[indent]run query 1
run query 2
display results of query 1
display results of query 2[/indent]
Well, this thread may be one for the PHP Forum, but like I originally said, I think it also sounds like an Advanced Database/Stored Procedure question as well.
Let me show you some more details…
member
- id
- email
- username
- password
- first_name
- photo_name
- location
(While I could break out the User’s Info into another table, in this context and state it is fine…)
comment
- article_id
- member_id
- body
- status
article
- id
- slug
- heading
- body
Here is my current Query that I use to get the Users’ Info and Users’ Comments…
$q2 = 'SELECT m.first_name, m.username, m.photo_name, m.photo_label,
m.location, m.created_on, m.logged_in, m.last_activity,
c.created_on, c.body, c.status
FROM member AS m
INNER JOIN comment AS c
ON m.id = c.member_id
WHERE c.status="Approved" AND c.article_id=?
ORDER BY c.created_on';
So I have that quesry in my PHP section at the top of my file, and then down in the HTML section, I am currently outputting the data like this…
// ********************************
// Display Comments on Article. *
// ********************************
while (mysqli_stmt_fetch($stmt2)){
echo '<div class="post">';
// ********************
// Display User Info. *
// ********************
echo ' <div class="userInfo">
<a href="#" class="username">
<strong>' . nl2br(htmlentities($username, ENT_QUOTES)) . '</strong>
</a>';
AND SO ON AND SO FORTH...
// ************************
// Display User Comments. *
// ************************
echo ' <div class="userComments">
<p class="commentDate">Posted on: ' . date('Y-m-d g:ia', strtotime($createdOn)) . '</p>
<p>' . nl2br(htmlentities($comments, ENT_QUOTES)) . '</p>
</div>
</div>';
}
?>
</div><!-- End of COMMENTS SECTION -->
That query works fine for basic info, but if I want to start getting fancier, e.g.
- User’s # of Posts
- User’s Friends
- User’s Interests
- Last 5 Articles User Read
…and so on, then I clearly can’t do that from one query?! (I suppose using Sub-Queries, anything is possible, but I tend to like to break more complex problems up into smaller pieces versus making things even more complicated!!)
I think the key concept I am stuck on is - regardless of how I get the data - how do I link Username, Online Status, User Photo, User Location, User # of Posts and User Comments so I know they are all from the SAME USER pertaining to the SAME COMMENT?!
sarcasm omitted
Ah, see, you still do sorta care?!
Debbie
