News:

Wondering if this will always be free?  See why free is better.

Main Menu

Download your membership roster to excel

Started by mikeymx5, July 19, 2011, 11:50:21 PM

Previous topic - Next topic

mikeymx5

Please move to Tips and Tricks

I use my forum to hold my clubs membership, so it nice to have a print out of my members.  Im still trying to fix a few things in this code, such as if you filter out a group the custom fields still remain. I will update it when I get it fixed but for now it works as is. If anyone wants to make suggestions Im all ears.

I also should note that my website was hacked and I had no idea until I printed out a roster. I not only found several spam accounts but that several of my members had spam placed in their profiles. So this is useful if not just to check on your website.

SMF 2.0 only - I don't know the structure of 1.0 so it may and may not work.

create a php file with the code below and name it whatever.php and place it in your root directory or sub folder. To access it go to yourdomain/whatever.php (you must be logged in as a admin)

If any one wants to use this code for a mod please ask before hand.

<?php
$ssi_guest_access 
false;

// Include the SSI file.
require_once('../SSI.php');
?>

<?php
$allowed_groups = array(1);  //admin only
$can_see FALSE;
foreach ($allowed_groups as $allowed)
if (in_array($allowed$user_info['groups']))
{
$can_see TRUE;
break;
}

if ($can_see)
{
global $scripturl$modSettings$smcFunc;

//Add your custom fields to the array replacing custom field 1,2,3..
//I got the table fields from my database 
$list_of_fields = array('cust_1''cust_2''cust_3');

//this part is here because I wanted to see when their subscriptions ended.
$list_of_fields2 = array('end_time');
$data = array();

$query $smcFunc['db_query']('''
SELECT id_member, member_name, email_address FROM {db_prefix}members
WHERE is_activated = 1'
);

while (
$row $smcFunc['db_fetch_assoc']($query))
{
$data[$row['id_member']] = $row;
// foreach ($list_of_fields as $field) // set up blanks for the fields we want in case they're empty
//$data[$field] = ''; // removed because it made excess lines will fix later
}

$smcFunc['db_free_result']($query);

$query $smcFunc['db_query']('''
SELECT id_member, variable, value FROM {db_prefix}themes
WHERE variable IN ({array_string:fields}) AND id_theme = 1
'
, array(
'fields' => $list_of_fields,
)
);

while (
$row $smcFunc['db_fetch_assoc']($query))
{
$data[$row['id_member']][$row['variable']] = $row['value'];
}

$smcFunc['db_free_result']($query);


//test for subscriptions
//shows only active subscriptions and converts to a usable time

$query $smcFunc['db_query']('''
SELECT id_member, FROM_UNIXTIME(end_time) AS end_time FROM {db_prefix}log_subscribed
WHERE status = 1
'
, array(
'fields' => $list_of_fields2,
)

);

while (
$row $smcFunc['db_fetch_assoc']($query))
{
$data[$row['id_member']][$row['status']] = $row['end_time'];
}

$smcFunc['db_free_result']($query);



// Original PHP code by Chirp Internet: www.chirp.com.au 
// Please acknowledge use of this code by including this header. 

function cleanData(&$str

$str preg_replace("/\t/""\\t"$str); 
$str preg_replace("/\r?\n/""\\n"$str); 
if(strstr($str'"')) $str '"' str_replace('"''""'$str) . '"'


// file name for download 
$filename "Membership_Roster_" date('Ymd') . ".xls"

header("Content-Disposition: attachment; filename=\"$filename\""); 
header("Content-Type: application/vnd.ms-excel"); 

$flag false
// foreach($data as $row) { 
// Removed only if direct data base search
// $result = mysql_query("SELECT * FROM smf2_members ORDER BY id") or die('Query failed!'); 
// while(false !== ($row = mysql_fetch_assoc($result))) { 

//if(!$flag) { 



//  display field/column names as first row 
// echo implode("\t", array_keys($row)) . "\n"; 
$flag true
//} 
// array_walk($row, 'cleanData'); 
// echo implode("\t", array_values($row)) . "\n"; 

//be sure and add your custom field titles here, I probably could of done this automatically with a few //lines of code but I was lazy

echo'<table border="1">
<tr>
<th>#</th>
<th>Forum Name</th>
<th>Email</th>
<th>Name</th>
<th>cust_1</th>
<th>cust_2</th>
<th>cust_3</th>
<th>Subscription Ends</th>
</tr>'
;
foreach (
$data as $row)
{
echo '
<tr>
<td>'
$row['id_member'], '</td><td>'$row['member_name'], '</td><td>',$row['email_address'],'</td>';

foreach ($list_of_fields as $field)
echo '
<td>'
$row[$field], '</td>';
foreach ($list_of_fields2 as $field)
echo '
<td>'
$row[$end_time], '</td>';
echo '
</tr>'
;
}
echo
'</table>';


}
else
{
//Redirect if they are not allowed.
redirectexit('www.yourdomain.com');
}
?>



DanCarroll

Does this work. Seems to be missing something. I could use something like this so will take a closer look and see what I find.

Thanks. ~ Dan


StanJones

The concept seems pretty convincing, though I'll prefer to get it verified from our developers first. Though please don't misunderstand me, I'm definitely not doubting your efforts.
Cloud Hosting [nofollow] - VMware & HyperV
Affordable web hosting [nofollow] - Offering 24x7 Fully Managed Services Since 2001
SSL Certificate [nofollow]

mikeymx5

Quote from: DanCarroll on November 04, 2011, 12:23:10 AM
Does this work. Seems to be missing something. I could use something like this so will take a closer look and see what I find.

Thanks. ~ Dan

Works great for me.... you must create a php file and access it directly. I spent many days trying to put this in a portal PHP block and soon discovered it just will not work.

Change the cust_1.. 2.. 3 to what ever custom fields you want it to display.

The code is pretty sound but if a moderator wants to check it out for security reasons I wouldn't mind the feed back.

live627

Your script grabs the username, which may be different from their display name. Use real_name instead of member_name for that.

pearpandas

Thanks!  I was just thinking about needing something like this for one of my sites! :D

naijasoil

anyone with help!!!!!!!!!!!!!!!!!!!!!!!. i do not how to place the code in my forum. am a newbie.   anyone with help can send mail to me : [email protected]

Home 4 Answers

Any ideas on how I can achieve the same to a .csv?

Thank you!

Advertisement: