Export User Data to Microsoft Excel

I can remember several users in the past asking if it was possible to export user data from Pligg into Excel. It was an interesting idea, and it seemed especially useful for people who needed a simple way to export user email addresses to use for email campaigns. I recently discovered a PHP script that demonstrates how to export an array of data into a Microsoft Excel document. Using that script as a base I was able to come up with a very simple PHP script for Pligg that will export user data that is being stored in the MySQL database into a Microsoft Excel document. An example of such a document can be seen in the screenshot below.
excel

The script sits in the root of your Pligg CMS site and first checks if the person accessing the script is logged into a “god” level account. If they aren’t it will redirect them to the Pligg login page. If a user is logged in as a “god” level user it will then connect to Pligg’s MySQL database. It then sends header information to your browser letting it know that it’s receiving a Microsoft Excel document and it should open the data as such. Lastly the script generates the data for Excel by grabbing information stored in the pligg_users table. The end result gives you an easy to read Excel data sheet with usernames, email addresses, registration dates and other useful information. The script is very easy to modify so if you don’t like the default data being used it’s not difficult to add your own columns.I’ve pasted the script below for anyone to use on their own website. Just copy these lines into a new file and rename it to whatever you want, just remember to add a .php extension to the end of the file name and store it in the root directory where Pligg CMS resides. You can also get a copy of the code from this pastebin post.
<?php
include_once 'libs/dbconnect.php';
include_once('Smarty.class.php');
$main_smarty = new Smarty;
include_once('config.php');
include(mnminclude.'html1.php');
include(mnminclude.'smartyvariables.php');

force_authentication();
$amIgod = 0;
$amIgod = $amIgod + checklevel('god');
$main_smarty->assign('amIgod', $amIgod);

if($amIgod == 0){
	header("Location: " . getmyurl('login', $_SERVER['REQUEST_URI']));
	die();
}
$main_smarty->assign('isAdmin', $canIhaveAccess);

mysql_connect(EZSQL_DB_HOST,EZSQL_DB_USER,EZSQL_DB_PASSWORD);
mysql_select_db(EZSQL_DB_NAME);

header("Content-Type: application/vnd.ms-excel");
$filename = "Website_User_Data_" . date('d-m-Y') . ".xls";
header("Content-Disposition: attachment;filename="$filename"");

$result = mysql_query("SELECT * FROM ".table_prefix."users ORDER BY user_id") or die('MySQL query attempting to select user data failed!');
while(false !== ($row = mysql_fetch_assoc($result))) {
	echo $row[user_id]."t";
	echo $row[user_names]."t";
	echo $row[user_login]."t";
	echo $row[user_email]."t";
	echo $row[user_url] . "t";
	echo $row[user_date] . "t";
	echo $row[user_lastlogin]." n";
	//echo implode("t", array_values($row)) . "n";
}
exit;
?>

3 thoughts on “Export User Data to Microsoft Excel

Comments are closed.