Display Hierarchical Data using PHP

[erdem]

Hi everyone, I hope I can explain. Please help me for this. any idea or help can safe my life. thanks.

 

I have 3 mysql table (users and categories the others. total is 5). contents, comments, votes.

 

so I pull the content and then comments of the content and then votes for the comments.

 

What I would like to do is answering system for the comments. similar to reddit or youtube comments.

 

my comment table is this

 

CommentsID - ContentsID - UserID - Comment - Time - Parent - WriteIP - Active -TotalVotes - VoteSum

 

Parent come from CommentsID

 

So, this is Hierarchical Data. But i cannot figure out how can I display this.

 

this is my query

 

        $Where = array( "ct.ContentsID = '" . $Contents['ContentsID'] . "'" );
        if ( $_SESSION['UserID'] == 0 )
            $Where[] = "ct.Active = '1'";
        else if ( $_SESSION['Level'] < 3 )
            $Where[] = "(ct.Active = '1' OR ct.UserID = '" . $_SESSION['UserID'] . "')";
        else
        {
            $THEME->loadJS( 'Ajax.js' );
            $THEME->loadJS( 'Moderate.js' );
        }     


        $sql = mysql_query( "SELECT ct.*, ut.*, vt.UserID UserVote FROM comments ct
        INNER JOIN users ut ON ut.UserID = ct.UserID
        LEFT JOIN voters vt ON ( vt.CommentsID = ct.CommentsID AND vt.UserID = '" . $_SESSION['UserID'] . "' )
        LEFT JOIN comments cp ON ( ct.Parent = cp.CommentsID )
        WHERE " . implode( ' AND ', $Where ) . " ORDER BY Time DESC" );

 

and this is how I display everything

 

if ( !$sql ) echo mysql_error();
        if ( mysql_num_rows( $sql ) )
        {
            $Contents = array();
            $Count = 0;
            $LoadVoteJS = false;

            while ( $ROW = mysql_fetch_array( $sql ) )
            {
                $Status = '';
                $WriteTime = $ROW['Time'] > 0 ? date( 'd.m.Y H:i:s', $ROW['Time'] ) : '';
                $Content = preg_replace( '/tag:&#8220;([^&]+)&#822[01];/ie', "'<a href=\"/tag/'.tagPointer('\\1').'.html\">\\1</a>'", $ROW['Comment'] );

                $Vote = "";

                if ( $ROW['Active'] == '0' )
                    $Status = $_SESSION['Level'] >= 3 ? '<a onClick="MsgOnayla(\'' . $ROW['CommentID'] . '\',this);" style="cursor:pointer">Onayla!</a> <a onClick="MsgSil(\'' . $ROW['CommentID'] . '\',this);" style="cursor:pointer">Sil!</a>' : 'Onay Bekliyor!';
             
                
                else if ( $_SESSION['Level'] >= 1 )
                {
                    if ( is_null( $ROW['UserVote'] ) AND $ROW['UserID'] != $_SESSION['UserID'] )
                    {
                        $LoadVoteJS = true;
                        $Status = '<span id="voteContent-' . $ROW['CommentID'] . '">[ <a onClick="MsgOyla(\'' . $ROW['CommentID'] . '\',\'1\');" style="cursor:pointer">?yi!</a> | <a onClick="MsgOyla(\'' . $ROW['ContentID'] . '\',\'0\');" style="cursor:pointer">Kötü!</a> ]</span> ';
                    }
                    else if ( $ROW['TotalVotes'] > 0 )
                        $Vote = "%" . intval( 100 * ( $ROW['VoteSum'] / $ROW['TotalVotes'] ) );
                }
                 
                if ($ROW['Active'] == '1')
                    $Oparation = $_SESSION['Level'] >= 3 ? '<a onClick="MsgSil(\'' . $ROW['CommentID'] . '\',this);" style="cursor:pointer"><img src="/Themes/Standart/Image/delete_content.gif"></a>' : '';
                if ($_SESSION['UserID'] != $ROW['UserID'])
                    $Message = $_SESSION['Level'] >= 1 ? '<a href="/Message/?senduser='.$ROW['UserID'].'">Mesaj</a>' : '';
                    
                    $Name = '<a href="/User/profile.php?id='.$ROW['UserID'].'">'.$ROW['Username'].'</a>';
                    
                $Contents[] = t_ContentBox( $Content, $Name, $WriteTime, $Status,  $Vote, $Message, $Oparation  );
                if ( $Count++ == 0 AND $_SESSION['Level'] < 3 )
                    $Contents[] = t_ContentBanner();
            }
            if ( $LoadVoteJS )
            {
                $THEME->loadJS( 'Ajax.js' );
                $THEME->loadJS( 'Vote.js' );
            }
            
            
            $_PAGE .= t_commentinfo(mysql_num_rows($sql));  
            
            $_PAGE .= t_Contents( implode( t_ContentSeperator(), $Contents ), $CommentForm, $Title );
        }

 

[xyph]

MySQL was never designed to store hierarchical data, so it can be quite tricky. You should check out this (great) article

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

[scootstah]

This one too: http://www.sitepoint.com/hierarchical-data-database/

[erdem]

al of these shows me mysql codding

 

I'm asking that how do I display with php?

 

for example

 

-Comment 1

  -Replay 1 for Comment 1

  -Replay 2 for Comment 1

        -Replay 1 for Replay 2

-Comment 2

 

and so on.....

[xyph]

The examples show you how it would be done in SQL so the PHP part is much, much easier.

 

Can you show up the kind of data your SQL query is returning?

 

<?php

echo "<pre>";
while( $row = mysql_fetch_assoc($sql) ) {
print_r($row);
}
echo "</pre>";

?>

 

You haven't given us enough information to help you in a quick manner.

[erdem]

what do you mean enough information?

 

I gave you my entire codes:))

 

I gave you the database structure. what else would you like to know?

[Barand]

Here's a simple example using one of my test tables (category)

+-------------+----------------------+--------+

| category_id | name                | parent |

+-------------+----------------------+--------+

|          1 | ELECTRONICS          |      0 |

|          2 | TELEVISIONS          |      1 |

|          3 | TUBE                |      2 |

|          4 | LCD                  |      2 |

|          5 | PLASMA              |      2 |

|          6 | PORTABLE ELECTRONICS |      1 |

|          7 | MP3 PLAYERS          |      6 |

|          8 | FLASH                |      7 |

|          9 | CD PLAYERS          |      6 |

|          10 | 2 WAY RADIOS        |      6 |

+-------------+----------------------+--------+

 

// call the recursive function
displayHierarchy(1);

// function to print a category then its child categories 
function displayHierarchy($parent, $indent=0)
{
    $sql = "SELECT category_id, name
            FROM category
            WHERE parent=$parent";
    $res = mysql_query($sql);
    $ind = $indent * 50;
    while (list($id, $name) = mysql_fetch_row($res))  {
        // print category
        echo "<div style='width:300px; margin-top:5px; margin-left: {$ind}px; padding:5px; border:1px solid gray;'>$name</div>" ;
        // print its children
        displayHierarchy($id, $indent+1);
    }
    
}

 

Output sample attached

[Barand]

Alternative method avoiding recursive queries

 

$sql = "SELECT category_id, name, parent
        FROM category";
$res = mysql_query($sql);
while (list($id, $name, $parent) = mysql_fetch_row($res)) {
    $data[$parent][] = array('id'=>$id, 'name'=>$name);
}
    

// call the recursive function
displayHierarchy($data, 0);

// function to print a category then its child categories 
function displayHierarchy(&$arr, $parent, $indent=0)
{
    $ind = $indent * 50;
    if (isset($arr[$parent]))
    foreach($arr[$parent] as $rec)
    {
        echo "<div style='width:300px; margin-top:5px; margin-left: {$ind}px; padding:5px; border:1px solid gray;'>
        {$rec['name']}
        </div>" ;
        displayHierarchy($arr, $rec['id'], $indent+1);
    }
    
}

[xyph]

what do you mean enough information?

 

I gave you my entire codes:))

 

I gave you the database structure. what else would you like to know?

 

I provided you the exact code you need to run to give me what I wanted. It would save time on my end, knowing what possible values, how deep you've nested comments, etc.

 

I also provided you with an alternate SQL solution, which would make both the query and the PHP vastly easier