how to create a dynamic table

[wizardry]

i'm trying to achive this results layout: 

 

example:

                    catagory

                          comment

                          comment

                          comment

                    catagory

                    catagory

                    catagory

                          comment

 

what is being pulled now is if their is 2 comments two 1 catagory then 2 catagorys are being returned with the same id. what i want is to return one cataogry with many comments.

 

thanks in advance for your help.

 

<?php require_once('Connections/Del_Comments.php'); ?>
    <?php 
    $all_ids = array();
    $str_ids = "";
     
    // first parent query
    $maxRows_sourceType = 10;
    $pageNum_sourceType = 0;
    if (isset($_GET['pageNum_sourceType'])) {
      $pageNum_sourceType = $_GET['pageNum_sourceType'];
    }
    $startRow_sourceType = $pageNum_sourceType * $maxRows_sourceType;
     
    mysql_select_db($database_Del_Comments, $Del_Comments);
    $query_sourceType = "SELECT a.Id, a.Type, 
a.Dates, 
a.UIdFk, 
b.Id as Did, 
b.comment, 
b.dates as Day, 
b.sfk as Sfk ,
c.sfk as sfk1, 
d.Memo as memo
FROM 
asstatusupdate as a   
left join asstatusdata as b 
on a.id = b.sfk 
left join asmanystatusupdate as c
on b.sfk = c.sfk
left join ascomments as d
on d.id = c.cfk
where a.uidfk='1'
order by Dates asc";
    $query_limit_sourceType = sprintf("%s LIMIT %d, %d", $query_sourceType, $startRow_sourceType, $maxRows_sourceType);
    $sourceType = mysql_query($query_limit_sourceType, $Del_Comments) or die(mysql_error());
    $row_sourceType = mysql_fetch_assoc($sourceType);
     
    if (isset($_GET['totalRows_sourceType'])) {
      $totalRows_sourceType = $_GET['totalRows_sourceType'];
    } else {
      $all_sourceType = mysql_query($query_sourceType);
      $totalRows_sourceType = mysql_num_rows($all_sourceType);
    }
    $totalPages_sourceType = ceil($totalRows_sourceType/$maxRows_sourceType)-1;
     
    // add the array
    while ($row_source = mysql_fetch_assoc($sourceType)) {
     
        $all_ids[] = $row_source['Sfk'];
     
        $str_ids .= $row_source['Sfk'].',';
     
     }
     
            // remove the array
         $str_ids = (substr($str_ids,-1) == ',') ? substr($str_ids, 0, -1) : $str_ids;
    
echo $str_ids;
    //echo $all_ids;

//second child query
        $maxRows_sourceComments = 10;
    $pageNum_sourceComments = 0;
    if (isset($_GET['pageNum_sourceComments'])) {
      $pageNum_sourceComments = $_GET['pageNum_sourceComments'];
    }
    $startRow_sourceComments = $pageNum_sourceComments * $maxRows_sourceComments;
     
    mysql_select_db($database_Del_Comments, $Del_Comments);
    $query_sourceComments = "SELECT 
c.sfk as sfk1,
d.Memo as memo
FROM 
asmanystatusupdate as c
left join ascomments as d
on d.id = c.cfk
where c.uidfk0='1' and c.sfk in ($str_ids)";
    $query_limit_sourceComments = sprintf("%s LIMIT %d, %d", $query_sourceComments, $startRow_sourceComments, $maxRows_sourceComments);
    $sourceComments = mysql_query($query_limit_sourceComments, $Del_Comments) or die(mysql_error());
    $row_sourceComments = mysql_fetch_assoc($sourceComments);
     
    if (isset($_GET['totalRows_sourceComments'])) {
      $totalRows_sourceComments = $_GET['totalRows_sourceComments'];
    } else {
      $all_sourceComments = mysql_query($query_sourceComments);
      $totalRows_sourceComments = mysql_num_rows($all_sourceComments);
    }
    $totalPages_sourceComments = ceil($totalRows_sourceComments/$maxRows_sourceComments)-1;
     
     
     
    $resultComments = @mysql_query($query_sourceComments, $Del_Comments);
    $numComments = @mysql_num_rows($resultComments);
     
     
    $result = @mysql_query($query_sourceType, $Del_Comments);
     
    $num = @mysql_num_rows($result);
     
    // column count for parent
        $thumbcols = 1;
     
            // column count for parent query
        $thumbrows = 1+ round($num / $thumbcols);
     
                // column count for child query    $thumbrowsComments = 1+ round($numComments/$thumbcols);
     
                // header
                print '<br />';
                print '<table align="center" width="500" border="3" cellpadding="0" cellspacing="0">';
     
                if (!empty($num)) {
                    print '<tr><td colspan="3" align="center"><strong>Returned Num of Record Sets: ' .$num. ' and comments count' .$numComments. '</strong></td></tr>';    
     
                }
     
        // table layout for parent query        

	function display_table() {
     
    // global variables
    global $num, $result, $thumbrows, $thumbcols, $resultComments, $numComments, $thumbrowsComments ;
     
    // row count for parent
     for ($r=1; $r<=$thumbrows; $r++) {
     
         // print table row
             print '<tr>';
     
    //format the columns
    for ($c=1; $c<=$thumbcols; $c++) {
     
    print '<td align="center" valign="top">';
     
        $row = @mysql_fetch_array($result);
	$row1 = @mysql_fetch_array($resultComments);
     
        $Id = $row['Id'];
        $Type = $row['Type'];
        $Dates = $row['Dates'];
	$Comment = $row['Comment'];
	$Sfk1 = $row['sfk1'];
	$Memo = $row['memo'];


    // test if not empty show record sets            
        if (!empty($Id)) {
     
                                // print output from parent query
     
                       
                                        // grab type dates comment and format for there column
     
                                        echo '<td valign="top" align="center">'; 
                                        echo "$Type"; echo '  '; echo "$Dates"; echo '<br />';echo '<br />';echo '<br />';
                                        echo "$Comment"; echo '</td>';
                                        echo '<td>'; echo "$Id"; echo '</td>';
                                        echo '<td>'; echo "$str_ids"; echo '</td>';

                            				echo '<tr>';
										echo '<td>'; echo "$Sfk1"; echo '</td>';
										echo '<td>'; echo "$Memo"; echo '</td>';
										echo '</tr>';

		} // closing the $id loop
     					


                                                    else {
                                                            print '  ';
                                                        }
                                print '</td>'; //closing the table data
                            } //closing the rows
                                print '</tr>';
                 } // closing the outter loop
                //} //closing fk id loop
                                print '</td>'; //closing the table data
                    } //closing the rows
                                print '</tr>';    
              
        //} // closing the main loop
     
                    // call the main table
                display_table() ;
                    print '</table>';
     
     
    ?>
     
     
     <?php
var_dump(substr('a', 1)); // bool(false)
?>

 

 

[gizmola]

In SQL when you inner join 2 tables you will get a row every time the joined columns match.

 

In your case, order by catagory, comment_date or whatever the appropriate column name is. As you fetch the rows in a loop, use a variable to hold the category_id.  Anytime this changes you should output a new category section and set the variable. 

 

There's not much more to it than that.

[wizardry]

can you show me an example of the loop to use?

[gizmola]

$result = mysql_query....

$category_id = '';

while ($row = mysqlf_fetch_assoc($result)) {
  if ($row['category_id'] != $category_id) {
      //Output the markup for a new category here.
      $category_id = $row['category_id'];
  }
  // Output comment markup here
}

[wizardry]

i can get the id in the loop but what i dont get is the grouping of the catagory can you show me a more advanced example. thanks in advance for your help.

[gizmola]

That is the skeleton of what you need.  I glanced at your code, and the exercise for you now is to take my skeleton and apply it to your specific tables structure and queries.

 

What code you need where I typed the comments depends on what sort of markup you are going to use.  Are these nested lists?  Tables with embedded tables?  You really need to provide more specific information and code to get me to invest more time in an answer.

[wizardry]

thank you i just have a mirc up of the dev code i will post my test code once ive completed. thank you for your help this is the results i was looking for.

[wizardry]

here is the code i'm trying to get a table displaying correctly it has spacers between records. thanks in advance for your help.

 

<?php
$result = mysql_query("SELECT a.id as id, 
a.catagory as cat,
a.memo as memo,
a.dates as day, 
b.fk as fk,
b.memo as memo1 
FROM type as a
left join memo as b
on a.id = b.fk
");

$category_id = '';

while ($row = mysql_fetch_assoc($result)) {
  if ($row['id'] != $category_id) {
      //Output the markup for a new category here.
      $category_id = $row['id'];
  
	$Cat = $row['cat'];
	$Memo = $row['memo'];

	echo '<table align="center" width="500" border="3" cellpadding="0" cellspacing="0">'; 
	echo '<tr>';
	echo '<td> '; echo "$Cat"; echo "</td>";
	echo "</br>"; 
	echo '<td>' ; echo "$Memo"; echo '</td>'; echo "</br>";
  		echo '</tr>';
	// echo '</table>';
	  
  }
  // Output comment markup here
  
  		$Fk = $row['fk'];
  		$Memo1 = $row['memo1'];

	// echo '<table align="center" width="500" border="3" cellpadding="0" cellspacing="0">'; 
	echo '<tr>';
	echo '<td>'; echo "$Fk"; echo '</td>'; 
	echo "</br>"; 
	echo '<td>'; echo "$Memo1"; echo '</td>'; 
	echo "</br>";
	echo '</tr>';
	echo '</table>';


		}
?>

[wizardry]

i got it i had to remove the <br> thanks for your help.

[wizardry]

i have another problem; the image is not displaying its just showing the border. ii know the image path is being caught in the variable. i just dont understand thanks in advance for your help.

 

<?php
$result = mysql_query("SELECT a.Id, a.Type, 
a.Dates, 
a.Uidfk as Uidfk, 
b.Id as Did, 
b.comment as Comment, 
b.dates as Day, 
b.sfk as Sfk ,
c.sfk as sfk1, 
d.Memo as Memo,
aes_decrypt(e.ProfileName, '$Ukey') as Name,
i.Path as Path
FROM 
asstatusupdate as a   
left join asstatusdata as b 
on a.id = b.sfk 
left join asmanystatusupdate as c
on b.sfk = c.sfk
left join ascomments as d
on d.id = c.cfk
right join onlyonew_hquis.ASWebInfo as e
on e.Uidfk = a.uidfk
right join onlyonew_hqups.ASManyAlbums as f
on f.UserId=a.uidfk
right join onlyonew_hqups.ASAlbums as g
on f.AlbumId=g.Id
right join onlyonew_hqups.ASTitle as h
on g.Id=h.AlbumId
right join onlyonew_hqups.ASData as i
on h.Id=i.TitleId
where a.uidfk='1' and i.DefaultProfilePic='Y';

");

$category_id = '';

while ($row = mysql_fetch_assoc($result)) {
  if ($row['Id'] != $category_id) {
      //Output the markup for a new category here.
      $category_id = $row['Id'];
  
	$Cat = $row['Type'];
	$Memo = $row['Comment'];
	$Did = $row['Did'];
	$Dates = $row['Day'];
	$Name = $row['Name'];
	$Path = $row['Path'];
	$FriendId = $row['Uidfk'];

	echo '<table align="center" width="40%" border="3" cellpadding="0" cellspacing="0">'; 
	echo '<tr>';
	echo '<td align="center">'; echo "$Name"; echo "</br>"; echo  '<a href="source/source.php?FriendId=' ."$FriendId". '"><img src="' ."$Path". '" height="120" width="120" align="middle" border="3" /></a>';
	echo '<td> '; echo "$Cat"; echo "</td>";
   // echo "</br>"; 
	echo '<td>' ; echo "$Memo"; echo '</td>'; // echo "</br>";
	echo '<td>'; echo "$Did" ; echo '</td>'; 
	echo '<td>'; echo "$Dates" ; echo '</td>';
  		echo '</tr>';
    echo '</table>';
	  
  }
  // Output comment markup here
  
  		$Fk = $row['sfk1'];
  		$Memo1 = $row['Memo'];

    echo '<table align="center" width="500" border="3" cellpadding="7" cellspacing="0">'; 
	echo '<tr>';
	echo '<td>'; echo "$Fk"; echo '</td>'; 
   // echo "</br>"; 
	echo '<td>'; echo "$Memo1"; echo '</td>'; 
	// echo "</br>";
	echo '</tr>';
	echo '</table>';


		}
?>

[wildteen88]

What is $Path set to? Why don't you just echo it out to see what it is set to. The path it shows should be where your images are located. Do you know where the images should be?

[wizardry]

its my own error it was echo ../directoryname when its not in the sub domains im still developing it.

[wizardry]

My loop for the comments image is not displaying the users image and name it loops and displays the first one ( image for that record set ).

 

thanks in advance for your help.

 

 

<?php
$result = mysql_query("SELECT a.Id, a.Type, 
a.Dates, 
a.Uidfk as Uidfk, 
b.Id as Did, 
b.comment as Comment, 
b.dates as Day, 
b.sfk as Sfk ,
c.sfk as sfk1, 
d.Memo as Memo,
d.Date as Dates1,
aes_decrypt(e.ProfileName, '$Ukey') as Name,
substr(i.Path, 4) as Path
FROM 
asstatusupdate as a   
left join asstatusdata as b 
on a.id = b.sfk 
left join asmanystatusupdate as c
on b.sfk = c.sfk
left join ascomments as d
on d.id = c.cfk
right join onlyonew_hquis.ASWebInfo as e
on e.Uidfk = a.uidfk
right join onlyonew_hqups.ASManyAlbums as f
on f.UserId=a.uidfk
right join onlyonew_hqups.ASAlbums as g
on f.AlbumId=g.Id
right join onlyonew_hqups.ASTitle as h
on g.Id=h.AlbumId
right join onlyonew_hqups.ASData as i
on h.Id=i.TitleId
where a.uidfk in (select friendid from onlyonew_hquis.asfriends where uidfk0='1') and i.DefaultProfilePic='Y' order by dates desc;

");

$category_id = '';

while ($row = mysql_fetch_assoc($result)) {
  if ($row['Id'] != $category_id) {
      //Output the markup for a new category here.
      $category_id = $row['Id'];
  
	$Cat = $row['Type'];
	$Memo = $row['Comment'];
	$Did = $row['Did'];
	$Dates = $row['Day'];
	$Name = $row['Name'];
	$Path = $row['Path'];
	$FriendId = $row['Uidfk'];

	echo '<table align="center" width="40%" border="3" cellpadding="0" cellspacing="0">'; 
	echo '<tr>';
	echo '<td align="center">'; echo "$Name"; echo "</br>"; echo  '<a href="source/source.php?FriendId=' ."$FriendId". '"><img src="' ."$Path". '" height="120" width="120" align="middle" border="3" /></a>'; echo '</td>';
	echo '<td align="center"> '; echo "$Dates" ; echo "</br>"; echo "</br>"; echo "$Cat"; //echo "</td>";
    echo "</br>"; 
	 echo "$Memo"; echo '</td>'; // echo "</br>";
	echo '<td>'; echo "$Did" ; echo '</td>'; 
	echo '</tr>';
    echo '</table>';
	  
  }
  // Output comment markup here
        if(!empty($row['sfk1'])){
  		$Fk = $row['sfk1'];
  		$Memo1 = $row['Memo'];
	$FriendId1 = $row['Uidfk'];
	$Dates1 = $row['Dates'];
	$Path1 = $row['Path'];

    echo '<table align="center" width="500" border="3" cellpadding="7" cellspacing="0">'; 
	echo '<tr>';
	echo '<td align="center">'; echo "$Name"; echo "</br>"; echo  '<a href="source/source.php?FriendId=' ."$FriendId1". '"><img src="' ."$Path1". '" height="120" width="120" align="middle" border="3" /></a>'; echo '</td>';
	echo '<td>'; echo "$Fk"; echo '</td>'; 
   // echo "</br>"; 
	echo '<td align="center">'; echo "$Dates1"; echo "</br>"; echo "</br>"; echo "$Memo1"; echo '</td>'; 
	// echo "</br>";
	echo '</tr>';
	echo '</table>';

	}
		}
?>