How do I export more than 250 users per file?

Hello,

1. How do I export more than 250 users per file?

OR

2. What is an SQL query I can use to export all the exact same data in ONE CSV file using phpmyadmin?

s2Member® Pro ( Import/Export Tools )

says:

CSV File Exportation:
You have a total of 11,016 User/Member rows in the database.
You can export up to 250 database rows in each file...

Not counting the time involved, I’d need to repeat the process more than 40 times to get the data we need. Then I’d have to copy and paste 40+ MS Excel CSV files together.

Thank you for your help doing what seems like an easy task–but which Google and s2Member forums give no easy answer to.

Chris

Thanks for your inquiry.

~ We appreciate your patience :-)

This limit of 250 users per export file is designed to reduce the chance of s2Member exceeding memory and/or script timeout limits on shared hosting platforms. If your server is more powerful, you can certainly export several thousand users at a time. I’ll see what we can do to make this easier in the future, but for now, here is how you can circumvent this limitation.

Please open this installation file:
/s2member-pro/includes/classes/exports-in.inc.php
Search for: LIMIT " . $sql_s . ", 250"

Change 250 to whatever you’d like to try instead.

Please let us know if problems persist :-)

Thank you Jason,

It would be helpful for us and perhaps others if you could give us the SQL to use in phpmyadmin for this
(without the LIMIT–so it will give us all results):

SELECT `" . $wpdb->users . "`.`ID` 
FROM `" . $wpdb->users . "`, `" . $wpdb->usermeta . "` 
WHERE `" . $wpdb->users . "`.`ID` 
= `" . $wpdb->usermeta . "`.`user_id` 
AND `" . $wpdb->usermeta . "`.`meta_key` 
= '" . esc_sql ($wpdb->prefix . "capabilities") . "' 

…where in our phpmyadmin the database is:

nnn_xxxxx

…and the tables are:

nnn_users
nnn_usermeta

Thank you,

Chris

BTW We’re MORE than ready–VERY MORE THAN READY– to pay a reasonable monthly fee for direct, fast support. And I doubt we’re alone in this. From my last year living in the s2Member forums (my almost daily home away from home), I KNOW I’m not alone. Just think about “additional revenue stream,” and how the support given there would end up being much more direct and clean answers to very specific questions that could be paid for by s2Member customers and end up as VERY HELPFUL answers available to the world.

Thanks for the follow-up :-)

I’m not sure I completely understand the question, because the SQL query is in that file. So you can just remove the LIMIT from the end of that SQL query and run it in phpMyAdmin if you like. Or, if you want to just remove the LIMIT from the end of the SQL query in that file, you’ll have s2Member Pro pulling ALL members without any limit, and you would not need to use phpMyAdmin.

I’ll do the best I can to answer your question here though.

This example assumes that you have a database prefix of “wp_”, which is the default DB prefix.

SELECT `wp_users`.`ID` FROM `wp_users`, `wp_usermeta` WHERE `wp_users`.`ID` = `wp_usermeta`.`user_id` AND `wp_usermeta`.`meta_key` = 'wp_capabilities'

This example assumes the database prefix is “nnn_”, instead of the default prefix.

SELECT `nnn_users`.`ID` FROM `nnn_users`, `nnn_usermeta` WHERE `nnn_users`.`ID` = `nnn_usermeta`.`user_id` AND `nnn_usermeta`.`meta_key` = 'nnn_capabilities'

This is the SQL query that s2Member Pro uses, which simply pulls all of the User IDs. Then s2Member iterates these IDs and collects/formats additional details for each User as it constructs your export file. If you want more information for each user via phpMyAdmin, you can modify the query to SELECT all columns in the wp_users table, using an asterisk.

SELECT `wp_users`.* FROM `wp_users`, `wp_usermeta` WHERE `wp_users`.`ID` = `wp_usermeta`.`user_id` AND `wp_usermeta`.`meta_key` = 'wp_capabilities'

BTW We’re MORE than ready–VERY MORE THAN READY– to pay a reasonable monthly fee for direct, fast support. And I doubt we’re alone in this. From my last year living in the s2Member forums (my almost daily home away from home), I KNOW I’m not alone. Just think about “additional revenue stream,” and how the support given there would end up being much more direct and clean answers to very specific questions that could be paid for by s2Member customers and end up as VERY HELPFUL answers available to the world.

I will certainly give this some thought/consideration. Thanks for the feedback!

Please let us know if problems persist :-)

Thank you Jason,

I set the export to 15,000 and it worked on our server at WPEngine.

A question — when s2M upgrades will we need to change the 250 to 15,000 again inside of exports-in.inc.php?

And THANK YOU for the SQL; I’ll use it also.

Chris

Thanks for the follow-up :-)

You are VERY welcome.

A question — when s2M upgrades will we need to change the 250 to 15,000 again inside of exports-in.inc.php?

Yes, unfortunately, you will need to change this each time.

Hello Jason,

As I mentioned, I made the change from 250 to 15000 and the export ran fine. I ran the export and emailed it to my director. However, I didn’t look at the contents of the file before I forwarded it ( me = ID ten T ).

He bounced the file back to me with a question about the First Payment Date.

In the First Payment Date field rather than a date there are variations of data like this:

a:2:{s:5:”level”;s:10:”1342051396″;s:6:”level2″;s:10:”1342051396″;}

a:2:{s:5:”level”;s:10:”1350001043″;s:6:”level2″;s:10:”1350001043″;}

a:4:{s:5:”level”;s:10:”1349239954″;s:6:”level1″;s:10:”1349239954″;s:6:”level4″;s:10:”1354249925″;s:6:”level2″;i:1357928458;}

a:3:{s:5:”level”;s:10:”1342375937″;s:6:”level2″;s:10:”1342375937″;s:6:”level1″;s:10:”1348207773″;}

I really, really did only change the number from 250 to 15k in that file.

How can we get the correct data for that First Payment Date field/column?

Thank you,

Chris

Here’s the exact code line from the page where I made the change (maybe I jacked something?)

if (is_array ($_users = $wpdb->get_results ("SELECT `" . $wpdb->users . "`.`ID` FROM `" . $wpdb->users . "`, `" . $wpdb->usermeta . "` WHERE `" . $wpdb->users . "`.`ID` = `" . $wpdb->usermeta . "`.`user_id` AND `" . $wpdb->usermeta . "`.`meta_key` = '" . esc_sql ($wpdb->prefix . "capabilities") . "' LIMIT " . $sql_s . ", 15000")))

Jason,

Just to check the SQL results I ran the SQL you shared above (using the asterisk).

The SQL returns a different set of data with fewer columns.

SQL field results:

ID
user_login
user_pass
user_nicename
user_email
user_url
user_registered
user_activation_key
user_status
display_name

s2Member® Pro ( Import/Export Tools ) field results:

ID
Username
Password
First Name
Last Name
Display Name
Email
Website Role
Custom Capabilities
Registration Date
First Payment Date
Last Payment Date
Auto-EOT Date
Custom Value
Paid Subscr. ID
Paid Subscr. Gateway

Can you share the SQL to return the export data for all users?

Thank you,

Chris

Thanks for the follow-up :-)

He bounced the file back to me with a question about the First Payment Date.

In the First Payment Date field rather than a date there are variations of data like this:

a:2:{s:5:”level”;s:10:”1342051396″;s:6:”level2″;s:10:”1342051396″;}

This is to be expected. You didn’t mess anything up. When you see data like this, what you’re looking at are multiple values for one database column, in PHP’s serialized string format. See: serialize() if you want the details. s2Member’s default export format is designed to support all features of re-importation. Thus, you get ALL of the data that s2Member has for this column. If there are multiple values, you will get a serialized string like this. There are only a couple of columns in your export file where this is applicable. First Payment Date is one of these. The other is related to any Custom Fields that you might have configured with s2Member. If you have no Custom Registration/Profile Fields, all you’re dealing with in this regard, is First Payment Date(s).

a:2:{s:5:”level”;s:10:”1342051396″;s:6:”level2″;s:10:”1342051396″;}

Regarding the First Payment Date(s), please check the documentation on this import/export column in your Dashboard under this section where we provide full details.
See: Dashboard -› s2Member® -› Import/Export -› User/Member Importation

If you would like a cleaner version (i.e. one which is more readable, please select that option when you do your export). s2Member provides a drop-down menu giving you this option.

---

The SQL returns a different set of data with fewer columns.

The example that I provided pulls details from the `wp_users` table. Other details for each user can be found in the `wp_usermeta` table. This is a WordPress standard. Here is an example query that will give you all details from the `wp_usermeta` table.

SELECT `wp_usermeta`.* FROM `wp_usermeta`

See also: http://codex.wordpress.org/Database_Description#Table:_wp_usermeta

Jason,

Thank you for the guidance, links and support.

Warmest,

Chris

Also – Thank you for your post today: http://www.s2member.com/kb/paypal-pro-payflow-edition/?utm_source=s2Member%C2%AE+Updates&utm_medium=email&utm_campaign=d4e289d952-mailchimp