Table of Contents
First steps
Step 1. Create a new template
Step 2. Allow PHP for this template. PHP in Templates
Step 3. Copy and paste ”The Code” that follows inside your template:
Step 4. Add or remove group ids to the following array:
$allow_access = array(1);
By default, only super admins (1) have access to this script. To add more groups separate them with a comma. Example:
$allow_access = array(1,8,9);
The Code
<?php
/*
** This script will export member data to a
** comma or tab delimited text file.
**
** @version 0.1
** @author Bastian Kuberek
**
*/
if (!defined('EXT')){
die('Invalid file request');
}
/*
** Script Access Restriction
** member groups allowed to access this page
** array of group ids
*/
$allow_access = array(1);
/*
**************************************************************************************
*/
global $IN, $DB, $SESS, $LOC;
if (!in_array($SESS->userdata['group_id'], $allow_access))
{
die('You are not authorized');
}
/*
** get custom fields' label
*/
$query = $DB->query("SELECT m_field_id, m_field_label FROM exp_member_fields");
foreach ($query->result as $row)
{
$custom_fields[$row['m_field_id']] = $row['m_field_label'];
}
/*
** get member groups
*/
$query = $DB->query("SELECT group_id, group_title FROM exp_member_groups");
foreach ($query->result as $row)
{
$member_groups[$row['group_id']] = $row['group_title'];
}
/*
** Process Form Data
*/
if ($IN->GBL('export', 'POST'))
{
$delimiter = $IN->GBL('delimiter', 'POST');
$core_member_data = ( ! $IN->GBL('core_member_data', 'POST') ) ? array('member_id','username','screen_name','email') : $IN->GBL('core_member_data', 'POST');
$custom_member_fields = $IN->GBL('custom_member_fields', 'POST');
$filter_group = ( ! $IN->GBL('filter_group', 'POST') ) ? false : $IN->GBL('filter_group', 'POST');
$date_start = $IN->GBL('date_start', 'POST');
$date_end = $IN->GBL('end_start', 'POST');
$date_start = (empty($date_start)) ? false : $LOC->convert_human_date_to_gmt($date_start);
$date_end = (empty($date_end)) ? false : $LOC->convert_human_date_to_gmt($date_end);
switch ($delimiter)
{
case 'comma' : $delimiter = ","; break;
case 'tab' : $delimiter = "\t"; break;
default : $delimiter = "\t"; break;
}
/*
** execute query
*/
$DB->fetch_fields = TRUE;
$sql = "SELECT ";
if (count($core_member_data) > 0)
{
foreach ($core_member_data as $k => $v)
{
$core_member_data[$k] = "a." . $v;
}
$sql .= implode(', ',$core_member_data);
}
if (is_array($custom_member_fields) && count($custom_member_fields) > 0)
{
foreach ($custom_member_fields as $v)
{
$sql .= ", b.m_field_id_" . $v;
}
}
$sql .= " FROM exp_members as a, exp_member_data as b";
$sql .= " WHERE a.member_id = b.member_id";
if (count($filter_group) > 0)
{
$sql .= " AND (";
foreach ($filter_group as $k => $v)
{
$filter_group[$k] = "a.group_id = " . $v;
}
$sql .= implode(' OR ',$filter_group);
$sql .= ")";
}
if ($date_start)
{
$sql .= " AND a.join_date > $date_start";
}
if ($date_end)
{
$sql .= " AND a.join_date < $date_end";
}
$sql .= " ORDER BY a.member_id";
$query = $DB->query($sql);
/*
** create column headers
*/
$field_names = $DB->field_names;
$count = count($field_names);
foreach($field_names as $k => $v)
{
if( preg_match("/(m_field_id_)(\d+)/", $v, $matches) )
{
$field_names[$k] = $custom_fields[$matches[2]];
}
}
$header = '';
for ($i = 0; $i < $count; $i++)
{
$header .= '"' . ucwords( str_replace('_',' ',$field_names[$i]) ) . '"' . $delimiter;
}
$header = rtrim($header, $delimiter);
/*
** create data
*/
foreach ($query->result as $row)
{
$line = '';
foreach ($row as $k => $v)
{
if(empty($v))
{
$v = '""' . $delimiter;
}
else
{
$v = '"' . (($k == 'join_date' || $k == 'last_visit' || $k == 'last_activity') ? $LOC->decode_date('%Y-%m-%d %H:%i', $v) : $v) . '"' . $delimiter;
}
$line .= $v;
}
$line = rtrim($line, $delimiter);
$data .= $line . "\r\n";
}
/*
** output HTTP Headers
*/
header("Content-type: application/x-download");
header("Content-disposition: attachment; filename=\"Export_Member_Data-".date('YmdHi', time()).".txt\"\r\n");
header("Content-transfer-encoding: binary\n");
header("Content-Type: application/csv-tab-delimited-table");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Pragma: public");
/*
** output file contents
*/
echo $header."\r\n".$data;
exit;
} // end form processing
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html >
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title>{site_name} - Export Member Data</title>
<style type="text/css">
td {
text-align: left;
vertical-align: top;
border-collapse: collapse;
border: 1px solid #ccc;
}
td.gray {
background: #f0f0f0;
}
.red {
color: #FF0000;
font-weight: bold;
}
</style>
</head>
<body>
<div id="wrapper">
<div class="emd_form_wrapper">
<form id="emd_form" class="emd_form" action="<?=$_SERVER['PHP_SELF']?>" method="post">
<table width="980" cellspacing="0" cellpadding="5" border="0">
<tr><td colspan="4" class="gray"><h1>Export Member Data</h1></td></tr>
<tr>
<td colspan="2" class="gray">Delimiter:</td>
<td colspan="2" class="white">
<input type="radio" value="tab" name="delimiter" checked="checked" /> tab
<input type="radio" value="comma" name="delimiter" /> comma
</td>
</tr>
<tr>
<td colspan="2" class="gray">Filter by Member Group:</td>
<td colspan="2" class="white">
<? foreach ($member_groups as $k => $v): ?>
<input type="checkbox" value="<?=$k?>" name="filter_group[]"<?=($k == 1 || $k == 5) ? ' checked="checked"' : ''?> /> <?=$v?>
<? endforeach; ?>
</td>
</tr>
<tr>
<td colspan="2" class="gray">Filter by Join Date: (YYYY-MM-DD HH:MM AM/PM)</td>
<td colspan="2" class="white">
start: <input type="text" value="" name="filter_date_start" />
end: <input type="text" value="" name="filter_date_end" />
</td>
</tr>
<tr><td colspan="4" class="gray"> </td></tr>
<tr>
<td class="gray">Core Member Data:</td>
<td class="white">
<input type="checkbox" name="core_member_data[]" value="member_id" checked="checked" /> Memeber Id <br/>
<input type="checkbox" name="core_member_data[]" value="group_id" checked="checked" /> Group Id <br/>
<input type="checkbox" name="core_member_data[]" value="unique_id" /> Unique Id <br/>
<input type="checkbox" name="core_member_data[]" value="username" checked="checked" /> Username <br/>
<input type="checkbox" name="core_member_data[]" value="screen_name" checked="checked" /> Screen Name <br/>
<input type="checkbox" name="core_member_data[]" value="email" checked="checked" /> Email <br/>
<input type="checkbox" name="core_member_data[]" value="url" /> URL <br/>
<input type="checkbox" name="core_member_data[]" value="location" /> Location <br/>
<input type="checkbox" name="core_member_data[]" value="occupation" /> Ocupation <br/>
<input type="checkbox" name="core_member_data[]" value="bday_d" /> Bday Day <br/>
<input type="checkbox" name="core_member_data[]" value="bday_m" /> Bday Month <br/>
<input type="checkbox" name="core_member_data[]" value="bday_y" /> Bday Year <br/>
<input type="checkbox" name="core_member_data[]" value="aol_im" /> AOL IM <br/>
<input type="checkbox" name="core_member_data[]" value="yahoo_im" /> Yahoo IM <br/>
<input type="checkbox" name="core_member_data[]" value="msn_im" /> MSN IM <br/>
<input type="checkbox" name="core_member_data[]" value="icq" /> ICQ <br/>
<input type="checkbox" name="core_member_data[]" value="join_date" /> Join Date <br/>
<input type="checkbox" name="core_member_data[]" value="last_visit" /> Last Visit <br/>
<input type="checkbox" name="core_member_data[]" value="last_activity" /> Last Activity <br/>
<input type="checkbox" name="core_member_data[]" value="total_entries" /> Total Entries <br/>
<input type="checkbox" name="core_member_data[]" value="total_comments" /> Total Comments <br/>
<input type="checkbox" name="core_member_data[]" value="total_forum_topics" /> Total Forum Topics <br/>
<input type="checkbox" name="core_member_data[]" value="total_forum_posts" /> Total Forum Posts <br/>
<input type="checkbox" name="core_member_data[]" value="language" /> Language <br/>
<input type="checkbox" name="core_member_data[]" value="timezone" /> Timezone
</td>
<td class="gray">Custom Member Profile Fields:</td>
<td class="white">
<? foreach ($custom_fields as $k => $v): ?>
<br /> <input type="checkbox" name="custom_member_fields[]" value="<?=$k?>" checked="checked" /> <?=$v?>
<? endforeach; ?>
</td>
</tr>
<tr><td colspan="4" class="gray"> </td></tr>
<tr>
<td colspan="4" class="gray">
<input type="submit" name="export" value="Export Data" />
<input type="reset" name="reset" value="Reset" />
</td>
</tr>
</table>
</form>
</div>
</div>
</body>
</html>
Notes:
Note 1. Make sure that $allow_access only contains the ids of admins.
Note 2. Visit the page on a browser.
http://domain.com/export_member_data/
Note 3. Use the interface to filter and select data to be exported.
Note 4. Filter dates expect either a perfect string or nothing. To enter a date used this notation: YYYY-MM-DD hh:mm AM/PM. Example: 2008-08-29 17:19 PM
BUG Filter by date is not working. Will fix soon.
Result:
All selected data will be exported to a text-file. Each field is seperated with a comma (,) or tab (\t) and each text is surrounded with double quotes (""). Each row is placed on a new line. Save the text-file to your desktop and open it with Excel or probably any other Spreadsheet-tool. Follow the instructions with the information as provided.
Category:Exporting Category:Members