How to make a dynamic table in php?

hadiaruz · #1 (**permalink**) 03-17-09, 11:50 PM

hi,

i have a problem with dynamic table. In my project, i have to display dynamic column and row. for example, when user would like to have report from january to may and from year 2000 till 2008, the system will auto generate the table with those information...

kindly please help as this is very urgent project.
thanx

dgreenhouse · #2 (**permalink**) 03-18-09, 01:04 AM

This is a start on the output, but selecting the values based on your date criteria is more involved.

Also, depending on the number of rows returned from the query, you may want to
implement some type of paging scheme.

Note: There's no error detection an mitigation here. This has to be implemented to have a resilient program!

Code:

<table>
  <thead>
    <th>Col1</th><th>Col2</th><th>Col3</th><th>Colx</th>
  </thead>
  <?php

    $db = @mysql_connect('host','username','password');
    $selected = @mysql_select_db('dbname');

    $query = 'select col1, col2, col3, colx from the_table where some_criteria order by some_order';

    $rs = @mysql_query($query, $db);

    while ($row = @mysql_fetch_assoc($rs)) {
      print '<tr>'
      .'<td>' . $row['col1'] . '</td>'
      .'<td>' . $row['col2'] . '</td>'
      .'<td>' . $row['col3'] . '</td>'
      .'<td>' . $row['colx'] . '</td>'
      .'</tr>';
    }
  ?>
</table>

hadiaruz · #3 (**permalink**) 03-18-09, 02:46 AM

thanx for replying...actually i need to do some statistic from the result displayed.before creating the statistic, i have to collect and display the total of date received for the specific range of month and year. basically this will be the outcome:

year/ 2006 2007 2008 2009 TOTAL
month

JAN 1 0

FEB 3 0

MAC 4 1

TOTAL 8 1

hope this will give u the clear picture...
hope to hear from u soon...

dgreenhouse · #4 (**permalink**) 03-18-09, 02:57 AM

I think I see, but you'll need to provide a sample of how the table(s) is/are structured.

I see: JAN 1 0; FEB 3 0; etc.

What I don't understand is how that's associated with year/2006 etc.

Try to layout an example of what you actually want in the output.

hadiaruz · #5 (**permalink**) 03-18-09, 03:04 AM

This is the sample of table....everything need to generate automatically and dynamically...retrieve data from database

HTML Code:

<table width="100%"  border="1" cellpadding="2">
  <tr>
    <td width="15%">&nbsp;</td>
    <td width="7%">2006</td>
    <td width="6%">2007</td>
    <td width="72%">2008</td>
    <td width="72%">Total</td>
  </tr>
  <tr>
    <td>January</td>
    <td>1</td>
    <td>1</td>
    <td>0</td>
    <td>2</td>
  </tr>
  <tr>
    <td>Feb</td>
    <td>2</td>
    <td>1</td>
    <td>0</td>
    <td>3</td>
  </tr>
  <tr>
    <td>Mac</td>
    <td>3</td>
    <td>1</td>
    <td>0</td>
    <td>4</td>
  </tr>
  <tr>
    <td>April</td>
    <td>4</td>
    <td>1</td>
    <td>0</td>
    <td>5</td>
  </tr>
  <tr>
    <td>Total</td>
    <td>10</td>
    <td>4</td>
    <td>0</td>
    <td>15</td>
  </tr>
</table>

Jcbones · #6 (**permalink**) 03-19-09, 05:24 PM

/Homework???

OK I'll bite...

You can populate your own query.

PHP Code:


			
<?php

    $con = mysql_connect('host','username','password');
    $selected = mysql_select_db('dbname', $con);

    $search_string = 'January';  //populate from a $_POST or $_GET argument;
    $query = "SELECT * FROM `table` WHERE `column`='$search_string'";

    $result = mysql_query($query);
   echo '<table cellpadding="0" cellspacing="0" border="1">';
   echo '<th>Month</th>';
  echo '<th>1</th>';
  echo '<th>2</th>';
  echo '<th>3</th>';
  echo '<th>4</th>';

  $total1 = 0;
  $total2 = 0;
  $total3 = 0;
  $total4 = 0;

    while ($row = mysql_fetch_array($result)) 
    {
      echo '<tr>';
      echo '<td>'. $row[0] . '</td>';
      echo '<td>' . $row[1] . '</td>';
      echo '<td>' . $row[2] . '</td>';
      echo '<td>' . $row[3] . '</td>';
      echo '<td>'.$row[4].'</td>';
      echo '</tr>';

    $total1 = $total1 + $row[1];
    $total2 = $total2 + $row[2];
    $total3 = $total3 + $row[3];
    $total4 = $total4 + $row[4];
    }
   echo '<tr>';
   echo '<td>Totals</td>';
   echo '<td>'.$total1.'</td>';
   echo '<td>'.$total2.'</td>';
   echo '<td>'.$total3.'</td>';
   echo '<td>'.$total4.'</td>';
   echo '</tr>';
 
   echo '</table>'
?>

*note* I used alot of echo's to help you understand what's going on...

hadiaruz · #7 (**permalink**) 03-19-09, 07:34 PM

Thank you for your replied..please refer attached file for the ouput. I would like to have column next to month is year that user selected. for example from 2008 - 2010...

scenario:when user select month from january to december and year from 2008 to 2010, the system will display how many date received on particular month and year..hope it clear.I post some of the coding of my project.

?>

<form method="post" action="test.php">

<table width="100%" border="1" cellpadding="2">
<tr>
<td width="20%" class="t2">Month</td>
<td>From:</td>
<td><? echo $oEntity->show_combo("BULAN",$bulan,"bulan")?></td>
<td>To:</td>
<td><? echo $oEntity->show_combo("BULAN",$bulan2,"bulan2")?></td>
</tr>
<tr>
<td class="t2">Year</td>
<td width="5%">From: </td>
<td width="18%"><? echo $oEntity->show_combo("TAHUN",$tahun,"tahun")?></td>
<td width="3%"> To:</td>
<td width="54%"><? echo $oEntity->show_combo("TAHUN",$tahun2,"tahun2")?></td>

</tr>
<tr>
<td> </td>
<td colspan="4"><input type="submit" name="Submit" value="Submit"></td>
</tr>

</table>

</form>
<?

$bulan=$_POST["bulan"];
$bulan2=$_POST["bulan2"];
$tahun=$_POST["tahun"];
$tahun2=$_POST["tahun2"];
if(trim($bulan != "0" )) {
$query= "SELECT MONTH(dt_receive), COUNT( * ) FROM cab_paper WHERE MONTH(dt_receive) BETWEEN '$bulan' AND '$bulan2' ";
//echo $sql2;
//exit;
}

if(trim($tahun != "0" )) {
$query.=" AND YEAR(dt_receive) BETWEEN '$tahun' AND '$tahun2' GROUP BY MONTH(dt_receive),YEAR(dt_receive) ";
}

// $search_string = 'January'; //populate from a $_POST or $_GET argument;
// $query = "SELECT * FROM `table` WHERE `column`='$search_string'";

$result = mysql_query($query);
echo '<table cellpadding="0" cellspacing="0" border="1">';
echo '<th>Month</th>';
echo '<th>1</th>';
echo '<th>2</th>';
echo '<th>3</th>';
echo '<th>4</th>';

$total1 = 0;
$total2 = 0;
$total3 = 0;
$total4 = 0;

while ($row = mysql_fetch_array($result))
{
echo '<tr>';
echo '<td>'. $row[0] . '</td>';
echo '<td>' . $row[1] . '</td>';
echo '<td>' . $row[2] . '</td>';
echo '<td>' . $row[3] . '</td>';
echo '<td>'.$row[4].'</td>';
echo '</tr>';

$total1 = $total1 + $row[1];
$total2 = $total2 + $row[2];
$total3 = $total3 + $row[3];
$total4 = $total4 + $row[4];
}
echo '<tr>';
echo '<td>Totals</td>';
echo '<td>'.$total1.'</td>';
echo '<td>'.$total2.'</td>';
echo '<td>'.$total3.'</td>';
echo '<td>'.$total4.'</td>';
echo '</tr>';

echo '</table>'
?>

Dommy · #8 (**permalink**) 03-20-09, 02:33 AM

I think your script is not good. Try Object Oriented. It's better and easy.

hadiaruz · #9 (**permalink**) 03-20-09, 03:29 AM

i'm quite new with php and not really familiar with object oriented...if you have an idea,kindly please respond on how to solve it..Thanx..i need this to be done quite urgent..thanx one more time...=)

Jcbones · #10 (**permalink**) 03-21-09, 04:16 PM

OOP is overkill for a small project. If you have a lot of pages, it's a godsend.

as far as:

Quote:

I think your script is not good

Perhaps it isn't, but it is better than the one you posted.

try this:

PHP Code:


			
<form method="post" action="test.php">

<table width="100%" border="1" cellpadding="2">
<tr>
<td width="20%" class="t2">Month</td>
<td>From:</td>
<td><? echo $oEntity->show_combo("BULAN",$bulan,"bulan")?></td>
<td>To:</td>
<td><? echo $oEntity->show_combo("BULAN",$bulan2,"bulan2")?></td>
</tr>
<tr>
<td class="t2">Year</td>
<td width="5%">From: </td>
<td width="18%"><? echo $oEntity->show_combo("TAHUN",$tahun,"tahun")?></td>
<td width="3%"> To:</td>
<td width="54%"><? echo $oEntity->show_combo("TAHUN",$tahun2,"tahun2")?></td>

</tr>
<tr>
<td>&nbsp;</td>
<td colspan="4"><input type="submit" name="Submit" value="Submit"></td>
</tr>

</table>




</form>
<?

$bulan=$_POST["bulan"];
$bulan2=$_POST["bulan2"];
$tahun=$_POST["tahun"];
$tahun2=$_POST["tahun2"];
if(trim($bulan != "0" )) {
$query= "SELECT MONTH(dt_receive), COUNT( * ) FROM cab_paper WHERE MONTH(dt_receive) BETWEEN '$bulan' AND '$bulan2' ";
//echo $sql2;
//exit;
}

if(trim($tahun != "0" )) {
$query.=" AND YEAR(dt_receive) BETWEEN '$tahun' AND '$tahun2' GROUP BY MONTH(dt_receive),YEAR(dt_receive) ";
}




$month = array('January','Feburary','March','April','May','June','July','August','september','October','November','December');

$result = mysql_query($query);

//We will place these results into a table, build table.
echo '<table cellpadding="0" cellspacing="0" border="1">';
//Lets make it nice, and add table headers.
echo '<th>Month</th>';

//We will dynamically create the headers after the month
//Using $i as a control so we don't lose the value of $tahun.
$i = $tahun;
while($i <= $tahun2)
    {
        echo '<th>'.$i.'</th>';
        $i++;
    }

//Pull the data and store it in an array, so we can sort our $query.
while ($row = mysql_fetch_array($result))
{
    $dbMonth = $month[$row[0] - 1];
    $dbYear = $row[1];
    $count = $row[2];
    
            $storedMonth["$dbMonth"]["$dbYear"] = $count;
        
    
}
//cycle through our array to build our table rows.    
        foreach($storedMonth as $key => $value)
            {
                echo '<tr><td width="100">' . $key . '</td>';
                
                //Since we are using a multi-dimensional array, we
                //want to cycle through our years we searched by
                //re-using $i so as not to lose our $tahun data
                //Use the FOR method, so we can populate <td> that
                //doesn't have a key inside the array.
                for ($i = $tahun; $i <= $tahun2; $i++)
                    {
                        echo ($storedMonth[$key][$i] == NULL) ? '<td width="100">0</td>' : '<td width="100">'.$storedMonth[$key][$i].'</td>';
                    }
                echo '</tr>';
            }
    


echo '</table>';


?>