opmaak van gegevens uit de database
Ik heb een reeks gegevens uit de database gehaald en die wil ik nu in een tabel op het scherm toveren.
ik gebruik de volgende query:
SELECT personen.id, personen.naam, uren.id, uren.id_personeel, uren.datum, uren.begin, uren.eind, uren.totaal FROM personeel AS personen, uren AS uren
WHERE personen.id=uren.id_personeel
GROUP BY uren.datum, personen.naam
werkt prima!
Nu wil ik graag een tabel, waarbij de personeelsnamen bovenaan (x-as) staan en de datum links (van nu tot vroeger, y-as)en daartussen dus: uren.begin, uren.eind, uren.totaal
Weet iemand hoe ik dit doe?
Vast bedankt voor de hulp.
*
* show a recordset as [normalized] crosstab
*
* @param string $SQL
* @param mixed $rowhead
* string or array of row field names; these will be the leftmost columns in the result
* @param string $colhead
* one field in the set to be used for column names
* @param mixed $values
* string or array of values to be put inside a 'cell'
* @return array
*/
function renderAsCrosstab($SQL, $rowhead, $colhead, $values) {
$SEP_ROWHEAD = '&^';
$SEP_VALUES = '&*';
if (!is_array($rowhead)) $rowhead = array($rowhead);
if (!is_array($values)) $values = array($values);
# collect crosstab data
$set = array();
$res = db_query($SQL);
$table = array();
$cols = array();
while ($res && $row = db_fetch_array($res)) {
# compose row header:
# collect row field values
$key = array();
foreach ($rowhead as $label) $key[] = $row[$label];
# collapse to single string
$key = implode($SEP_ROWHEAD, $key);
# if this will be the first entry, make a recipient array
if (!isset($table[$key]) || !$table[$key]) {
$table[$key] = array();
}
$idx = $row[$colhead];
# collect column headers in order to produce orderly column output (maybe even sorted!)
$cols[$idx] = $idx;
# collect values
$val = array();
foreach ($values as $label) {
$val[] = $row[$label];
}
$table[$key][$idx] = implode($SEP_VALUES, $val);
}
# render crosstab
# would like to prepare filler for multiple cell values
# but the csv output does not support that
$res = '';
$res .= out('block');
# the header line
# for every value one column per colhead column
$res .= out('line');
foreach ($rowhead as $label) {
$res .= out('cel', $label);
}
foreach ($cols as $collabel) {
foreach ($values as $vallabel) {
$res .= out('cel', $collabel.' '.$vallabel);
}
}
$res .= out('/line');
# the table body
foreach ($table as $key => $struc) {
$res .= out('line');
$key = explode($SEP_ROWHEAD, $key);
foreach ($key as $label) {
$res .= out('cel', $label);
}
foreach ($cols as $idx) {
if (isset($struc[$idx])) {
$val = explode($SEP_VALUES, $struc[$idx]);
foreach ($val as $label) {
$res .= out('cel', $label);
}
} else {
$val = array();
}
if (count($val) < count($values)) {
for ($i=count($val); $i<count($values); $i++) {
$res .= out('cel', '');
}
}
}
$res .= out('/line');
}
$res .= out('/block');
return $res;
}
/**
* helper for renderAsCrosstab
* either writes output, or adds it up, setting headers for csv download
*
* @param string $typ
* /?(block|line|cel)
* @param string $par
* empty cell will contain entity
* @return string
*/
function out($typ, $par='') {
static $line;
$res = '';
if (isset($_GET['csv']) && $_GET['csv']) {
switch ($typ) {
case 'block':
header("Content-type:text/csv");
header("Content-disposition: attachment; filename=\"result.csv\"");
break;
case '/block':
break;
case 'line':
$line = array();
break;
case '/line':
$res = implode(';',$line)."\r\n";
break;
case 'cel':
# iets doen als er een " in $par zit? Wat is daarover de afspraak in CSV?
// if (!$par) $par = ' '; // niet nodig
$line[] = '"'.$par.'"';
break;
}
} else {
switch ($typ) {
case 'block':
$res = '<table border="1">';
break;
case '/block':
$res = '</table>';
break;
case 'line':
$res = '<tr>';
break;
case '/line':
$res = '</tr>';
break;
case 'cel':
if (!$par) $par = ' ';
$res = '<td>'.$par.'</td>';
break;
}
$res .= "\r\n"; // toch beetje source formatten
}
return $res;
}
