Help needed in analysing a table struct and coding

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
psmshankar
Forum Commoner
Posts: 96
Joined: Tue Aug 06, 2002 4:25 am
Location: India

Help needed in analysing a table struct and coding

Post 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 replies to F.
    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..
:cry:

Thanks in advance...
Shankar
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

psmshankar
Forum Commoner
Posts: 96
Joined: Tue Aug 06, 2002 4:25 am
Location: India

Post 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...
psmshankar
Forum Commoner
Posts: 96
Joined: Tue Aug 06, 2002 4:25 am
Location: India

Post 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 :(
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

psmshankar
Forum Commoner
Posts: 96
Joined: Tue Aug 06, 2002 4:25 am
Location: India

Post 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 </td>
    <td>Test Post By Tutor </td>
    <td>1001022663 </td>
    <td>0 </td>
    <td>1 </td>
    <td>2002-11-03 03:19:27 </td>
    <td>A </td>
    <td>1 </td>
  </tr>
  <tr>
    <td>2 </td>
    <td>Post By Student A </td>
    <td>A </td>
    <td>1 </td>
    <td>1 </td>
    <td>2002-11-03 03:42:53 </td>
    <td>A </td>
    <td>1 </td>
  </tr>
  <tr>
    <td>3 </td>
    <td>Post By Student B to A </td>
    <td>B </td>
    <td>2 </td>
    <td>1 </td>
    <td>2002-11-03 03:48:11 </td>
    <td>A </td>
    <td>1 </td>
  </tr>
  <tr>
    <td>4 </td>
    <td>Post By Student C to A </td>
    <td>C </td>
    <td>2 </td>
    <td>1 </td>
    <td>2002-11-03 03:51:31 </td>
    <td>A </td>
    <td>1 </td>
  </tr>
  <tr>
    <td>5 </td>
    <td>Post By Student D to Tutor </td>
    <td>D </td>
    <td>1 </td>
    <td>1 </td>
    <td>2002-11-03 04:05:32 </td>
    <td>A </td>
    <td>1 </td>
  </tr>
  <tr>
    <td>6 </td>
    <td>Post By Student E to Tutor </td>
    <td>E </td>
    <td>1 </td>
    <td>1 </td>
    <td>2002-11-03 04:34:53 </td>
    <td>A </td>
    <td>1 </td>
  </tr>
  <tr>
    <td>7 </td>
    <td>Post By Student F to A </td>
    <td>F </td>
    <td>2 </td>
    <td>1 </td>
    <td>2002-11-03 05:11:26 </td>
    <td>A </td>
    <td>1 </td>
  </tr>
  <tr>
    <td>8 </td>
    <td>Post By Student G to B </td>
    <td>G </td>
    <td>3 </td>
    <td>1 </td>
    <td>2002-11-06 11:58:47 </td>
    <td>A </td>
    <td>1 </td>
  </tr>
  <tr>
    <td>9 </td>
    <td>Post By Student H to C </td>
    <td>H </td>
    <td>4 </td>
    <td>1 </td>
    <td>2002-11-06 04:16:48 </td>
    <td>A </td>
    <td>1 </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..

?>
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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 :D
  • topic 1
    • topic 1,1
    • topic 1,2
      • topic 1,2,1
  • topic 2
    • topic 2,1
  • topic 3
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 8O
Should test this with mozilla ...in time ;)
psmshankar
Forum Commoner
Posts: 96
Joined: Tue Aug 06, 2002 4:25 am
Location: India

Post 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???
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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 '&lt;div class="post"&gt;', $sValue;
$arr_display&#1111;$incCounter] = $iChildPostID;
$incCounter++;
DisplayChild($iChildParentID,$iChildPostID); 
echo '&lt;/div&gt;';
and then e.g. use a stlyesheet that contains

Code: Select all

div.post &#123; margin-left: 5px; &#125;
or

Code: Select all

div.post div	&#123;	padding-left: 5px;
							border-left-style: dashed;
							border-left-color: black;
							border-left-width: thin;
						&#125;
or what ever you want ;)
psmshankar
Forum Commoner
Posts: 96
Joined: Tue Aug 06, 2002 4:25 am
Location: India

Post 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???
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

define tree structure ;)
a collaps-/expandable treeview?
psmshankar
Forum Commoner
Posts: 96
Joined: Tue Aug 06, 2002 4:25 am
Location: India

Post by psmshankar »

how 2 go for tree view display???
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

php classes announced on September, 30
A new class was added to "PHP Classes" repository.
Name: NavTree
Author: Mark Quah
Description:
Generate an explorer-like tree structure. A combination of PHP and
javascript.
URL: http://www.phpclasses.org/browse.html/package/837.html
psmshankar
Forum Commoner
Posts: 96
Joined: Tue Aug 06, 2002 4:25 am
Location: India

Post 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 :)
Post Reply