Advertisement:

Author Topic: Download your membership roster to excel  (Read 13241 times)

Offline mikeymx5

  • Semi-Newbie
  • *
  • Posts: 63
Download your membership roster to excel
« on: July 19, 2011, 11:50:21 PM »
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.

Code: [Select]
<?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');
}
?>


Offline DanCarroll

  • Jr. Member
  • **
  • Posts: 125
  • Gender: Male
  • Technologist
Re: Download your membership roster to excel
« Reply #1 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

Offline wilowterra

  • Semi-Newbie
  • *
  • Posts: 44
    • BLURAYTEAM.COM
Re: Download your membership roster to excel
« Reply #2 on: November 04, 2011, 02:30:11 PM »
 :D :D :D :D :DGREAT!!!!!

Offline StanJones

  • Semi-Newbie
  • *
  • Posts: 24
    • Web Hosting UK
Re: Download your membership roster to excel
« Reply #3 on: November 11, 2011, 07:10:30 AM »
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]

Offline mikeymx5

  • Semi-Newbie
  • *
  • Posts: 63
Re: Download your membership roster to excel
« Reply #4 on: November 28, 2011, 05:06:56 PM »
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.

Offline live627

  • Developer
  • SMF Hero
  • *
  • Posts: 5,377
  • Gender: Male
    • live627 on Facebook
    • live627 on GitHub
    • live627 on LinkedIn
    • @live627 on Twitter
    • livemods
Re: Download your membership roster to excel
« Reply #5 on: November 28, 2011, 07:49:18 PM »
Your script grabs the username, which may be different from their display name. Use real_name instead of member_name for that.
Try not to become a man of success, but rather try to become a man of value.
- Albert Einstein

Offline pearpandas

  • Semi-Newbie
  • *
  • Posts: 26
    • Cutest Quotes
Re: Download your membership roster to excel
« Reply #6 on: January 10, 2012, 12:31:48 PM »
Thanks!  I was just thinking about needing something like this for one of my sites! :D

Offline naijasoil

  • Newbie
  • *
  • Posts: 2
Re: Download your membership roster to excel
« Reply #7 on: January 25, 2012, 08:28:29 PM »
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 : naijasoil@yahoo.com

Offline Home 4 Answers

  • Newbie
  • *
  • Posts: 8
Re: Download your membership roster to excel
« Reply #8 on: February 01, 2012, 09:25:32 PM »
Any ideas on how I can achieve the same to a .csv?

Thank you!