Page 2 of 2

Posted: Wed Aug 08, 2007 2:21 pm
by icesolid
My final code is working great if anyone was interested. I used ideas from everyone, but I think the final result I came up with is a really good solution.

Code I am using:

Code: Select all

<?php
if($_POST["customer"] == "All Customers") {
    $sort = "user_code!='001' AND user_code!='149' AND user_code!='150'";
} else {
    $sort = "customer='" . $_POST["customer"] . "'";
}

$_SESSION["output"] = "";

for($y = "2006"; $y <= date("Y"); $y++) {
    $next_year = $y + "1";
    $result = mysql_query("SELECT count(*) AS ordercount FROM cases WHERE $sort AND YEAR(date_ordered)=$y GROUP BY LEFT(DATE(date_ordered), 4)");
    $row = mysql_fetch_assoc($result);

    $_SESSION["output"].= "
    <tr>
      <td><font face=\"Courier New\" size=\"2\">January $y</font></td>
      <td align=\"center\"><font face=\"Courier New\" size=\"2\">" . number_format(mysql_num_rows(mysql_query("SELECT * FROM cases WHERE $sort AND date_ordered BETWEEN '$y-01' AND '$y-02'"))) . "</font></td>
    </tr>
    <tr>
      <td><font face=\"Courier New\" size=\"2\">February $y</font></td>
      <td align=\"center\"><font face=\"Courier New\" size=\"2\">" . number_format(mysql_num_rows(mysql_query("SELECT * FROM cases WHERE $sort AND date_ordered BETWEEN '$y-02' AND '$y-03'"))) . "</font></td>
    </tr>
    <tr>
      <td><font face=\"Courier New\" size=\"2\">March $y</font></td>
      <td align=\"center\"><font face=\"Courier New\" size=\"2\">" . number_format(mysql_num_rows(mysql_query("SELECT * FROM cases WHERE $sort AND date_ordered BETWEEN '$y-03' AND '$y-04'"))) . "</font></td>
    </tr>
    <tr>
      <td><font face=\"Courier New\" size=\"2\">April $y</font></td>
      <td align=\"center\"><font face=\"Courier New\" size=\"2\">" . number_format(mysql_num_rows(mysql_query("SELECT * FROM cases WHERE $sort AND date_ordered BETWEEN '$y-04' AND '$y-05'"))) . "</font></td>
    </tr>
    <tr>
      <td><font face=\"Courier New\" size=\"2\">May $y</font></td>
      <td align=\"center\"><font face=\"Courier New\" size=\"2\">" . number_format(mysql_num_rows(mysql_query("SELECT * FROM cases WHERE $sort AND date_ordered BETWEEN '$y-05' AND '$y-06'"))) . "</font></td>
    </tr>
    <tr>
      <td><font face=\"Courier New\" size=\"2\">June $y</font></td>
      <td align=\"center\"><font face=\"Courier New\" size=\"2\">" . number_format(mysql_num_rows(mysql_query("SELECT * FROM cases WHERE $sort AND date_ordered BETWEEN '$y-06' AND '$y-07'"))) . "</font></td>
    </tr>
    <tr>
      <td><font face=\"Courier New\" size=\"2\">July $y</font></td>
      <td align=\"center\"><font face=\"Courier New\" size=\"2\">" . number_format(mysql_num_rows(mysql_query("SELECT * FROM cases WHERE $sort AND date_ordered BETWEEN '$y-07' AND '$y-08'"))) . "</font></td>
    </tr>
    <tr>
      <td><font face=\"Courier New\" size=\"2\">August $y</font></td>
      <td align=\"center\"><font face=\"Courier New\" size=\"2\">" . number_format(mysql_num_rows(mysql_query("SELECT * FROM cases WHERE $sort AND date_ordered BETWEEN '$y-08' AND '$y-09'"))) . "</font></td>
    </tr>
    <tr>
      <td><font face=\"Courier New\" size=\"2\">September $y</font></td>
      <td align=\"center\"><font face=\"Courier New\" size=\"2\">" . number_format(mysql_num_rows(mysql_query("SELECT * FROM cases WHERE $sort AND date_ordered BETWEEN '$y-09' AND '$y-10'"))) . "</font></td>
    </tr>
    <tr>
      <td><font face=\"Courier New\" size=\"2\">October $y</font></td>
      <td align=\"center\"><font face=\"Courier New\" size=\"2\">" . number_format(mysql_num_rows(mysql_query("SELECT * FROM cases WHERE $sort AND date_ordered BETWEEN '$y-10' AND '$y-11'"))) . "</font></td>
    </tr>
    <tr>
      <td><font face=\"Courier New\" size=\"2\">November $y</font></td>
      <td align=\"center\"><font face=\"Courier New\" size=\"2\">" . number_format(mysql_num_rows(mysql_query("SELECT * FROM cases WHERE $sort AND date_ordered BETWEEN '$y-11' AND '$y-12'"))) . "</font></td>
    </tr>
   <tr>
      <td><font face=\"Courier New\" size=\"2\">December $y</font></td>
      <td align=\"center\"><font face=\"Courier New\" size=\"2\">" . number_format(mysql_num_rows(mysql_query("SELECT * FROM cases WHERE $sort AND date_ordered BETWEEN '$y-12' AND '$next_year-01'"))) . "</font></td>
   </tr>
    <tr>
      <td colspan=\"2\">&nbsp;</td>
    </tr>
    <tr>
      <td align=\"center\"><font face=\"Courier New\" size=\"2\"><b>TOTAL FOR $y:</b></font></td>
      <td align=\"center\"><font face=\"Courier New\" size=\"2\"><b>" . number_format($row["ordercount"]) . "</b></font></td>
    </tr>
    <tr>
      <td colspan=\"2\"><hr></td>
    </tr>";
}

$result = mysql_query("SELECT count(*) AS ordercount FROM cases WHERE $sort");
$row = mysql_fetch_array($result);

$_SESSION["output"].= "
<tr>
  <td><font face=\"Arial\"><big><b>TOTAL CASES:</b></big></font></td>
  <td align=\"center\"><font face=\"Arial\"><big><b>" . number_format($row["ordercount"]) . "</b></big></font></td>
</tr>";

echo $_SESSION["output"];
?>