Eliminate Query Loop

[lth2h]

I have a script which queries a database and tells me how many cases were handled each month as well as what % of cases were of a certain type.

The problem is if I want a report for an entire year, I end up making 24 queries to the database. Because we have millions of entiries and about 10k per month (with 1-2k per month for the special type) each query takes 3-4 seconds. This is after adding some indexes to the database.

Here's the code section which takes the longest time to run

for ($i=0; $i<=$_REQUEST["duration"]; $i++) {

  $SQL = "SELECT COUNT(CASE_ID) as IDCount FROM CASES WHERE CASE_DATE2 >= TO_DATE('" . date("Y-m-d H:i:s", $from) . "','YYYY-MM-DD HH24:MI:SS') AND CASE_DATE2 < TO_DATE('" . date("Y-m-d H:i:s", $to) . "','YYYY-MM-DD HH24:MI:SS') AND CASE_ID LIKE '____________WEB'";

  $SQL2 = "SELECT COUNT(CASE_ID) as IDCount FROM CASES WHERE CASE_DATE2 >= TO_DATE('" . date("Y-m-d H:i:s", $from) . "','YYYY-MM-DD HH24:MI:SS') AND CASE_DATE2 < TO_DATE('" . date("Y-m-d H:i:s", $to) . "','YYYY-MM-DD HH24:MI:SS')";


  $stmt = ociparse($connection, $SQL);
  ociexecute($stmt, OCI_DEFAULT); # use OCI_COMMIT_ON_SUCCESS to commit
 OCIFetchInto($stmt, $row, OCI_ASSOC+OCI_RETURN_NULLS);

  $stmt2 = ociparse($connection, $SQL2);
  ociexecute($stmt2, OCI_DEFAULT); # use OCI_COMMIT_ON_SUCCESS to commit
  OCIFetchInto($stmt2, $row2, OCI_ASSOC+OCI_RETURN_NULLS);

 $stats[date("My", $from)] = $row["IDCOUNT"];
 $dates[$i] = strtoupper(date("My", $from));
 $totals[date("My", $from)] = $row2["IDCOUNT"];
 $percents[date("My", $from)] = round(100 * $row["IDCOUNT"]/$row2["IDCOUNT"], 2);

  if ($row["IDCOUNT"] > $max) {
 $max = $row["IDCOUNT"];
  }

  if ($row2["IDCOUNT"] > $maxT) {
 $maxT = $row2["IDCOUNT"];
  }

  if (100 * $row["IDCOUNT"]/$row2["IDCOUNT"] > $maxP) {

 $maxP = 100 * $row["IDCOUNT"]/$row2["IDCOUNT"];

  }

}

I then go on to build charts from the data now stored in $stats, $dates, $totals, $percents, $max, $maxT, $maxP.

Is there some faster way to populate these var and maybe only do one or two queries on the database?

The database is Oracle 10g. PHP is 4.x on an Apache 2.x server.