Page 1 of 1
Help needed in analysing a table struct and coding
Posted: Tue Nov 05, 2002 12:15 am
by psmshankar
He all,
I am building a small discussion board in my application. I have a table called 'Message' wherein i am storing the discussions...
Well, my case is :
a post can hav many replies and for each reply there can be a comment or reply.
Lets say
- A starts the thread.
B replies to A.
- C comment on B.
D also comment on B.
E replies to A.
F replies to A.
G may also replies to A
so its like tree structure.... so i need to store these data and display accordingly like tree structure(may be not tree structure but indented correctly for good view)
My structure of 'Message' table is:
PostID tinyint(11) unsigned auto_increment
Message varchar(255)
PostedBy varchar(15)
ParentID tinyint(11) unsigned
BranchTutorID tinyint(11) unsigned
DatePosted datetime
Status char(1)
Unit tinyint(3) unsigned
currently what i am storing is..consdering the above example...
the first record in message table will be (i am writing only few column values which is imp..considering all users are from same unit)
PostID PostedBy ParentID Unit
First Record 1 A 0 1
Second Record 2 B 1 1
Third Record 3 C 2 1
Fourth Record 4 D 2 1
in the above records, ParentID '0' means its new thread...ParentID=1 means this post is a reply/comment for the PostID 1 and so on...
i want to display the whole discussion messages for a particular unit...
i am struck in that..
currently i am using ParentID to denote for which post the reply is...
I am using PHP 4.1/MySQL 3.23
I don't know whether the table design is correct or not...
Could you please suggest me how to accomplish this...
Please help me..
Thanks in advance...
Shankar
Posted: Tue Nov 05, 2002 8:06 pm
by volka
Posted: Tue Nov 05, 2002 8:11 pm
by psmshankar
volka,
i hav seen some scripts in that site...
but i want to have my own way....some i downloaded and looked at script...it seems to be too confusing for me...and unable to step thru...
as far as my table struct..its ok...
now i need to display it using PHP according to parent and child relation with correct indentation....
oops..here is what i am struck with...
Posted: Tue Nov 05, 2002 8:15 pm
by psmshankar
moreoever mine is a part of my application....so i have some criteria to show discussion board....its like duscussion bettwen tutor and student for each unit of subject....
so help me in displaying the parent and childs correctly thru PHP

Posted: Wed Nov 06, 2002 7:51 am
by volka
Posted: Wed Nov 06, 2002 9:14 am
by psmshankar
Hi Volka
the two articels in that 2 sites are really good...since previously i woked in MS SQL Server...so i send those 2 links to my previous office colleagues who are currently using that....just sharing of knowldge ....thanks for this info man....but anyway i could not implement it in MySQL...thats diff case..
ok...anyway volka, i have written PHP script to display it....but the thing is i cannot able to indent the posts....
following is the HTML version of my data in Message table(copied as HTML and pasted here ..using MySQLFront)
Code: Select all
<?php
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>localhost: test.message</title>
<meta name="GENERATOR" content="MySQL-Front 2.2">
<style type="text/css"><!--
.header {background-color: #C0C0C0;}
th,td {vertical-align: top; background-color: #FFFFFF;}
//--></style>
</head>
<body>
<h3>localhost: test.message (9 Records)</h3>
<table border=1>
<tr class="header">
<th>PostID</th>
<th>Message</th>
<th>PostedBy</th>
<th>ParentID</th>
<th>BranchTutorID</th>
<th>DatePosted</th>
<th>Status</th>
<th>Unit</th>
</tr>
<tr>
<td>1&nbsp;</td>
<td>Test Post By Tutor&nbsp;</td>
<td>1001022663&nbsp;</td>
<td>0&nbsp;</td>
<td>1&nbsp;</td>
<td>2002-11-03 03:19:27&nbsp;</td>
<td>A&nbsp;</td>
<td>1&nbsp;</td>
</tr>
<tr>
<td>2&nbsp;</td>
<td>Post By Student A&nbsp;</td>
<td>A&nbsp;</td>
<td>1&nbsp;</td>
<td>1&nbsp;</td>
<td>2002-11-03 03:42:53&nbsp;</td>
<td>A&nbsp;</td>
<td>1&nbsp;</td>
</tr>
<tr>
<td>3&nbsp;</td>
<td>Post By Student B to A&nbsp;</td>
<td>B&nbsp;</td>
<td>2&nbsp;</td>
<td>1&nbsp;</td>
<td>2002-11-03 03:48:11&nbsp;</td>
<td>A&nbsp;</td>
<td>1&nbsp;</td>
</tr>
<tr>
<td>4&nbsp;</td>
<td>Post By Student C to A&nbsp;</td>
<td>C&nbsp;</td>
<td>2&nbsp;</td>
<td>1&nbsp;</td>
<td>2002-11-03 03:51:31&nbsp;</td>
<td>A&nbsp;</td>
<td>1&nbsp;</td>
</tr>
<tr>
<td>5&nbsp;</td>
<td>Post By Student D to Tutor&nbsp;</td>
<td>D&nbsp;</td>
<td>1&nbsp;</td>
<td>1&nbsp;</td>
<td>2002-11-03 04:05:32&nbsp;</td>
<td>A&nbsp;</td>
<td>1&nbsp;</td>
</tr>
<tr>
<td>6&nbsp;</td>
<td>Post By Student E to Tutor&nbsp;</td>
<td>E&nbsp;</td>
<td>1&nbsp;</td>
<td>1&nbsp;</td>
<td>2002-11-03 04:34:53&nbsp;</td>
<td>A&nbsp;</td>
<td>1&nbsp;</td>
</tr>
<tr>
<td>7&nbsp;</td>
<td>Post By Student F to A&nbsp;</td>
<td>F&nbsp;</td>
<td>2&nbsp;</td>
<td>1&nbsp;</td>
<td>2002-11-03 05:11:26&nbsp;</td>
<td>A&nbsp;</td>
<td>1&nbsp;</td>
</tr>
<tr>
<td>8&nbsp;</td>
<td>Post By Student G to B&nbsp;</td>
<td>G&nbsp;</td>
<td>3&nbsp;</td>
<td>1&nbsp;</td>
<td>2002-11-06 11:58:47&nbsp;</td>
<td>A&nbsp;</td>
<td>1&nbsp;</td>
</tr>
<tr>
<td>9&nbsp;</td>
<td>Post By Student H to C&nbsp;</td>
<td>H&nbsp;</td>
<td>4&nbsp;</td>
<td>1&nbsp;</td>
<td>2002-11-06 04:16:48&nbsp;</td>
<td>A&nbsp;</td>
<td>1&nbsp;</td>
</tr>
</table>
<br><br>
<i>generated 06/11/2002 11:09:57 PM by <a href="http://www.mysqlfront.de/">MySQL-Front 2.2</a></i>
</body></html>
?>
here is my entire code for this
Code: Select all
<?php
<?
require('connection.php');
$CurPostID=0;$CurParentID=0;
$PrevPostID=0;$PrevParentID=0;
$TopPos = 3;
$incCounter = 0;
$sSQL = "SELECT * FROM Message WHERE Unit=1 AND BranchTutorID=1 ";
$sSQL .= " ORDER BY PostID,ParentID,DatePosted ";
$ParentResult = mysql_query($sSQL);
$No_Of_Parent_Record = mysql_num_rows($ParentResult);
if($No_Of_Parent_Record >0)
{
$arr_display = array($No_Of_Parent_Record);
while($rsParentResult = mysql_fetch_array($ParentResult))
{
$iPostID = $rsParentResultї"PostID"];
$CurPostID = $iPostID;
$sMessage = $rsParentResultї"Message"];
$sPostedBy = $rsParentResultї"PostedBy"];
$iParentID = $rsParentResultї"ParentID"];
$CurParentID = $iParentID;
$iBranchTutorID = $rsParentResultї"BranchTutorID"];
$sDatePosted = $rsParentResultї"DatePosted"];
$sStatus = $rsParentResultї"Status"];
$iUnit = $rsParentResultї"Unit"];
if($CurParentID==0)
{
$sValue = "";
$sValue = "<table border=0><tr><td>Posted By: $sPostedBy</td></tr>";
$sValue .= "<tr><td>Date: $sDatePosted</td></tr> <tr><td>$sMessage</td></tr></table><hr>";
echo $sValue;
$arr_displayї$incCounter] = $CurPostID;
$incCounter++;
}
else
{
if(in_array($CurPostID,$arr_display))
{
$incCounter = $incCounter;
}
else
{
$sValue = "";
$sValue = "<table border=0><tr><td>Posted By: $sPostedBy</td></tr>";
$sValue .= "<tr><td>Date: $sDatePosted</td></tr> <tr><td>$sMessage</td></tr></table><hr>";
echo $sValue;
$arr_displayї$incCounter] = $CurPostID;
$incCounter++;
}
DisplayChild($CurParentID,$CurPostID);
}
}
}
function DisplayChild($iParentID,$iPostID)
{
Global $TopPos;
Global $incCounter;
Global $arr_display;
$sSQL = "";
$sSQL = "SELECT * FROM Message WHERE Unit=1 AND BranchTutorID=1 AND ParentID=$iPostID ";
$sSQL .= " ORDER BY PostID,ParentID,DatePosted";
$ChildResult = mysql_query($sSQL);
$No_Of_Child_Record = mysql_num_rows($ChildResult);
if ($No_Of_Child_Record >0)
{
while($rsChildResult = mysql_fetch_array($ChildResult))
{
$iChildPostID = $rsChildResultї"PostID"];
$sChildMessage = $rsChildResultї"Message"];
$sChildPostedBy = $rsChildResultї"PostedBy"];
$iChildParentID = $rsChildResultї"ParentID"];
$iChildBranchTutorID = $rsChildResultї"BranchTutorID"];
$sChildDatePosted = $rsChildResultї"DatePosted"];
$sChildStatus = $rsChildResultї"Status"];
$iChildUnit = $rsChildResultї"Unit"];
if(in_array($iChildPostID,$arr_display))
{
$incCounter = $incCounter;
}
else
{
$TopPos = $TopPos + 25;
$sValue = "";
//$sValue = "<div style='position:absolute;left:10%;top:$TopPos%'><table border=0><tr><td>Posted By: $sChildPostedBy</td></tr>";
$sValue = "<table border=0><tr><td>Posted By: $sChildPostedBy</td></tr>";
$sValue .= "<tr><td>Date: $sChildDatePosted</td></tr> <tr><td>$sChildMessage</td></tr></table><hr>";
echo $sValue;
$arr_displayї$incCounter] = $iChildPostID;
$incCounter++;
DisplayChild($iChildParentID,$iChildPostID);
}
}
//DisplayChild($iChildParentID,$iChildPostID);
}
}
?>
i used recursive funstion to get the childs....
this will display the posts one by one with parent-child relation but no indentation...
getting the result correctly but donno how to indent the posts(display)based on parent-child relation..
?>
Posted: Wed Nov 06, 2002 9:53 am
by volka
you may use lists as formatting elements, e.g.
Code: Select all
<html><body>
<ul>
<li>topic 1</li>
<ul>
<li>topic 1,1</li>
<li>topic 1,2</li>
<ul>
<li>topic 1,2,1</li>
</ul>
</ul>
<li>topic 2</li>
<ul>
<li>topic 2,1</li>
</ul>
<li>topic 3</li>
</ul>
</body></html>
this structure can be easily created with your DisplayChild-function and CSS will make it look pretty
the [ list ]-element of BBCode can do it, too
There is one thing that baffled me
Code: Select all
<li>topic 2</li>
<ul>
<li>topic 2,1</li>
</ul>
with IE's DOM that was the only way to make the <ul>-element a child of the 'previous' <li>-element even though it is not in the html-structure

Should test this with mozilla ...in time

Posted: Wed Nov 06, 2002 10:47 am
by psmshankar
sorry volka..no idea...cannot figure it out clearly..
can u just say what code i need to add there in displaychild function???
Posted: Wed Nov 06, 2002 11:50 am
by volka
I just realized, that you print all child messages completely.
This can be done with list, too, but imho it's easier to nest them in a <div>-element as you've already begun.
try
Code: Select all
echo '<div class="post">', $sValue;
$arr_displayї$incCounter] = $iChildPostID;
$incCounter++;
DisplayChild($iChildParentID,$iChildPostID);
echo '</div>';
and then e.g. use a stlyesheet that contains
Code: Select all
div.post { margin-left: 5px; }
or
Code: Select all
div.post div { padding-left: 5px;
border-left-style: dashed;
border-left-color: black;
border-left-width: thin;
}
or what ever you want

Posted: Fri Nov 08, 2002 8:12 pm
by psmshankar
it worked....thank you... sorry for the delay that i was held up in another module ..simultaneously doing...
i am also trying to work out using a tree structure??
any idea???
Posted: Fri Nov 08, 2002 8:24 pm
by volka
define
tree structure 
a collaps-/expandable treeview?
Posted: Fri Nov 08, 2002 8:58 pm
by psmshankar
how 2 go for tree view display???
Posted: Sat Nov 09, 2002 9:59 am
by volka
php classes announced on September, 30
Posted: Tue Nov 12, 2002 7:17 pm
by psmshankar
i downloaded that ...but still some javascript (.js) files missing...
anyway had meeting with clients,,now they decided not to go for tree view...but listing out replies normally and when click a link it shud show the messaged with parent child relation - with indentation...
making my job easier
