Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New Coder
    Join Date
    Jan 2010
    Posts
    68
    Thanks
    3
    Thanked 5 Times in 4 Posts

    Help with filtering results

    I am taking a different approach to my problem (in my previous thread) due to not being able to index the query without it having to use filesort.

    What I'm trying to do is grab all comments for a 'postid' - loop through the results and build a string - displaying the comments and any replies it may have.

    Structure looks like this:

    Code:
    id | parent id
    
    1      0
    2      0
    3      0
    4      3
    5      3
    6      3
    7      0
    id 4,5,6 are replies to id 3


    What I have so far is :

    PHP Code:
    if($stmt $mysqli -> prepare("SELECT id, parentid, username, body, dt, active FROM comments WHERE postid = 12 AND active = 1 ORDER BY id desc"))
    {
        
    $stmt->bind_param('i'$postid);
        
    $stmt->execute();
        
    $res $stmt->get_result();
        
    $stmt->close();
    }

    while (
    $row $res->fetch_assoc())
    {
        foreach (
    $row as $key => $val) { $$key $val; }
        
        if (
    $parentid == 0// it's a comment
        
    {
            
    $comments .= '        <div class="text">'.$body.'</div>'."\n";
            
            
    // now how to loop through all the results to see if the current 'id'
            // has any replies to it?
        
    }
    }

    echo 
    $comments
    I just can't seem to figure out where to go from here ...

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,555
    Thanks
    80
    Thanked 4,492 Times in 4,456 Posts
    There is no good way to do that, as you have the code.

    The best way to do this is to convert the data from the SQL query to an *ARRAY* in PHP and then you can simply loop through the array as needed.

    I don't use PHP, but I've done this in other languages and it works great.

    The best way would be to create an array of objects, where each object is created from one record in the DB. Second best is to create and array of arrays, with each inner array representing one record.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,555
    Thanks
    80
    Thanked 4,492 Times in 4,456 Posts
    By the by: I still say you might be better off simply dumping all the DB data into a JavaScript array and then using JS to build the HTML content. Note that this *does* require the user to have JS enabled, so if you want the page to run even with JS disabled it's clearly not a solution. But if you need JS enable for other reasons, then why not? More efficient use of the server.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    New Coder
    Join Date
    Jan 2010
    Posts
    68
    Thanks
    3
    Thanked 5 Times in 4 Posts
    I appreciate the input! I would prefer a php solution (just because the rest of structure is already programmed in that way) ie : session cookies, xss security, etc.

    If anyone comfortable with php can show me the way, I'd be more than happy to buy you a case of beer for your troubles (via paypal).

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,555
    Thanks
    80
    Thanked 4,492 Times in 4,456 Posts
    Ugh. So you aren't really a PHP person, either?

    Well, if you don't mind the blind leading the blind...

    Code:
    if($stmt = $mysqli -> prepare("SELECT id, parentid, username, body, dt, active FROM comments WHERE postid = 12 AND active = 1 ORDER BY id desc")) 
    { 
        $stmt->bind_param('i', $postid); 
        $stmt->execute(); 
        $res = $stmt->get_result(); 
        $stmt->close(); 
    } 
    
    $names = array('id', 'parentid', 'username', 'body', 'dt', 'active');
    $rows = array( );
    while ($dbrow = $res->fetch_assoc()) 
    { 
        $values = array( $dbrow["id"],$dbrow["parentid"],$dbrow["username"],
                         $dbrow["body"],$dbrow["dt"],$dbrow["active"] );
        $row = array_combine( $names, $values )
        array_push( $rows, $row );
    }
    // so now all the data is in an array of arrays...
    // the outer array is just numbered; the inner array is an associative array
    
    
    // first, define our recursive function:
    function outputreplies( $forid )
    {
        global $rows;
    
        $first = true;
        for ( $ix = 0; $ix < count($rows); ++$ix )
        {
            $row = $rows[$ix];
            if ( $row["parentid"] == $forid )
            {
                if ( $first ) 
                {
                    echo "<br/>Replies: <ul>\n";
                    $first = false;
                }
                echo "<li>" . $row["body"] . "</li>\n";
                // call ourselves recursively:
                outputlevel( $row["id"] );
            }
        }
        if ( ! $first ) { echo "</ul>\n"; }
    }
    
    // then go find all top level comments:
    for ( $ix = 0; $ix < count($rows); ++$ix )
    {
        $row = $rows[$ix];
        if ( $row["parentid"] == 0 ) /* no parent, so this is top level */
        {
            echo "<div>Comment: " . $row["body"];
            // the output any replies (and replies to replies, ad nauseum)
            outputreplies( $row["id"] );
            echo "</div>\n";
        }
    }
    That *should* create something like:
    Code:
    <div>Comment: [body of comment 1]</div>
    <div>Comment: [body of comment 2]</div>
    <div>Comment: [body of comment 3]<br/>
        Replies:<ul>
            <li>[body of comment 4]</li>
            <li>[body of comment 5]</li>
            <li>[body of comment 6]</li>
        </ul></body>
    <div>Comment: [body of comment 7]</div>
    You can, of course, include other content and modify the HTML tags as you wish.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,555
    Thanks
    80
    Thanked 4,492 Times in 4,456 Posts
    And if your data is something like this:
    Code:
    id | parent id
    
    1      0
    2      0
    3      2
    4      3
    5      3
    6      4
    7      0
    Then the output would something like
    Code:
    <div>Comment: [body of comment 1]</div>
    <div>Comment: [body of comment 2]<br/>
        Replies:<ul>
            <li>[body of comment 3]
                  Replies: <ul>
                      <li>[body of comment 4]
                          Replies: <ul>
                              <li>[body of comment 6]</li>
                              </ul>
                     </li>
                      <li>[body of comment 5]</li>
                 </ul>
            </li>
        </ul>
    </div>
    <div>Comment: [body of comment 7]</div>
    Assuming I have done the PHP correctly. And I don't use PHP.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Mayhem30 (06-27-2013)

  • #7
    New Coder
    Join Date
    Jan 2010
    Posts
    68
    Thanks
    3
    Thanked 5 Times in 4 Posts
    This works perfectly!

    I really appreciate you taking the time out to show me how it's done. I've never had to do anything like this before, so I was really scratching my head on how to tackle it.

    I'd like to buy you a case of beer for your troubles - PM me your paypal email address and I'll take care of it

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,555
    Thanks
    80
    Thanked 4,492 Times in 4,456 Posts
    Sorry, I can't drink beer. I have kidney disease and diabetes. Just buy a round on me for your friends.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,555
    Thanks
    80
    Thanked 4,492 Times in 4,456 Posts
    You know, the sad part about this is that I may have to stop saying I don't use PHP. *sigh*. (And <grin/>. But I really don't. I had to look up array_combine, array_push, and global in the PHP docs.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    New Coder
    Join Date
    Jan 2010
    Posts
    68
    Thanks
    3
    Thanked 5 Times in 4 Posts
    Well for not using PHP, you did a heck of a job

    This part of my project was really bothering me .. but now it's full steam ahead.


  •  

    Tags for this Thread

    Posting Permissions

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