SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Strategy for combing Disparate Data

    This may be more of a programming question, but I'm sure it is equally a challenge for those who write Stored Procedures.

    Some background...

    My website is content heavy and has lots of Articles. Under each Article, (registered) Users can post their Comments.

    Trying to "add a face to a name", next to each Comment I have the Poster's User Details, including...
    - Username
    - User Online Status
    - User Photo
    - User Location
    - User Number of Posts


    Additional info I might add includes...

    - User's Friends
    - User's Interests


    So my question is this...
    How do I gather the User's Info from multiple Queries and them merge it together with the User's Comment?

    When I just wanted to display Username, User Photo, and User Location, it was just a matter of creating a simple Inner Join between the MEMBER and COMMENT table. But with the User Info listed above, that is no longer possible.

    And I am a loss of how to combine this data in my Prepared Statements.

    Sincerely,


    Debbie

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    .
    How do I gather the User's Info from multiple Queries and them merge it together with the User's Comment?
    by displaying the results of those multiple queries

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    by displaying the results of those multiple queries

    Sarcasm - which you seem to have a lot of lately - doesn't help me out...


    Debbie

  4. #4
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    The only thing I can think of to bring disparate data into the same record is to run Subqueries?!

    Maybe something like...
    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, **COUNT OF # OF POSTS GOES HERE**
    			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';

    Debbie

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    The only thing I can think of to bring disparate data into the same record is to run Subqueries?!
    why do you feel you have to combine the queries?

    pseudocode (this is the mysql forum) ...


    run query 1
    run query 2

    display results of query 1
    display results of query 2



    sarcasm omitted

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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($usernameENT_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($commentsENT_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

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    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?![/b]
    by userid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    by userid
    r937, your one and two word responses only make a frustrating situation worse...

    And I wasn't asking about key values!!


    Debbie

  9. #9
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Mods, maybe this thread would be better in the PHP Forum...

  10. #10
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    What is a "post" defined by:

    1.) Articles + Comments
    2.) Articles
    3.) Comments

    Would users "friends" and "interests" merely be the number of each?
    The only code I hate more than my own is everyone else's.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Mods, maybe this thread would be better in the PHP Forum...
    because it's more of a programming question

    see? more than two words

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    because it's more of a programming question

    see? more than two words

    I'm not sure which it is. God knows you've been no help...

    Too busy trolling I guess...


    Debbie

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    God knows you've been no help...
    that's a bit of an exaggeration

    how many threads have you posted in this forum? how many have i helped you with?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    that's a bit of an exaggeration

    how many threads have you posted in this forum? how many have i helped you with?
    You used to help me a lot.

    Now you just tell me to go look up the answer myself or you tease me like in this thread, even though I have put A LOT of effort thinking about and typing up my problem.

    If you don't want to help, that is fine, but please don't play around.


    Debbie

  15. #15
    Mouse catcher silver trophy Stevie D's Avatar
    Join Date
    Mar 2006
    Location
    Yorkshire, UK
    Posts
    5,892
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    You used to help me a lot.

    Now you just tell me to go look up the answer myself or you tease me like in this thread, even though I have put A LOT of effort thinking about and typing up my problem.
    Debbie - since 1st January, you have started over 80 threads, asking for help across pretty much the whole spectrum of website design and management, you have made nearly 400 posts in those threads, but you have not made one single contribution to anyone else's threads.

    Sitepoint Forums is a community, not a free consultancy service. You've taken the equivalent of tens of thousands of dollar's worth of help from the very generous people here, and you've given nothing except demands for more work. You have absolutely no right to get cranky when the people who have already given you masses of help for free suggest that, just maybe, once in a while, you might do something apart from coming on here and demanding help.

    The community only stays alive while enough people are willing to help others out. Why don't you, from time to time, drop in on other people's threads and offer them some help?

  16. #16
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Stevie D View Post
    Debbie - since 1st January, you have started over 80 threads, asking for help across pretty much the whole spectrum of website design and management, you have made nearly 400 posts in those threads, but you have not made one single contribution to anyone else's threads.

    Sitepoint Forums is a community, not a free consultancy service. You've taken the equivalent of tens of thousands of dollar's worth of help from the very generous people here, and you've given nothing except demands for more work. You have absolutely no right to get cranky when the people who have already given you masses of help for free suggest that, just maybe, once in a while, you might do something apart from coming on here and demanding help.

    The community only stays alive while enough people are willing to help others out. Why don't you, from time to time, drop in on other people's threads and offer them some help?
    1.) I don't *demand* that anyone help me.

    2.) Being a troll never helps anything, and it is clear that r937 just likes to drag things out for fun which doesn't help SitePoint

    3.) All I do is "take" and give nothing back?? Really?! Maybe you should reflect on the fact that I ask thought-provoking, intelligent questions and my threads often have the Most Viewers, Most Responses, and Highest Rankings. It's people like me that drive SitePoint as much as those who respond. Doubt that? Well that is what people on SitePoint have told me consistently...

    4.) I don't get "cranky" when people don't respond. I do get cranky when people knowingly and willingly screw around because they can.

    5.) I have spent TENS OF THOUSANDS of hours of my time over the years helping people On and Offline.

    Like a guy at a gas station this morning who needed computer help, and I took 20 minutes of my time - making me late - so that he had some solution when we finished.

    Or some kids that I mentored.

    Or food I gave to a homeless person.

    Or money I gave to the needy.

    (Of course since those weren't on Siteoint, I guess they don't count?!)

    You also overlook the fact that I used to be very active on online Forums back in the day before the economy blew up, and I spent countless hours helping other geeks with their computer problems.

    So don't ever try to make me feel guilty about helping others, because it is a big part of my life's work...

    6.) If you or r937 or anyone else feels so strongly, then how about just ignoring me and my threads and then the Forums don't get filled up with non-sense like this back-and-forth we are having...


    Debbie

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm not "screwing around"

    i'm trying to coax you into being more independent and self-reliant

    would you like me to catalogue all your mysql threads which could have been easily answered with a quick search of the manual?

    no, i didn't think so

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i'm not "screwing around"

    i'm trying to coax you into being more independent and self-reliant
    I don't sense that lately...

    You come across as taunting.

    What I want more than anything - counter to Stevie D's nasty accusations - is a stimulating conversations...

    (It took some bumps in our relationship, but I'd like to think that DeathShadow and I get along pretty well now, and I like how he challenges me regularly.)

    What people like Stevie D and you don't seem to get, is that I come to SitePoint as much for companionship and stimulating conversation as for answers...

    If I just wanted answers I would be at Barnes & Noble. But no one can describe CSS quirks using body fluids quite like DeathShadow...

    BTW, I temporarily solved my problem from yesterday. I used a Sub-Query.

    But since a conversation never developed, I never got any ideas of how to solve my larger issue... *sigh*


    would you like me to catalogue all your mysql threads which could have been easily answered with a quick search of the manual?

    no, i didn't think so
    I have started many a time that I DO look in the MySQL Manual and I find it incoherent.

    And my goal is not to read all day trying to find things, so I come here on certain things hoping for quick answers. No sin in that.

    Never once have I held a gun to anyone's head demanding an answer.

    Why is it a crime to throw out sometimes simple questions hoping someone has the answer and the time to respond?!

    Regardless, I go back to my earlier points to Stevie D...

    I think more of my questions are intended to start Conversations, Be Thought-Provoking, Build Better Solutions, and sometimes Start Debates...

    For every "fluff" question I have asked, I have also started some kick-ass threads that others have complimented me on.

    Anyways, the "anti-Christ" - that would be ME - is hungry and is off to get some eggrolls...


    Debbie

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    What people like Stevie D and you don't seem to get, is that I come to SitePoint as much for companionship and stimulating conversation as for answers...
    okay, fine

    next time you post a question in this forum, you'll get a nice conversation about dim sum

    but i'm through just handing you answers or reading the "incoherent" manual for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •