Member Export Tool in ExpressionEngine

Posted on in Tutorials, ExpressionEngine

Sometimes a client asks for some extra functionality that is outside of what you originally agreed. Rather than refuse the request, the alternative would be to turn it into something worthwhile you could charge for. The Member Export Tool was such a request.

Note this article was posted 11 years ago so techniques may not work anymore.

Introduction

A couple of years back a client asked me for some functionality in ExpressionEngine that would enable them to export their member details into a CSV for use internally. I had already built them some extra tools into templates that gave them a simple control panel with mostly read only information. I wanted to include the tool here but also wanted to add a bit of extra functionality to allow them choose a range of members to download.

Now I’m not saying that this is the best way to achieve this and neither am I dismissing any add-ons that deliver the same functionality, but as an exercise in thinking laterally in ExpressionEngine I found it worthwhile doing. I have since used it on other member based websites.

Preparation

Download the Export plugin from brandnewbox. Add it to your third-party folder. Download daterangepicker.jQuery.js, date.js and ui.daterangepicker.css and dump them wherever your site assets live.

Templates

Create a new member group called member-export In the newly created member-export group create templates called exporter, today, date-range and specific-date

Index Template

Add the following to your index template or your embed header if you are using one. Be sure to update the assets paths to your own

<link rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/base/jquery-ui.css" type="text/css" />
	<link href="/assets/css/ui.daterangepicker.css" type="text/css" rel="stylesheet" />
	
	<script src="//ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
	<script>window.jQuery || document.write('<script src="js/vendor/jquery-1.7.2.min.js"><\/script>')</script>
	
	<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.21/jquery-ui.min.js"></script>
	
	<script src="/assets/js/date.js" type="text/javascript"></script>
	<script src="/assets/js/daterangepicker.jQuery.js" type="text/javascript"></script>
	
	<script type="text/javascript">
		//<![CDATA[
		$(function() {
			$('input#range').daterangepicker({
				text: 'My Range',
				dateStart: '2010-01-01',
				dateEnd: 'Today',
				dateFormat: 'yy-mm-dd',
				rangeSplitter: '/',
				onClose: function() {
					var actionDate = "/member-export/exporter/" + $("input#range").val();
					$("#export-tool").attr("action", actionDate);
				}
			});
		});
		//]]>
	</script>


In the body of your index template add the following. Notice I have added a simple conditional to keep the tool away from prying eyes

{if logged_out}
		<h1>Bugger off!</h1>
		<p>You must be a site administrator to view this page.</p>
	{if:else}
		<h1>Member Data Custom Export Tool</h1>
		<form action="" id="export-tool">
			<input type="text" value="Choose dates" id="range" /> <input class="submit" type="submit" value="Download" />
		</form>
	{/if}

Exporter Template

Add the following to the exporter template, again adding a conditional to keep it safe. This template does the heavy lifting of deciding what template to embed.

{if segment_3 == "" AND segment_4 == ""}
		<h1>Bugger off!</h1>
		<p>You must be a site administrator to view this page.</p>
	{/if}
	<!-- Check for todays date and show correct embed -->
	{if segment_3 == "{current_time format='%Y-%m-%d'}" AND segment_4 == ""}
		{embed="member-export/today" date1="{current_time format='Y-%m-%d'}"}
	{/if}
	<!-- Check for a specific date and show correct embed -->
	{if segment_3 != "{current_time format='Y-%m-%d'}" AND segment_4 == "" AND segment_3 != ""}
		{embed="member-export/specific-date" date1="{segment_3}"}
	{/if}
	<!-- Check for a date range and show correct embed -->
	{if segment_3 != "" && segment_4 != ""}
		{embed="member-export/date-range" date1="{segment_3}" date2="{segment_4}"}
	{/if}

Today Template

{exp:ajw_export sql="SELECT
		m.member_id AS 'Website Member Id',
		date_format(from_unixtime(join_date), '%d/%m/%Y') AS 'Join Date',
		m.username AS 'Username',
		m.email AS 'Email',
		m.url AS 'URL'
		FROM exp_members AS m LEFT JOIN exp_member_data AS d ON d.member_id = m.member_id
		WHERE date_format(from_unixtime(join_date), '%d-%m-%Y')='{embed:date1}'
		ORDER BY m.member_id DESC"
		format="csv" filename="member-export.csv"
	}

Specific Date Template

{exp:ajw_export sql="SELECT
		m.member_id AS 'Website Member Id',
		date_format(from_unixtime(join_date), '%d/%m/%Y') AS 'Join Date',
		m.username AS 'Username',
		m.email AS 'Email',
		m.url AS 'URL'
		FROM exp_members AS m LEFT JOIN exp_member_data AS d ON d.member_id = m.member_id
		WHERE date_format(from_unixtime(join_date), '%d-%m-%Y')='{embed:date1}'
		ORDER BY m.member_id DESC"
		format="csv" filename="member-export.csv"
	}

Date Range Template

{exp:ajw_export sql="SELECT
		m.member_id AS 'Website Member Id',
		date_format(from_unixtime(join_date), '%d/%m/%Y') AS 'Join Date',
		m.username AS 'Username',
		m.email AS 'Email',
		m.url AS 'URL'
		FROM exp_members AS m LEFT JOIN exp_member_data AS d ON d.member_id = m.member_id
		WHERE date_format(from_unixtime(join_date), '%Y-%m-%d') BETWEEN '{embed:date1}' AND
	'{embed:date2}'
		ORDER BY m.member_id DESC"
		format="csv" filename="member-export.csv"
	}

Each of the date templates contain queries to pull member info from the database. They can easily be extended to include more member information. Let me know how you get on.