User data export

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • uk chi3f
    Member
    • Dec 2011
    • 63
    • 4.2.X

    [CMS] User data export

    Hi there, I cant seem to find a definitive answer on this,

    Is there a way to export all of my member data to a excel spreadsheet ie, user name, email, and a custom field?

    Reason is we use office to do our mailing but need to check the data every now and again against the user database and clicking every member one by one is tedious where as split screen in a big list is great.

    Thanks
    If i haven't already told you, I've caused the problem on [URL="http://www.xboxplayers.co.uk/site/content.php"]Xbox players official site[/URL="http://www.xboxplayers.co.uk/site/content.php"]
  • Wayne Luke
    vBulletin Technical Support Lead
    • Aug 2000
    • 73981

    #2
    MySQL is a Structured Query Database. You should export any data you need directly from this database using a tool like MySQL Workbench, HeidiSQL or phpMyAdmin.
    Translations provided by Google.

    Wayne Luke
    The Rabid Badger - a vBulletin Cloud demonstration site.
    vBulletin 5 API

    Comment

    • jdfranke
      New Member
      • Aug 2016
      • 8
      • 5.2.x

      #3
      How would I go about exporting our user lists including fields like occupation, title, years of experience, etc. that we have listed as variables when they register?

      Comment

      • jdfranke
        New Member
        • Aug 2016
        • 8
        • 5.2.x

        #4
        Does anyone have an answer to this? Anyone at all?

        Comment

        • Mark.B
          vBulletin Support
          • Feb 2004
          • 24286
          • 6.0.X

          #5
          There's no feature that will do this....you will need to export this out of the database, from memory you would be taking the user table with a join to the profilefield table.
          MARK.B
          vBulletin Support
          ------------
          My Unofficial vBulletin 6.0.0 Demo: https://www.talknewsuk.com
          My Unofficial vBulletin Cloud Demo: https://www.adminammo.com

          Comment


          • jdfranke
            jdfranke commented
            Editing a comment
            Thank you Mark
        • Wayne Luke
          vBulletin Technical Support Lead
          • Aug 2000
          • 73981

          #6
          The basic query would be:
          Code:
          select u.*, uf.* from user as u
             left join userfield as uf on (u.userid=uf.userid)
          Alter the field list to meet your needs.

          If you want the titles of each field you'll have to get that data from the profilefield table and assign it to variables to use in the query above. This query will pull the names:
          Code:
          select p.text, profilefieldid from profilefield AS pf
              LEFT JOIN phrase AS p ON (p.varname LIKE CONCAT('field', pf.profilefieldid, '_title'))
          You would probably want to do this in a MySQL Procedure with Cursor control. Here is the documentation: https://dev.mysql.com/doc/refman/5.7/en/cursors.html

          I'd write this out if I had more time at the moment.
          Translations provided by Google.

          Wayne Luke
          The Rabid Badger - a vBulletin Cloud demonstration site.
          vBulletin 5 API

          Comment


          • jdfranke
            jdfranke commented
            Editing a comment
            Thank you for the detail

          • jdfranke
            jdfranke commented
            Editing a comment
            We are on a cloud version. Can this still be done?

          • Wayne Luke
            Wayne Luke commented
            Editing a comment
            Sorry. I didn't notice that. Unfortunately, you cannot do queries on a cloud install. You can request a backup of the database and restore it into a local version of MySQL to run queries against.
        • jdfranke
          New Member
          • Aug 2016
          • 8
          • 5.2.x

          #7
          jlaird Can you help with this?

          Comment

          widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
          Working...