Randy,
Suggest pulling the data out of the resultset using a numberically-indexed array. Then
o/p each <TD>...</TD>
inside a loop - you know how many times to loop, because that is set by the number of
rows (plus Total column).
BTW if you use aliases on those SUM() columns - it will make life a lot easier when
you are using associative
arrays to post-process the data.
Regards,
=dn
> 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>
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
