Forums » SEPHP Help and Tips

In engine4_users is there a column relating to profile type?

    • 184 posts
    June 19, 2019 12:08 PM EDT

    In engine4_users is there a column relating to profile type?  By this I mean the question upon signup that asks someone whether they are an expert or a regular user. 

    • 36 posts
    June 23, 2019 12:15 PM EDT
    • 184 posts
    June 23, 2019 4:28 PM EDT

    Thanks but profile type is not the same as level_id.  Profile type only has two types which are expert and regular.  Level ID has 5 types: public, default, moderator, admin and super admin.  I need to know where profile type and first and last name are found in the database.

    • 36 posts
    June 23, 2019 5:14 PM EDT

    Sorry, misunderstood the question. The tables you're looking for are as followed:

     

    Profile Type: `engine4_user_fields_options` (The `label`field holds your profile type names)

    First and Last name: `engine4_user_fields_search` (I don't use First and Last names on my site, rather display names, but the `first_name` and `last_name` fields in this table should be what you're looking for)

    • 184 posts
    June 24, 2019 3:14 PM EDT

     

    Thanks that helped!

    engine4_user_fields_search

                     

                      Profile_type: Expert is 4. Regular is 1.

                      First name and last name are here.

     

    How can I connect this table to the table at engine4_user so that I can line up names with email addresses?


    This post was edited by Donna at June 26, 2019 7:49 AM EDT
    • 36 posts
    June 26, 2019 2:31 AM EDT
    How would you be executing this query is my question? Through the admin control panel? On the user profile? Where would this information be utilized at?
    • 184 posts
    June 26, 2019 5:08 AM EDT

    I will be exporting a csv file from the database and opening it in Microsoft excel.  I would like to line up rows corresponding to users so I can see the first name, last name followed by email address and the other characteristics I have mentioned in these two posts on the subject.

    • 3731 posts
    June 26, 2019 7:49 AM EDT

    @cmackie, I edited your other post as you had linked your database which is unsafe. This is a public forum and linking sensitive areas of your server is extremely unsafe.

    • 36 posts
    June 29, 2019 6:10 PM EDT

    Sorry for the delay. IYes, utilize PHPMyAdmin to execute the following query:

    SELECT engine4_users.user_id, engine4_user_fields_search.first_name, engine4_user_fields_search.last_name, engine4_users.email, engine4_user_fields_options.label, DATE_FORMAT(engine4_users.creation_date,'%b %d %Y %h:%i %p') AS joined, DATE_FORMAT(engine4_users.lastlogin_date,'%b %d %Y %h:%i %p') AS lastlogin FROM engine4_users INNER JOIN engine4_user_fields_values ON engine4_user_fields_values.item_id = engine4_users.user_id INNER JOIN engine4_user_fields_options ON engine4_user_fields_options.option_id = engine4_user_fields_values.value INNER JOIN engine4_user_fields_search ON engine4_user_fields_search.item_id = engine4_users.user_id WHERE engine4_user_fields_options.field_id = '1' GROUP BY user_id ORDER BY engine4_users.user_id ASC

     

    I've even given you additional information like a user's email address, user id, account creation date, and their last login. If you don't need that then let me know. Once you're information populates, you can export as .CSV like you would normally.

     

    Let me know if you face any issues.

    • 184 posts
    July 1, 2019 8:01 AM EDT

    @Donna - which post are you referring to? 

    • 184 posts
    July 1, 2019 8:07 AM EDT

    @OTAw where exactly do I input this.  I am on the page you can see in the screenshot.  I hope I am allowed to upload this Donna:)  I did not include the link in the browser and I hope that's ok.

     

    • 36 posts
    July 1, 2019 8:18 AM EDT

    The "SQL" tab

    • 184 posts
    July 1, 2019 9:16 AM EDT

    A couple of quick questions:

    What are the field_id and item_id columns in engine4_user_fields_options?

    What is the value column in engine4_user_fields_values?

     

    This worked nicely OTAw!  Thank you so much!

     

    I added the extra fields I needed and it seemed to export everything without a problem.  I still have an outstanding related issue. 

     

    The IP addresses get scrambled when I export them.  See screenshot.  Any idea how I can export them without scrambling them?

     

    You might want to answer that question here:

    https://community.socialengine.com/forums/topic/1849/ip-addresses-scrambled

     

    Thanks a million once again OTAw!    You are a life saver!

     

     

     

    • 184 posts
    July 2, 2019 7:34 AM EDT

    One other thing.  I would like to also extract the information related to the following two profile questions:

    "Are you willing to volunteer in your district's campaign?"

    "What House committee does your expertise relate to?"

     See screenshots below. 

     

    Where in the database would this information be found? 

     

     

    • 184 posts
    July 8, 2019 1:52 PM EDT

    Can anyone help me with this?  Where in the database are the answers to profile questions found?

    • 36 posts
    July 10, 2019 12:13 PM EDT

    Most likely in `engine4_user_fields_values`but if not then try `engine4_user_fields_search`

    • 184 posts
    July 10, 2019 1:49 PM EDT

    Thank you.  I'llgive that a try

    • 184 posts
    July 11, 2019 5:52 PM EDT

    I am afraid that only gave me one of the three fields I am looking for.  Any other ideas?

    • 184 posts
    August 6, 2019 1:24 PM EDT

    Can anyone help me with this?  Where in the database are the answers to profile questions found?