I’m trying to query a table that has around 200 columns and 100 or so rows. I’d like to return a list of those fields that are >0, but I also need an associated list of the column names;
e.g.
id | col1 | col2 | col3
1 | 0.00 | 0.50 | 1.07
2 | 1.02 | 0.00 | 0.00
3 | 0.32 | 0.01 | 0.00
I need to return the following:
for id 1: col2, col3
0.50, 1.07
for id 2: col1
1.02
for id 3: col1, col2
0.32, 0.01
I can query by id no problem, and i can return the field names, but I’m not sure how to query any/all fields without specifying a column name and then output that list with the associated field name.
Any help would be hugely appreciated.
My current code returns all the values to a chunk tpl - but I’m using >200 placeholders...
<?php
$tpl = $modx->getOption('tpl',$scriptProperties,'');
$table = $modx->getOption('table',$scriptProperties,'');
$id = $modx->getOption('id',$scriptProperties,'');
$path = MODX_CORE_PATH . 'components/library/';
$result = $modx->addPackage('library',$path . 'model/','library_');
$query = $modx->newQuery($table);
if (!empty($id)) {
$query->where(array('id' => $id,));
}
$items = $modx->getCollection($table,$query);
$fields = $modx->getFieldMeta($table);
foreach ($fields as $keyname => $value){
$output .= ''.$keyname.',';
}
//$output .= '<p>Total: '. count($items) . '</p>';
foreach ($items as $item) {
$itemArray = $item->toArray();
$output .= $modx->getChunk($tpl,$itemArray);
}
return $output;
☆ A M B ☆