[contrib] Import Emails from another Database Solution

[radon]

Hi,
I found a way to import users from an other database:

First I made my "Import table" in the phplist database:

Code: Select all

CREATE TABLE `phplist_import_user` (
  `id` int(11) NOT NULL auto_increment,
  `email` varchar(255) NOT NULL default '',
  `confirmed` tinyint(4) default '0',
  `blacklisted` tinyint(4) default '0',
  `bouncecount` int(11) default '0',
  `entered` datetime default NULL,
  `modified` timestamp(14) NOT NULL,
  `uniqid` varchar(255) default NULL,
  `htmlemail` tinyint(4) default '0',
  `subscribepage` int(11) default NULL,
  `rssfrequency` varchar(100) default NULL,
  `password` varchar(255) default NULL,
  `passwordchanged` date default NULL,
  `disabled` tinyint(4) default '0',
  `extradata` text,
  `foreignkey` varchar(100) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `idx_phplist_user_user_uniqid` (`uniqid`)
) TYPE=MyISAM AUTO_INCREMENT=497 ;


and copied my email adresses who I like to import into the email colum

Then I copied the phplist_user_attribute table and renamed to phplist_import_attribute. The same with phplist_user_attribute to phplist_import_user_attribute

That's it for the database.

The last step is in the php admin interface:
open manage user and click on import users and then import emails from another database.

Server: localhost
User: databaseuser
Password: databasepasswort (NOT phplist admin password)
Database Name: phplist (in my case)
Table prefix: phplist_
Usertable prefix: phplist_import_

And don't forget to choose a list.

That's it

(I tried first do make my tmp folder readable but without success )

[H2B2]

This may come in handy.

Thanks for sharing!

[malek]

important code
i am going to apply now

[malek]

HI
i just made a new table, and followed the given instructions inorder to import my old emails from an old list,

in the first test, i inserted two email address into the new table: phplist_import_user

then i imported them to the phplist original table, and it worked well, i was able to see them from the admin phplist page.

I deleted teh two emails from my admin page, and i made another test but i had this problem:

Copying users

Database error 1054 Unknown column 'phplist_import_user_attribute.attributeid' in 'where clause'

why?

I also have another question:
the imported emails are NOT confirmed, how am i gonig to send them an email?!

THANKS ALOT

[malek]

Hi
I tried the CSV emport and import, which is better and easier

thanks

[sahengala]

I have been stuck at trying to import mails into the PHPList, we have over 1.5 million emails that we need to sent to. Just installed PHPList to give it a try - looks good and promising but importing emails and attributes seems there is no help. Due to the volume issue and not able to insert in CSV format tried the remote database but that looks like it is made to import from an existing PHP installation

Anyone have any idea how this can be done ?

Help greatly appreciated

THanks
Sahen

[lygie]

@sahengala

Hi sahengala,
it may be a bit too late for my reply, but you could try something like the following code.
On my system I can Import 200000 Emails in less then 3 Minutes.

You have no checks if the supplied addresses are correct and you can not add attributes during import, but it is much faster then the original import.

You have to connect your mysql-Database in the Script before the while-loop begins.

Code: Select all

$Dateizeiger = fopen("files/email.csv", "r");

while(($Daten = fgetcsv($Dateizeiger, 200000, ",")) !== FALSE)
{
$Zeilen++;

$sql="INSERT INTO phplist_user_user (id, email,confirmed,htmlemail) VALUES ($Zeilen,'$Daten[0]',1,1)";
mysql_query($sql)or die(mysql_error());

$AnzahlDerFelder = count($Daten);
for ($i=1;$i<=$AnzahlDerFelder;$i++){
    $sql="INSERT INTO phplist_user_user_attribute (attributeid, userid, value) VALUES ($i,$Zeilen,'$Daten[$i]')";
    mysql_query($sql)or die(mysql_error());
   }
}
fclose($Dateizeiger);


[Jackdd]

I am newbie, but have trouble importing large email list, could you tell me where to use the above code?

[undercover]

I could never get the import from another database feature to work. Here's what worked for me to import a LARGE amount of users:

1) create an MS Access database on my PC with a similar structure.
2) massage the data you intend to import to fit the structure (there will be no validation checks).
3) Use MS Access to MySQL utility (google it) to create mysql dump files.
4) Upload to your web site
5) Use BigDump (google it, too) utility to add records to existing PHPlist installation.

You can do over a million records very fast this way, even on shared hosting. However, as mentioned above, there are no validation checks; so, you have to cleanse your data locally before importing it.

[Heritage]

I have been digging into the issue (feature) of:

"phplist - import emails from a remote database"

Seems there is an issue with the code looking for the remote information but actually looking locally.

(Note: I tried on both 2.10.5 and 2.10.7 versions. Also to have it not hang... I left "mark new users as HTML" and "Overwrite existing" users "unchecked".)

So to test this I went to the installation that I had the data on and put in the database info that I wanted it to go to; backwards, but it proved that the first part that checks the remote version is actually checking locally and saying it is checking the remote. Then the "copying users" just copy's from local to local.

So regardless it always writes local. Even if I go into the code and flip/switch the function connect on line 20 and 28 around... it still looks and reads locally.

ConnectLocal connections are on lines 20,125,158,187,212,284 and connectRemote connections are on lines 28,88,148,181,203,248. Is there somewhere where this is coded backwards?

It seems import4.php works as far as reads and writes something locally; however, the code is a bit backwards or improperly coded on actually looking remotely for the data and pulling the remote data to write locally.

I am still battle testing to see what part of the code needs modifying. I tried a few things, but I am not having much luck.

Any insight here would be great. With some simple trouble shooting . . . I think this could be fixed.

[Heritage]

I think the feature should really ask more information of the location of the remote database and the local database. This way it is a bit less hard coded and more flexible to make sure the user can match up each respective tables and such.

Also it should ask what type of SQL is the remote DB and the local DB. I know I would be willing to beta test it.

There is also some ODBC configuring for each database permissions that most DB admins already know.

I agree it is a bit confusing and I questioned if it is not really a feature since there is very little documentation on it. This is really more of an advance issue.

Any comments that can support more effort here would be greatly appreciated. It seems the more we talk about improvements . . . the more things get developed in the next versions.

I used the "bigdump" work around as stated above and by building queries and got around the issue of larger data moves.

[KPaxton]

I would love to be able to use this feature but I personally think it is a bit misleading. I have a website that uses Microsoft SQL Server 2005 and then I have this listserv set up on another machine utilizing MySQL. By the name of the import feature I assumed that I could connect to a remote database(sql server) and import users and email addresses into the local (mysql) database. From what I'm reading here that is obviously not the case. So now I have to do the run-around and export the users from the SQL Server instance to an excel spreadsheet then import them from that to the local instance.

I dunno about you but to me this just doesn't make sense. why would you say import from a remote database when you can't even connect to one to do that!?!?!

This should definitely be a new feature on a future version.

[dleigh]

As Heritage points out, it always connects locally. Actually it DOES do a connection, but the mysql.inc file, which handles the database calls, has a "hard-coded" database connection in the Sql_Query function (line 110 in 2.10.5):

Code: Select all

$result = mysql_query($query,$GLOBALS["database_connection"]);

So, the $GLOBALS["database_connection"] trumps the remote connection that was just retrieved via the $remote = connectRemote(); in import4.php.

Until there's a fix, one needs to hack import4.php (not done it yet) to facilitate the queries.

Just to also add an idea as to HOW to do this. I'm in the process of adding tables and views in an Oscommerce database that will allow phplist to do the import directly from the oscommerce data. It's simply presented to phplist as views on the oscommerce tables so that phplist thinks there's a phplist installation. I tried to use Federated tables to point to phplist_user_attribute and phplist_config but I couldn't get it to work yet, so I've copied those two tables, but for user_user_attribute and user_user, I'm using views on the oscommerce tables. I hope this will facilitate the process a bit so that it can be redone more easily.

[Heritage]

There seems to be somewhat of a fix here:

http://mantis.phplist.com/view.php?id=5157

I am testing now. . .

Not not sure if the release after 2.10.10 will be fixed.

[dleigh]

Actually, I found the solution to be in admin/mysql.inc where, around line 110 you have a query to the database. Unfortunately it specifies TOO MUCH info. What one needs at this point, when, for example in import4.php, you are going back and forth between databases, is to use the CURRENT DATABASE CONNECTION instead of specifying what's in GLOBALS. Here's my hack.

Code: Select all

# hack - David Leigh - 2009-04-09 - remove database connection from the mysql_query
#                                   so that it uses the last defined connection.
#                                   This is to facilitate the remote database email
#                                   import function which switches back and forth.
# original line:  $result = mysql_query($query,$GLOBALS["database_connection"]);
  $result = mysql_query($query);
# end hack