I have a form (criteria.php) that includes two drop down lists from which a
user can select a start period and an end period from a MySQL table. This
form posts to a form called order_summary.php on which I am using the
$start_period and $end_period variables from the criteria.php form to pull
data from another MySQL table which will produce a summary of sales for
those periods as illustrated by the following example:
$start_period $end_period $total
-------------------------------------------------
Order Type | APR-00 | MAY-00 | Total |
-------------------------------------------------
Widget 1 | $100 | $200 | $300 |
Widget 2 | $300 | $200 | $500 |
.
.
.
OK. Fine and good. I have this working. My problem is this:
How can I create a table which will dynamically adjust the number of columns
based on the users request. That is, if the user requests APR-00 through
MAY-00, the table will be built as illustrated above. If the user requests
JAN-01 through JUN-00, the table will be built as illustrated below:
$start_period through
$end_period
----------------------------------------------------------------------------
----------------------------------
Order Type | JAN-01 | FEB-01 | MAR-01 | APR-01 |
MAY-01 | JUN-01 |Total |
----------------------------------------------------------------------------
----------------------------------
Widget 1 | $100 | $200 | $300 | $300 | $300 | $300 | $300 |
Widget 2 | $300 | $200 | $500 | $300 | $300 | $300 | $300 |
.
.
.
I have posted order_summary.php below.
Any thoughts?
Thanks,
Randy Rankin
************************************ order_summary.php
**************************************************
<?
session_register($dbname);
?>
<?
require("./includes/salesdb.inc");
$query_first_last_period = "select period_id, period_name
from periods
where period_id between $start_period_id and $end_period_id
GROUP BY period_id
HAVING period_id = $start_period_id OR period_id = $end_period_id
ORDER BY period_id";
$result_first_last_period = mysql_query($query_first_last_period);
$row = mysql_fetch_row($result_first_last_period);
for( $i= 0; $i<count($row);$i++ )
{
if( $i == 0 )
$start_period_name = $row[$i];
elseif( $i == (count($row)-1) )
$end_period_name = $row[$i];
}
echo "<font face=Tahoma size=2 color=#333999>";
echo "Salesperson: <b>$dbname</b><br>";
echo "Report Period: <b>$start_period_name through $end_period_name</b>";
echo "</font><P>";
echo "<table border=0 cellpadding=2 cellspacing=2>";
echo "<tr bgcolor=#333399><font face=Tahoma size=2 color=#ffffff>";
echo "<th align=left bordercolor=#808080><font color=#FFFFFF face=Tahoma
size=2><b>Category</b></font></th>";
// ******************** Start Get the Order Summary Data ***************
$sql_order_summary = "SELECT order_type, SUM(extended_dollars)
from salesdata
where salesperson = '$dbname'
and period_id between '$start_period_id' and '$end_period_id'
group by order_type";
$result_order_summary = mysql_query($sql_order_summary) or die ("I can't get
the sales data!");
// ******************** Stop Get the Order Summary Data ***************
// ******************** Start Get the Period Names for the Table Header
***************
$sql_period_name = "Select period_name
from periods
where period_id between '$start_period_id' and '$end_period_id'
order by period_id";
$result_period_name = mysql_query($sql_period_name) or die ("I can't get the
period name!");
// ******************** End Get the Period Names for the Table Header
***************
// ******************** Build and Print the Table Headers ***************
$num_periods = mysql_numrows($result_period_name);
if ($num_periods == 0)
{
echo "<font color=#ff0000><b>There were no records found for
the periods selected.</b></font>";
}
else
while ($row = mysql_fetch_array($result_period_name))
{
$period_name = $row["period_name"];
echo "<th align=left bordercolor=#808080><font
color=#FFFFFF face=Tahoma size=2>
<b>$period_name</b></th>
</font>";
}
echo "<th align=left bordercolor=#808080><font
color=#FFFFFF face=Tahoma size=2><b>Total Sales</b></font></th>";
echo "</tr>";
// ******************** End Build and Print the Table Headers
***************
// ******************** Start Calculate and Format Total Sales
***************
$get_itemtot = "SELECT SUM(extended_dollars)
FROM salesdata
WHERE salesperson = '$dbname'
and period_id between '$start_period_id' and '$end_period_id' ";
$itemtot_result = mysql_query($get_itemtot) or die ("I can't do the math!");
$itemtot = mysql_result($itemtot_result,0,"SUM(extended_dollars)");
$fmt_Total_Sales = "\$".number_format($itemtot,0);
// ******************** End Calculate and Format Total Sales
***************
mysql_close();
//Display Results: Sales Summary Information:
$num = mysql_numrows($result_order_summary);
$alternate = "2"; // number of alternating rows
if ($num == 0)
{
echo "<font color=ff0000><b>There were no records found for
$dbname.</b></font>";
}
else
while ($row = mysql_fetch_array($result_order_summary))
{
$Raw_Value = $row["SUM(extended_dollars)"];
$Value = "\$".number_format($Raw_Value,0);
$type = $row["order_type"];
if ($alternate == "1")
{
$bcolor = "#EDEDF1";
$alternate = "2";
}
else
{
$bcolor = "#ffffff";
$alternate = "1";
}
echo "<tr>
<td bgcolor = $bcolor align=left><font
face=Tahoma size=2 color=#333999><b>$type
</b></font></td>
<td bgcolor = $bcolor
align=left><font face=Tahoma size=2 color=#333999><b>DATA
</b></font></td>
<td bgcolor = $bcolor
align=left><font face=Tahoma size=2 color=#333999><b>DATA
</b></font></td>
<td bgcolor = $bcolor
align=left><font face=Tahoma size=2 color=#333999><b>DATA
</b></font></td>
<td bgcolor = $bcolor align=right><font
face=Tahoma size=2 color=#333999><b>$Value
</b></font></td>
</tr>";
}
mysql_free_result ($result_order_summary)
?>
</table>
<P>
<a href="criteria.php"><font face="Tahoma" size="2"><img border="0"
src="images/icon_arrows_right.gif" width="15" height="15">Select another
Period</font></a>
