Web Interface for database searching and display

[Ross]

The following core codes are kindly provided by Toby Inkster. As he may not
appear in PHP newsgroup, pls forgive me to post the question here. My data
are formatted like those as in the last few codes of $d, now my problem is
some entry's attribute has more than one information, e.g. figC, figD, figE
instead of fig C only. Therefore, i find sorting by using the following
codes can lead to a problem. Since i have >2000 records, if i open multiple
columns for these categories, there may be more than >100 categories, and it
will span widely in Excel spreadsheet, if I do something like hashing, it is
mentally expensive. I'm thinking about putting them into a single cell, and
then create a database search function so to use the following codes to
display the searched result. Is that a good idea or are there other better
choices? Otherwise, how do I change the database format to support database
searching? Is the pattern matching just like Perl? If Toby Inkster has
interest in academic publication, please notify me and I'll email you.
Thanks a lot!!!


<?php
function insert_datatable_cmp ($a, $b) {
return ($a[$_GET['sort']]<$b[$_GET['sort']]) ? -1 : 1;
}

function insert_datatable ($data, $headings, $options=array(), $caption='')
{

if ($caption!='' && $options['h2']==1) print
"<h2>$caption</h2>\n";
print "<table>\n";
if ($caption!='' && $options['h2']!=1) print
"<caption>$caption</caption>\n";
print "<thead><tr>";
$i = 0;
foreach ($headings as $h) {
if ($options["nosort:$i"]==1) {
print "<th scope=\"col\">$h</th>";
} else {
print "<th scope=\"col\"><a
href=\"${_SERVER['PHP_SELF']}?sort=$i\">$h</a></th>";
}
$i++;
}
print "</tr></thead>\n<tbody>\n";

$lines = explode("\n",$data);
$i = 0;
while ($l = array_shift($lines)) {
$s[$i++] = explode("|",$l);
}
if(isset($_GET['sort'])) {
usort($s,"insert_datatable_cmp");
}
foreach ($s as $S) {
print "<tr>";
for($i=0;$S[$i];$i++)
{
if ($options['email:'.$i]==1)
{
$S[$i] = '<a href="mailto:' . $S[$i]
.. '">' . $S[$i] . '</a>';
} elseif ($options['web:'.$i]==1)
{
$S[$i] = '<a href="' . $S[$i] . '">'
.. $S[$i] . '</a>';
}

if ($options['join:'.$i.':'.($i+1)]==1)
{
print '<td colspan="2">' . $S[$i] .
' ' . $S[$i+1] . '</td>';
$i++;
}
else
{
print '<td>' . $S[$i] . '</td>';
}
}
print "</tr>\n";
}

print "</tbody></table>\n";

}

$d = "A|3|5|1|10|3|figA.gif
B|5|2|2|4|3|figB.gif
C|4|3|3|9|6|figC.gif";

$h = array('name','size','length','0min','3min','10min','chart');
$o = array('web:6'=>1, 'nosort:2'=>1, 'nosort:6'=>1);
$c = 'My Caption';

insert_datatable($d, $h, $o, $c);
?>

 

[Toby Inkster]

Since i have >2000 records, if i open multiple columns for these
categories, there may be more than >100 categories, and it will span
widely in Excel spreadsheet

Ouch! The function I posted is really only meant for up to 200 or so rows
and maybe 10 columns. After that, it's inefficient and would overwhelm the
user with data.

Another part of the same site has to deal with a table of about 65,000
rows and about 15 columns. I think I've come up with a pretty good
solution for searching and sorting, but I don't have time to post it here
right now, as it would need a certain degree of tidying up to be suitable
for public consumption!

Feel free to e-mail me if you would like to discuss further.