/**
1) Make sure we have our Modx Object.
2) Table names - adjust for Evo.
3) SQL with the fields we need - adjust for Evo.
4) Get a file handle.
5) Set the header so it outputs as csv.
6) Loop the results.
7) On the first iteration, set the csv headers from the DB field names.
8) Create csv row.
9) Output to browser.
*/
// 1)
if($modx) {
// 2)
$table1 = $modx->getFullTableName("web_user_attributes");
$table2 = $modx->getFullTableName("web_users");
$table3 = $modx->getFullTableName("foxycart_pins");
$table4 = $modx->getFullTableName("foxycart_subscriptions");
// 3)
$sql = "SELECT
`ua`.`fullname`,
`ua`.`email`,
`ua`.`logincount`,
`ua`.`failedlogincount`,
`ub`.`pin`,
`uc`.`start_date`,
`uc`.`exp_date`,
`uc`.`frequency`,
`uc`.`status`,
DATE_FORMAT(FROM_UNIXTIME(`ua`.`thislogin`),'%W, %M %e, %Y @ %h:%i %p') as `last_login`
FROM $table1 `ua`
JOIN $table3 `ub`, $table4 `uc`, $table2 `u`
ON (`ua`.`id` = `ub`.`id` = `uc`.`id` = `u`.`id`)
ORDER BY `ua`.`fullname` ASC";
// You could check/refine the query in PhpMyAdmin...
// die($sql);
$result = $modx->db->query($sql);
if($modx->db->getRecordCount($result) >= 1) {
// 4)
$stdout = fopen('php://output', 'w');
// 5)
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename="export_userlogins_' . date("d-m-Y") . '.csv"');
$c = 0;
// 6)
while($row = $modx->db->getRow($result)) {
// 7)
if($c == 0) { fputcsv($stdout, array_keys($row)); }
// 8)
fputcsv($stdout, $row);
$c++;
}
} else {
return "None found";
}
// 9)
fclose($stdout);
exit;
} else {
return "Missing Modx Object";
}
« Execution of a query to the database failed - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON (`ua`.`id` = `ub`.`id` = `uc`.`id` = `u`.`id`) ORDER BY `ua`.`fu' at line 14 »
SQL: SELECT `ua`.`fullname`, `ua`.`email`, `ua`.`logincount`, `ua`.`failedlogincount`, `ub`.`pin`, `uc`.`start_date`, `uc`.`exp_date`, `uc`.`frequency`, `uc`.`status`, DATE_FORMAT(FROM_UNIXTIME(`ua`.`thislogin`),'%W, %M %e, %Y @ %h:%i %p') as `last_login` FROM `secretse_mod01`.`modx_web_user_attributes` `ua` JOIN `secretse_mod01`.`modx_foxycart_pins` `ub`, `secretse_mod01`.`modx_foxycart_subscriptions` `uc`, `secretse_mod01`.`modx_web_users` `u` ON (`ua`.`id` = `ub`.`id` = `uc`.`id` = `u`.`id`) ORDER BY `ua`.`fullname` ASC 
SELECT
`ua`.`fullname`,
`ua`.`email`,
`ua`.`logincount`,
`ua`.`failedlogincount`,
`ub`.`pin`,
`uc`.`start_date`,
`uc`.`exp_date`,
`uc`.`frequency`,
`uc`.`status`,
DATE_FORMAT(FROM_UNIXTIME(`ua`.`thislogin`),'%W, %M %e, %Y @ %h:%i %p') as `last_login`
FROM $table1 `ua`
JOIN $table2 `u`
ON (`u`.`id` = `ua`.`id`)
LEFT JOIN $table3 `ub`
ON (`ub`.`user_id` = `u`.`id`)
LEFT JOIN $table4 `uc`,
ON (`uc`.`user_id` = `u`.`id`)
ORDER BY `ua`.`fullname` ASC
/**
1) Make sure we have our Modx Object.
2) Table names - adjust for Evo.
3) SQL with the fields we need - adjust for Evo.
4) Get a file handle.
5) Set the header so it outputs as csv.
6) Loop the results.
7) On the first iteration, set the csv headers from the DB field names.
8) Create csv row.
9) Output to browser.
*/
// 1)
if($modx) {
// 2)
$table1 = $modx->getFullTableName("web_user_attributes");
$table2 = $modx->getFullTableName("web_users");
$table3 = $modx->getFullTableName("foxycart_pins");
$table4 = $modx->getFullTableName("foxycart_subscriptions");
// 3)
$sql = "SELECT
`ua`.`fullname`,
`ua`.`email`,
`ua`.`logincount`,
`ua`.`failedlogincount`,
`ub`.`pin`,
`uc`.`frequency`,
`uc`.`status`,
DATE_FORMAT(FROM_UNIXTIME(`uc`.`start_date`),'%m-%d-%Y') as `start_date`,
DATE_FORMAT(FROM_UNIXTIME(`uc`.`exp_date`),'%m-%d-%Y') as `exp_date`,
DATE_FORMAT(FROM_UNIXTIME(`ua`.`thislogin`),'%m-%d-%Y @ %h:%i %p') as `last_login`
FROM $table1 `ua`
JOIN $table2 `u`
ON (`u`.`id` = `ua`.`id`)
LEFT JOIN ($table3 `ub`)
ON (`u`.`id` = `ub`.`id`)
LEFT JOIN ($table4 `uc`)
ON (`uc`.`user_id` = `u`.`id`)
ORDER BY `uc`.`status` ASC";
// You could check/refine the query in PhpMyAdmin...
// die($sql);
$result = $modx->db->query($sql);
if($modx->db->getRecordCount($result) >= 1) {
// 4)
$stdout = fopen('php://output', 'w');
// 5)
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename="export_userlogins_' . date("d-m-Y") . '.csv"');
$c = 0;
// 6)
while($row = $modx->db->getRow($result)) {
// 7)
if($c == 0) { fputcsv($stdout, array_keys($row)); }
// 8)
fputcsv($stdout, $row);
$c++;
}
} else {
return "None found";
}
// 9)
fclose($stdout);
exit;
} else {
return "Missing Modx Object";
}