I'm actually using dBase flat files. This works well with PHP but the lack of indexing functions is the problem. I'll take a look at that Berkley stuff. I guess MySQL is the answer but I dont have the time to sort this out yet.

I've done some tests using arrays. It works fine. Speed is only a problem with some of the bigger databases - I was getting PHP exceed a 30 second limit. The answer to this is to limit the size of the database the users can access.

The ram caching is good and the server utilization is ticking over nicely. The acid test is when dozens of the users are running the reports.

Anyway, this is how I have implemented it. I'll show a quick overview of the code and then snippets of the actual code itself. It looks kinda over the top but thats the only way I know how to implement this.

re: creating summary reports for a flat file database where users can filter the report for dozens of options.

1. loop through database and identify matches with user form

2. run check_vars function for each summary report. Fill array with results

3. perform string formatting on each array

4. Display each array.

------

#loop through the database and find matches

for($i=1; $i <= $count; $i++) {

$rec = dbase_get_record($fid, $i);

if($rec[0] >= $month_min && $rec[0] <= $month_max &&

.

.

. 15 other conditions to be checked here.

.

(isset($spposa) ¦¦ preg_match("/$rec[21]/", "$s_sppos"))) {

#This calls the check_vars func for each summary report. the y93 is the name of the array, the $rec[22] the record in DB to match.

#There are about 30 summaries in total, each summary will have 3-10 entries, therefore approx 180 arrays will be created.

check_vars("1993", "y93", "$rec[22]");

check_vars("1994", "y94", "$rec[22]");

check_vars("1995", "y95", "$rec[22]");

check_vars("1996", "y96", "$rec[22]");

check_vars("1997", "y97", "$rec[22]");

check_vars("1998", "y98", "$rec[22]");

check_vars("1999", "y99", "$rec[22]");

check_vars("2000", "y00", "$rec[22]");

check_vars("2001", "y01", "$rec[22]");

check_vars("2002", "y02", "$rec[22]");

check_vars("A", "ssnA", "$rec[23]");

check_vars("B", "ssnB", "$rec[23]");

check_vars("C", "ssnC", "$rec[23]");

check_vars("D", "ssnD", "$rec[23]");

check_vars("A", "lrA", "$rec[29]");

check_vars("B", "lrB", "$rec[29]");

check_vars("C", "lrC", "$rec[29]");

check_vars("D", "lrD", "$rec[29]");

check_vars("E", "lrE", "$rec[29]");

check_vars("F", "lrF", "$rec[29]");

}

}

#html code here

#next some string formating of each array is performed

calc_vars("y93");

calc_vars("y94");

calc_vars("y95");

calc_vars("y96");

calc_vars("y97");

calc_vars("y98");

calc_vars("y99");

calc_vars("y00");

calc_vars("y01");

calc_vars("y02");

calc_vars("ssnA");

calc_vars("ssnB");

calc_vars("ssnC");

calc_vars("ssnD");

calc_vars("lrA");

calc_vars("lrB");

calc_vars("lrC");

calc_vars("lrD");

calc_vars("lrE");

calc_vars("lrF");

#more html here - the report main headings.

#each array is then displayed

display_vars("1993", "y93");

display_vars("1994", "y94");

display_vars("1995", "y95");

display_vars("1996", "y96");

display_vars("1997", "y97");

display_vars("1998", "y98");

display_vars("1999", "y99");

display_vars("2000", "y00");

display_vars("2001", "y01");

display_vars("2002", "y02");

display_vars("Winter", "ssnA");

display_vars("Spring", "ssnB");

display_vars("Summer", "ssnC");

display_vars("Autumn", "ssnD");

display_vars("1 to 7 days", "lrA");

display_vars("8 to 14 days", "lrB");

display_vars("15 to 28 days", "lrC");

display_vars("29 to 56 days", "lrD");

display_vars("57+ days", "lrE");

display_vars("First Time Out", "lrF");

#end html stuff here

#ok, this is the meat. Need to use a log of ${"var"} stuff.

function check_vars($value, $array, $field) {

global ${"$array"}, $rec;

if($field == $value) {

${"$array"}[0]++;

if($rec[10] == 1) {

${"$array"}[1]++;

${"$array"}[2] = ${"$array"}[2] + $rec[11];

}

}

}

function calc_vars($array) {

global ${"roi$array"}, ${"pro$array"}, ${"wpc$array"}, ${"iv$array"}, ${"rc$array"}, ${"$array"};

global $t_wins, $t_runs;

if(${"$array"}[0] > 0) {

${"pro$array"} = ${"$array"}[2] + ${"$array"}[1] - ${"$array"}[0];

${"roi$array"} = 100 * ${"pro$array"} / ${"$array"}[0];

${"iv$array"} = (${"$array"}[1] / $t_wins) / (${"$array"}[0] / $t_runs);

${"wpc$array"} = sprintf("%8.1f", 100 * ${"$array"}[1] / ${"$array"}[0]) . '%';

}

${"roi$array"} = sprintf("%8.1f", ${"roi$array"}) . '%';

${"pro$array"} = '£' . trim(sprintf("%8.2f", ${"pro$array"}));

${"iv$array"} = sprintf("%8.2f", ${"iv$array"});

}

function display_vars($value, $array) {

global ${"roi$array"}, ${"pro$array"}, ${"wpc$array"}, ${"iv$array"}, ${"rc$array"};

global ${"$array"};

if(${"$array"}[0] > 0) {

print '<TR><TD> </TD><TD CLASS="' . ${"rc$array"} . '">' . $value . '</TD><TD CLASS="' . "r${"rc$array"}" . '">' . ${"$array"}[1] . '</TD><TD CLASS="' . "r${"rc$array"}" . '">' . ${"$array"}[0] . '</TD><TD CLASS="' . "r${"rc$array"}" . '">' . trim(${"wpc$array"}) . '</TD><TD CLASS="' . "r${"rc$array"}" . '">' . trim(${"iv$array"}) . '</TD><TD CLASS="' . "r${"rc$array"}" . '">' . trim(${"pro$array"}) . '</TD><TD CLASS="' . "r${"rc$array"}" . '">' . trim(${"roi$array"}) . '</TD></TR>';

}

}

#end

Looks a bit long winded but it works. What concerns me is that about 180 arrays are going to be created. The speed is not a problem yet. But the memory could be.