If I want to import members using a MySQL query, do I need to know the IP address of the members’ computer (in order to supply the required ip_address)? Or have I totally misunderstood?
Thanks Lisa - I’ve read the WIKI entry and it was the referenced thread that led me to the WIKI entry, but neither of those items answers my question. I have to import nearly 1,400 member records. I know from the WIKI entry and the thread that ip_address is one of the required pieces of information that needs to be imported, but it doesn’t seem reasonable to expect that I would know the IP addresses of all 1,400 members.
For example, can I just put in any IP address for each record? Will it then be overwritten the first time each member logs in?
You can put in your IP address or 0.0.0.0, and I believe the next time they update their profile their IP address will be updated. I do not believe it is updated on login.
Thanks Paul - that’s helpful. A follow-up question:
Is it possible in the query to include a spot for the email address, but leave it blank so that members have to fill in their own email address once they’ve logged in for the first time? Something like this:
The email address is a required field. If it is not there, then if they comment there will likely be error messages. Also, forgot password will not work. So, while you can do it (and things might turn out alright) it is not recommended.
Custom member fields are stored in the exp_member_data field, and we do not name the field using the short name but rather using the form m_field_id_1 where the number is ascribed to the short and long name of the field (and the various other custom member field settings).
1. Following on your appreciated heads-up about the name for custom member fields, I have two custom member fields - firstname and lastname. In the control panel, they have a “1” and a “2” next to them. Would this mean that I would name them m_field_id_1 and m_field_id_2, respectively, in the query?
2. Since they are in exp_member_data table, can you give me a suggestion for how I would formulate the query(ies) in order to import most of the information into the exp_members table and the information for the custom member fields into the exp_members_data table? Can I do it all with one query or are multiple queries required?
3. Similarly, and trying to comprehend Rick’s initial comment in this thread:
There are two additional tables that must contain a row for each member, correlated with the member_id of the member:
exp_member_data exp_member_homepage
Would a query (whether it is part of the original query or an additional query) which places the custom member field info into the exp_member_data table also need to have the member_id of the member (and where do I find that - is it the unique_id?)?
4. Finally, do I need to formulate an additional query to create the rows for each member in exp_member_homepage (and what would that query look like?)?
As you can tell, I am worse than a novice when it comes to working with mySQL. I would just hire someone to do it, but my client is a non-profit with limited funds, and I don’t have the funds either. Thanks in advance for any help you can offer - I really appreciate it!
As it is a new member going into the database, you will have to know the inserted member’s member_id value. This is only known after the insert into exp_members is done. Really, to do this, you are going to need to use PHP to do the first, query, discover the member_id, and then create an entry into exp_member_data and exp_member_homepage for the new user. We are looking at a PHP script with multiple queries, and it has to cycle through all of your new members.
If you are unfamiliar with PHP and MySQL, then you might need to have someone help you with this script. It might look something like this:
I’m following this thread, but it requires some time to figure out how this all is going to work.
Why not make a module that allows all users to import member lists? I mean, it would make EE much more visible and popular: all members on a list will notice EE’s brilliance!
It is on our to do list, but it has not been finished because we are working on something else in tandem with it that might change the entire approach.
Very interesting thread. I read the Wiki and the above instructions too but have some questions left.
First the member_id. The members already in the tables are numbered from 1 to 9. Is it not possible to simply apply the next numbers to the next members?
Second. The password. The wiki says “can be MD5 or SHA1”. I do not understand that. Can I put a default word there for the 300 new members I want to introduce through MySQL?
Third. The unique_id. The wiki says: a random 32 character string, used for session cookies. I suppose that I can use the same for all new users?
Very interesting thread. I read the Wiki and the above instructions too but have some questions left.
First the member_id. The members already in the tables are numbered from 1 to 9. Is it not possible to simply apply the next numbers to the next members?
You could, but Paul gives you a method to grab that number to use when writing out data to the database.
$member_id = $DB->insert_id;
Second. The password. The wiki says “can be MD5 or SHA1”. I do not understand that. Can I put a default word there for the 300 new members I want to introduce through MySQL?
Here’s a way of doing it..
$password = 'apple'; $password = md5($password);
Third. The unique_id. The wiki says: a random 32 character string, used for session cookies. I suppose that I can use the same for all new users?
I know very well that it should be much better to include some reading of a file instead of this including the contents in the script. But this worked for me. I was in a hurry.
