Originally Posted by
r937
why do you feel you have to combine the queries?
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) ...
run query 1
run query 2
display results of query 1
display results of query 2
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
Code:
- 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
Code:
- article_id
- member_id
- body
- status
article
Code:
- id
- slug
- heading
- body
Here is my current Query that I use to get the Users' Info and Users' Comments...
Code:
$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...
PHP Code:
// ********************************
// 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
Bookmarks