My response to your other thread on this matter: https://community.socialengine.com/forums/topic/1851/what-is-member-count-and-level-id
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)
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?
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.
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.
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!
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?