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.
My response to your other thread on this matter: https://community.socialengine.com/forums/topic/1851/what-is-member-count-and-level-id
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.
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.
@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.
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.
@Donna - which post are you referring to?
@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.
The "SQL" tab
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?
Can anyone help me with this? Where in the database are the answers to profile questions found?
Most likely in `engine4_user_fields_values`but if not then try `engine4_user_fields_search`
Thank you. I'llgive that a try
I am afraid that only gave me one of the three fields I am looking for. Any other ideas?
Can anyone help me with this? Where in the database are the answers to profile questions found?