Hello, in effort to give back to the community I humble present my first practical snippet. Please feel free to take it and run with it. I have working for my needs right now. But I sure it will help others.
I am using modx to manage data from another mysql database. I created this snippet to export the data. I have a page using a simple snippet that creates a list of links...
$output .= "<tr onclick=\"document.location.href='[~10~]?f=" . $formRow['reportform_id'] ."';\">";
That document has a blank template and [!ExportCSV!] as the content.
<?php
// usage:
// [!ExportCSV!]
// [[ExportCSV? &f='7']]
$mydb= new DBAPI(); $mydb->connect($host='www.mysite.com',$dbase='reports', $uid='modxdbuser',$pwd='abc123');
$tempForm = $_GET["f"];
if ($tempForm != "") {
$loadForm = $tempForm;
} else {
/* sets a default form ID if no querystring specified */
$loadForm = 1;
};
$entryForm = $loadForm;
/* build sql for query, you can ignore the following code */
$fieldListSql = "SELECT LB_reports.*,LB_fields.field_label
FROM LB_reports
LEFT JOIN LB_fields ON LB_reports.report_field = LB_fields.field_id
WHERE LB_reports.report_id = $entryForm";
$rs = $mydb->query($fieldListSql);
while ($row = mysql_fetch_assoc($rs)) {
$form_id = $row['report_form'];
$aliasCol .= "v" . $row['report_field'] . ".value_value AS '" . $row['field_label'] . "',";
$leftJoins .= "LEFT JOIN LB_values v" . $row['report_field'] . " ON LB_entries.entry_id = v" . $row['report_field'] . ".value_entry AND v" . $row['report_field'] . ".value_field = " . $row['report_field'] ." ";
}
$aliasCol = substr($aliasCol, 0, -1);
/* I have overly normalized db setup, you can use a simpler query here and ignore the above code */
$sql = "SELECT LB_entries.*,LB_values.value_entry," .
$aliasCol
. " FROM LB_entries LEFT JOIN LB_values ON LB_entries.entry_id = LB_values.value_entry " .
$leftJoins
. " WHERE LB_entries.entry_form = " . $form_id . " GROUP BY entry_id";
$rs = $mydb->query($sql);
$count = mysql_num_fields($rs);
for ($i = 0; $i < $count; $i++) {
$header .= mysql_field_name($rs, $i)."\t";
}
while($row = mysql_fetch_row($rs)) {
$line = '';
foreach($row as $keys => $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$data = str_replace("\r", "", $data);
if ($data == "") {
$output = "\n(0) Records Found!\n";
} else {
header('Content-type: application/octet-stream');
header('Content-Disposition: attachment; filename=spreadsheet.xls');
header('Pragma: no-cache');
header('Expires: 0');
$output .= "$header\n$data";
}
mysql_close();
return $output;
?>
Please do not hesitate to point out flaws or enhancements.
Thank you, modx development team, for creating such a wonderful tool!
