[contrib] Import Emails from another Database Solution

Solutions for other advanced phplisters

[contrib] Import Emails from another Database Solution

Postby radon » 7:58pm, Mon 25 Sep, 2006

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 :D

(I tried first do make my tmp folder readable but without success ) :roll:
radon
phpList newbie
 
Posts: 2
Joined: 7:37pm, Mon 25 Sep, 2006

Postby H2B2 » 2:53pm, Tue 26 Sep, 2006

This may come in handy. Thanks for sharing!

EDIT:
See also [mod] Import emails from any database
H2B2
Moderator
 
Posts: 7188
Joined: 1:51am, Wed 15 Mar, 2006

Got an error while importing

Postby malek » 1:50pm, Thu 16 Nov, 2006

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
phpLister
 
Posts: 8
Joined: 6:29pm, Sat 11 Nov, 2006

Another Import method

Postby malek » 7:30pm, Fri 17 Nov, 2006

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

thanks
malek
phpLister
 
Posts: 8
Joined: 6:29pm, Sat 11 Nov, 2006

Postby sahengala » 1:39pm, Fri 16 Nov, 2007

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
sahengala
phpList newbie
 
Posts: 1
Joined: 1:21pm, Fri 16 Nov, 2007

Postby lygie » 3:41pm, Mon 11 Feb, 2008

@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);
lygie
PL Nut
 
Posts: 19
Joined: 11:17am, Tue 24 Jul, 2007

Postby undercover » 3:05am, Wed 20 Aug, 2008

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.
undercover
phpLister
 
Posts: 7
Joined: 9:07pm, Thu 19 Jun, 2008
Location: Seattle, WA

import emails from a remote database

Postby Heritage » 2:56pm, Tue 18 Nov, 2008

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
Moderator
 
Posts: 223
Joined: 3:25pm, Fri 23 Jun, 2006
Location: 800 720-7301

Postby Heritage » 8:24pm, Fri 21 Nov, 2008

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.
Heritage
Moderator
 
Posts: 223
Joined: 3:25pm, Fri 23 Jun, 2006
Location: 800 720-7301

Postby KPaxton » 8:37pm, Fri 21 Nov, 2008

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.
KPaxton
phpLister
 
Posts: 7
Joined: 4:49pm, Mon 16 Jun, 2008

Re: [contrib] Import Emails from another Database Solution

Postby dleigh » 4:43pm, Thu 09 Apr, 2009

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.
dleigh
phpLister
 
Posts: 9
Joined: 1:49pm, Wed 19 Oct, 2005
Location: Soultz, Alsace, France

Re: [contrib] Import Emails from another Database Solution

Postby Heritage » 9:07pm, Fri 19 Jun, 2009

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.
Heritage
Moderator
 
Posts: 223
Joined: 3:25pm, Fri 23 Jun, 2006
Location: 800 720-7301

Re: [contrib] Import Emails from another Database Solution

Postby dleigh » 9:19am, Sat 20 Jun, 2009

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
dleigh
phpLister
 
Posts: 9
Joined: 1:49pm, Wed 19 Oct, 2005
Location: Soultz, Alsace, France

Re: [contrib] Import Emails from another Database Solution

Postby wasuaje » 3:52pm, Wed 25 Aug, 2010

I've written a solution, tested and working now.

Code: Select all
<?php
//***********************************************************************************************************************
// General Idea taken from  lygie work
// CSV format first line= mail;attr_id1;attr_id2;attr_id3;attr_id4;attr_idn
// CSV format rest of lines= "mail@mydomain";"attr_val1";attr_val2;att_val3;"attr_val4";attrvaln
//example of CVS:
//correo;22;10;11;30;31;32;9
//"xxxxdddfff@madmail.com";782;"name";"surname";"N";"S";"";"M"
//"asd3423asd@soft.net";783;"name";"surname";"S";"S";"";"M"
//"asdasd32@hardmail.net";784;"name";"surname";"S";"N";"";"F"
//"dadsa32323@gmail.com";785;"AAA";"XXXXXXXXXXX";"S";"S";"";"F"
//
//run it = php bulk_insert.php
//************************************************************************************************************************

//location of csvfile
$CSV="usuarios.csv";
//$database_host = "localhost";
$database_host = "YOUIPHERE";
//what is the name of the database we are using
$database_name = "phplist";
// who do we log in as?
$database_user = "YOURUSER";
// and what password do we use
$database_password = 'YOURPASS';
//me conecto a la bbdd
$conexion = mysql_connect($database_host, $database_user, $database_password);
mysql_select_db($database_name,$conexion);

if ($conexion){
//   echo "todo bien";
   }
else{
//   echo "opps";   
   die(mysql_error());
   }
//abro el archivo csv
$Dateizeiger = fopen($CSV, "r");
$Zeilen=0;

while(($Daten = fgetcsv($Dateizeiger, 200000, ";")) !== FALSE)
   {
   $Zeilen++;
   $cantcampos = count($Daten);
   //salvamos un array con los id de atributo - need to keep the attrib ids somewhere
   if ($Zeilen==1){
      for ($i=0;$i<=$cantcampos;$i++){         
         $acampos=$Daten;
      }
   } else {
      //verificamos que usuario existe! - check if user exists! to avoid too much inserts each time
      $valor=strtr($Daten[0],"'","");         
      $valor=mysql_escape_string($valor);
      $sql="SELECT id FROM phplist.phplist_user_user where email='$valor';";
      //echo $sql;
      $result=mysql_query($sql) or die($sql);
      if ($result) $num_rows = mysql_num_rows($result);
      if ($num_rows>0){
         $row=mysql_fetch_row($result);
         $Id=$row[0];
      }else{            //if it doesnt exist insert it
         $valor=strtr($Daten[0],"'","");         
         $valor=mysql_escape_string($valor);
         $sql="INSERT INTO phplist_user_user (email,confirmed,htmlemail) VALUES ('$valor',1,1);";
         mysql_query($sql) or die($sql);
         $Id= mysql_insert_id();
      }

      for ($i=1;$i<=$cantcampos-1;$i++){   //pass trough csv cols with attr_id
                     //erase this atribbute if exists
          $sql="DELETE FROM phplist_user_user_attribute WHERE attributeid=$acampos[$i] AND userid=$Id;";
           mysql_query($sql)or die(mysql_error());
            $valor=mysql_escape_string($Daten[$i]);      //insert the attribute
           $sql="INSERT INTO phplist_user_user_attribute (attributeid, userid, value) VALUES ($acampos[$i],$Id,'$valor');";
          mysql_query($sql)or die($sql);           
         
         }
      
     }//if zeilen=1
} //while

fclose($Dateizeiger);



Hope it to be helpfull
wasuaje
phpList newbie
 
Posts: 1
Joined: 3:43pm, Wed 25 Aug, 2010

Re: [contrib] Import Emails from another Database Solution

Postby schkovich » 11:43am, Sat 18 Sep, 2010

dleigh wrote: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.

If so then whenever switching from local to remote connection and vice versa previous connection should be closed. Otherwise running import4.php will open 10+ connections.

This script could be used to import emails from any database following suggestion made by dleigh:
dleigh wrote: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
.

Here is patch to import4.php. Patch is for v2.10.12. Please note that patch IS NOT properly tested and it might be buggy. :)
Code: Select all
# This patch file was generated by NetBeans IDE
# Following Index: paths are relative to: ${HOME}/NetBeansProjects/phpList/trunk/public_html/lists/admin
# This patch can be applied using context Tools: Patch action on respective folder.
# It uses platform neutral UTF-8 encoding and \n newlines.
# Above lines and this line are ignored by the patching process.
Index: import4.php
--- import4.php Remotely Modified (Based On HEAD)
+++ import4.php Locally Modified (Based On LOCAL)
@@ -7,16 +7,52 @@
 
 # import from a different PHPlist installation
 
-if ($require_login && !isSuperUser()) {
-  $access = accessLevel("import4");
-  if ($access == "owner")
-    $subselect = " where owner = ".$_SESSION["logindetails"]["id"];
-  elseif ($access == "all")
-    $subselect = "";
-  elseif ($access == "none")
-    $subselect = " where id = 0";
+function MySql_Query($query, $ignore = 0, $link_identifier = null) {
+  if (isset($GLOBALS['lastquery'])) {
+    unset($GLOBALS['lastquery']);
 }
+  if (isset($GLOBALS["developer_email"])) {
+  #  if (preg_match("/dev$/",VERSION))
+  #  print "<b>$query</b><br>\n";
+  #  if ($GLOBALS["commandline"]) {
+  #    ob_end_clean();
+  #    print "Sql: $query\n";
+  #    ob_start();
+  #  }
+    # time queries to see how slow they are, so they can
+    # be optimized
+    $now =  gettimeofday();
+    $start = $now["sec"] * 1000000 + $now["usec"];
+    $GLOBALS['lastquery'] = $query;
+  }
+  $GLOBALS["pagestats"]["number_of_queries"]++;
+  if (null === $link_identifier) {
+    $link_identifier = $GLOBALS["database_connection"];
+  }
+  $result = mysql_query($query,$link_identifier);
+  if (!$ignore) {
+    if (Sql_Check_Error($link_identifier))
+      dbg("Sql error in $query");
+  }
+  if (isset($GLOBALS["developer_email"])) {
+    # log time queries take
+    $now = gettimeofday();
+    $end = $now["sec"] * 1000000 + $now["usec"];
+    $elapsed = $end - $start;
+    if ($elapsed > 300000) {
+      $query = substr($query,0,200);
+      sqllog(' ['.$elapsed.'] '.$query,"/tmp/phplist-sqltimer.log");
+    }
+  }
 
+  return $result;
+}
+
+function MySql_Fetch_Row_Query($query, $ignore = 0, $link_identifier = null) {
+  $req = MySql_Query($query, $ignore, $link_identifier);
+  return Sql_Fetch_Row($req);
+}
+
 function connectLocal() {
   $database_connection = Sql_Connect(
     $GLOBALS["database_host"],
@@ -26,27 +62,57 @@
    return $database_connection;
 }
 function connectRemote() {
-  return Sql_Connect($_POST["remote_host"],
-  $_POST["remote_user"],
-  $_POST["remote_password"],
-  $_POST["remote_database"]);
+  return Sql_Connect(
+    filter_input(INPUT_POST, "remote_host", FILTER_SANITIZE_STRING),
+    filter_input(INPUT_POST, "remote_user", FILTER_SANITIZE_STRING),
+    filter_input(INPUT_POST, "remote_password", FILTER_SANITIZE_STRING),
+    filter_input(INPUT_POST, "remote_database", FILTER_SANITIZE_STRING)
+    );
 }
+/**
+ * Do not rely upon already existing database connection
+ * but explicity create new one
+ *
+ * @var $local_link_identifier resource
+ */
+$local_link_identifier = connectLocal();
 
-$result = Sql_query("SELECT id,name FROM ".$tables["list"]." $subselect ORDER BY listorder");
+if (!$_POST["remote_host"] ||
+  !$_POST["remote_user"] ||
+  !$_POST["remote_password"] || !$_POST["remote_database"]) {
+
+  if ($require_login && !isSuperUser()) {
+    $access = accessLevel("import4");
+    if ($access == "owner")
+      $subselect = " where owner = ".$_SESSION["logindetails"]["id"];
+    elseif ($access == "all")
+      $subselect = "";
+    elseif ($access == "none")
+      $subselect = " where id = 0";
+  }
+
+  $result = MySql_Query(
+          "SELECT id,name FROM ".$tables["list"]." $subselect ORDER BY listorder",
+          0,
+          $local_link_identifier
+          );
+  /**
+   * Holds array of available list
+   *
+   * @var $available_lists array
+   */
+  $available_lists = array();
 while ($row = Sql_fetch_array($result)) {
   $available_lists[$row["id"]] = $row["name"];
-  $some = 1;
 }
-if (!$some)
+  if (empty ($available_lists)) {
  # @@@@ not sure about this one:
  echo $GLOBALS['I18N']->get('No lists available').', '.PageLink2("editlist",$GLOBALS['I18N']->get('add_list'));
 #foreach ($_POST as $key => $val) {
 #  print "$key => $val<br/>";
 #}
+  }
 
-if (!$_POST["remote_host"] ||
-  !$_POST["remote_user"] ||
-  !$_POST["remote_password"] || !$_POST["remote_database"]) {
   printf( '
   <p>'.$GLOBALS['I18N']->get('remote_server').'</p>
   <form method=post>
@@ -85,8 +151,8 @@
   include_once("structure.php");
   print $GLOBALS['I18N']->get('connecting_remote')."<br/>";
   flush();
-  $remote = connectRemote();
-  if (!$remote) {
+  $remote_link_identifier = connectRemote();
+  if (!$remote_link_identifier) {
     Fatal_Error($GLOBALS['I18N']->get('cant_connect'));
     return;
   }
@@ -100,16 +166,28 @@
   );
   print $GLOBALS['I18N']->get('getting_data').$_POST["remote_database"]."@".$_POST["remote_host"]."<br/>";
 
-  $version = Sql_Fetch_Row_Query("select value from {$remote_tables["config"]} where item = \"version\"");
+  $version = MySql_Fetch_Row_Query(
+          "select value from {$remote_tables["config"]} where item = \"version\"",
+          0,
+          $remote_link_identifier
+          );
   print $GLOBALS['I18N']->get('remote_version')." $version[0]<br/>\n";
-  $usercnt = Sql_Fetch_Row_Query("select count(*) from {$remote_tables["user"]}");
+  $usercnt = MySql_Fetch_Row_Query(
+          "select count(*) from {$remote_tables["user"]}",
+          0,
+          $remote_link_identifier
+          );
   print $GLOBALS['I18N']->get('remote_has')." $usercnt[0] ".$GLOBALS['I18N']->get('users')."<br/>";
   if (!$usercnt[0]) {
     Fatal_Error($GLOBALS['I18N']->get('no_users_to_copy'));
     return;
   }
   $totalusers = $usercnt[0];
-  $listcnt = Sql_Fetch_Row_Query("select count(*) from {$remote_tables["list"]}");
+  $listcnt = MySql_Fetch_Row_Query(
+          "select count(*) from {$remote_tables["list"]}",
+          0,
+          $remote_link_identifier
+          );
   print $GLOBALS['I18N']->get('remote_has')." $listcnt[0] ".$GLOBALS['I18N']->get('lists')."<br/>";
 
   flush();
@@ -117,15 +195,25 @@
   # first copy the lists across
   $listmap = array();
   $remote_lists = array();
-  $lists_req = Sql_Query("select * from {$remote_tables["list"]}");
+  $lists_req = MySql_Query(
+          "select * from {$remote_tables["list"]}",
+          0,
+          $remote_link_identifier
+          );
   while ($row = Sql_Fetch_Array($lists_req)) {
     array_push($remote_lists,$row);
   }
 
-  connectLocal();
   foreach ($remote_lists as $list) {
-    $localid_req = Sql_Fetch_Row_Query(sprintf('select id from %s where name = "%s"',
-      $tables["list"],$list["name"]));
+    $localid_req = MySql_Fetch_Row_Query(
+            sprintf(
+                    'select id from %s where name = "%s"',
+                    $tables["list"],
+                    $list["name"]
+                    ),
+            0,
+            $local_link_identifier
+            );
     if ($localid_req[0]) {
       $listmap[$list["id"]] = $localid_req[0];
        print $GLOBALS['I18N']->get('list').' '.$list["name"] .$GLOBALS['I18N']->get('exists_locally')." <br/>\n";
@@ -138,27 +226,40 @@
        }
       $query = substr($query,0,-1);
        print $GLOBALS['I18N']->get('list')." ".$list["name"] .$GLOBALS['I18N']->get('created_locally')." <br/>\n";
-      Sql_Query("insert into {$tables["list"]} set $query");
+      MySql_Query(
+              "insert into {$tables["list"]} set $query",
+              0,
+              $local_link_identifier
+              );
       $listmap[$list["id"]] = Sql_Insert_id();
     } else {
        print $GLOBALS['I18N']->get('remote_list')." ".$list["name"] .$GLOBALS['I18N']->get('not_created')." <br/>\n";
     }
   }
 
-  connectRemote();
   print '<h1>'.$GLOBALS['I18N']->get('copying_attribs').'</h1>';
   # now copy the attributes
   $attributemap = array();
   $remote_atts = array();
-  $att_req = Sql_Query("select * from {$remote_tables["attribute"]}");
+  $att_req = MySql_Query(
+          "select * from {$remote_tables["attribute"]}",
+          0,
+          $remote_link_identifier
+          );
   while ($row = Sql_Fetch_Array($att_req)) {
     array_push($remote_atts,$row);
   }
 
-  connectLocal();
   foreach ($remote_atts as $att) {
-    $localid_req = Sql_Fetch_Row_Query(sprintf('select id from %s where name = "%s"',
-      $tables["attribute"],stripslashes($att["name"])));
+    $localid_req = MySql_Fetch_Row_Query(
+            sprintf(
+                    'select id from %s where name = "%s"',
+                    $tables["attribute"],
+                    stripslashes($att["name"])
+                    ),
+            0,
+            $local_link_identifier
+            );
     if ($localid_req[0]) {
       $attributemap[$att["id"]] = $localid_req[0];
        print $GLOBALS['I18N']->get('attrib')." ".$att["name"] .$GLOBALS['I18N']->get('exists_locally')." <br/>\n";
@@ -171,23 +272,41 @@
        }
       $query = substr($query,0,-1);#
        print $GLOBALS['I18N']->get('attrib')." ".$att["name"].$GLOBALS['I18N']->get('created_locally')." <br/>\n";
-      Sql_Query("insert into {$tables["attribute"]} set $query");
-      $attributemap[$att["id"]] = Sql_Insert_id();
+      MySql_Query(
+              "insert into {$tables["attribute"]} set $query",
+              0,
+              $local_link_identifier
+              );
+      $attributemap[$att["id"]] = Sql_Insert_id($local_link_identifier);
       if ($att["type"] == "select" || $att["type"] == "radio" || $att["type"] == "checkboxgroup") {
         $query = "create table if not exists $table_prefix"."listattr_".$att["tablename"]."
         (id integer not null primary key auto_increment,
         name varchar(255) unique,listorder integer default 0)";
-        Sql_Query($query,0);
-        connectRemote();
-        $attvalue_req = Sql_Query("select id,name,listorder from ".$_POST["remote_prefix"]."listattr_".$att["tablename"]);
+        MySql_Query($query,0, $local_link_identifier);
+        $attvalue_req = MySql_Query(
+                "select id,name,listorder from ".
+                filter_input(INPUT_POST, "remote_prefix", FILTER_SANITIZE_STRING).
+                "listattr_".$att["tablename"],
+                0,
+                $remote_link_identifier
+                );
         $values = array();
         while ($value = Sql_Fetch_Array($attvalue_req)) {
           array_push($values,$value);
         }
-        connectLocal();
         foreach ($values as $value) {
-          Sql_Query(sprintf('replace into %slistattr_%s (name,id,listorder)
-            values("%s",%d,"%s")',$table_prefix,$att["tablename"],addslashes($value["name"]),$value["id"],$value["listorder"]));
+          MySql_Query(
+                  sprintf(
+                          'replace into %slistattr_%s (name,id,listorder) values("%s",%d,"%s")',
+                          $table_prefix,
+                          $att["tablename"],
+                          addslashes($value["name"]),
+                          $value["id"],
+                          $value["listorder"]
+                          ),
+                  0,
+                  $local_link_identifier
+                  );
         }
       }
     }
@@ -200,8 +319,11 @@
   $newcnt = 0;
   while ($usercnt < $totalusers) {
     set_time_limit(60);
-    connectRemote();
-    $req = Sql_Query("select * from {$remote_tables["user"]} limit $usercnt,1");
+    $req = MySql_Query(
+            "select * from {$remote_tables["user"]} limit $usercnt,1",
+            0,
+            $remote_link_identifier
+            );
     $user = Sql_Fetch_Array($req);
     $usercnt++;
     $new = 0;
@@ -209,9 +331,16 @@
       print "$usercnt / $totalusers<br/>";
       flush();
     }
-    connectLocal();
     $query = "";
-    $exists = Sql_Fetch_Row_Query(sprintf('select id from %s where email = "%s"',$tables["user"],$user["email"]));
+    $exists = MySql_Fetch_Row_Query(
+            sprintf(
+                    'select id from %s where email = "%s"',
+                    $tables["user"],
+                    $user["email"]
+                    ),
+            0,
+            $local_link_identifier
+            );
     if ($exists[0]) {
       $existcnt++;
   #    print $user["email"] .$GLOBALS['I18N']->get('exists_locally')." ..";
@@ -236,32 +365,65 @@
        }
       $query = substr($query,0,-1);
       #print $query . "<br/>";
-      Sql_Query("$query");
-      $userid = Sql_Insert_id();
+      MySql_Query("$query", 0, $local_link_identifier);
+      $userid = Sql_Insert_id($local_link_identifier);
     }
     if ($userid && $_POST["markhtml"]) {
-      Sql_Query("update {$tables["user"]} set htmlemail = 1 where id = $userid");
+      MySql_Query(
+              "update {$tables["user"]} set htmlemail = 1 where id = $userid",
+              0,
+              $local_link_identifier
+              );
     }
 
     if ($new || (!$new && $_POST["overwrite"])) {
       # now check for attributes and list membership
-      connectRemote();
       $useratt = array();
-      $req = Sql_Query("select * from {$remote_tables["user_attribute"]},
-        {$remote_tables["attribute"]} where {$remote_tables["user_attribute"]}.attributeid =
-        {$remote_tables["attribute"]}.id and {$remote_tables["user_attribute"]}.userid = $user[0]");
+      $req = MySql_Query(
+              "select * from {$remote_tables["user_attribute"]},
+              {$remote_tables["attribute"]}
+              where {$remote_tables["user_attribute"]}.attributeid =
+              {$remote_tables["attribute"]}.id
+              and {$remote_tables["user_attribute"]}.userid = $user[0]",
+              0,
+              $remote_link_identifier
+              );
       while ($att = Sql_Fetch_Array($req)) {
         $value = "";
         switch ($att["type"]) {
           case "select":
           case "radio":
-            $valreq = Sql_Fetch_Row_Query(sprintf('select name from %slistattr_%s where id = %d',
-              $_POST["remote_prefix"],$att["tablename"],$att["value"]));
+            $valreq = MySql_Fetch_Row_Query(
+                    sprintf(
+                            'select name from %slistattr_%s where id = %d',
+                            filter_input(
+                                    INPUT_POST,
+                                    "remote_prefix",
+                                    FILTER_SANITIZE_STRING
+                                    ),
+                            $att["tablename"],
+                            $att["value"]
+                            ),
+                    0,
+                    $remote_link_identifier
+                    );
             $value = $valreq[0];
             break;
           case "checkboxgroup":
-            $valreq = Sql_Query(sprintf('select name from %slistattr_%s where id in (%s)',
-              $_POST["remote_prefix"],$att["tablename"],$att["value"]));
+            $valreq = MySql_Query(
+                    sprintf(
+                            'select name from %slistattr_%s where id in (%s)',
+                            filter_input(
+                                    INPUT_POST,
+                                    "remote_prefix",
+                                    FILTER_SANITIZE_STRING
+                                    ),
+                            $att["tablename"],
+                            $att["value"]
+                            ),
+                    0,
+                    $remote_link_identifier
+                    );
             while ($vals = Sql_fetch_Row($valreq)) {
               $value .= $vals[0].',';
             }
@@ -273,30 +435,53 @@
       $userlists = array();
       $userlists = array_merge($_POST["lists"],$userlists);
       if ($_POST["copyremotelists"]) {
-        $req = Sql_Query("select * from {$remote_tables["listuser"]},
-          {$remote_tables["list"]} where {$remote_tables["listuser"]}.listid =
-          {$remote_tables["list"]}.id and {$remote_tables["listuser"]}.userid = $user[0]");
+        $req = MySql_Query(
+                "select * from {$remote_tables["listuser"]},
+                {$remote_tables["list"]} where {$remote_tables["listuser"]}.
+                listid = {$remote_tables["list"]}.id
+                and {$remote_tables["listuser"]}.userid = $user[0]",
+                0,
+                $remote_link_identifier
+                );
         while ($list = Sql_Fetch_Array($req)) {
         #  print $list["name"]."<br/>";
           array_push($userlists,$list);
         }
       }
-      connectLocal();
       foreach ($useratt as $att) {
         $localattid = $attributemap[$att["attributeid"]];
         if (!localattid) {
           print $GLOBALS['I18N']->get('no_mapped_attrib')." ".$att["name"]."<br/>";
         } else {
-          $tname = Sql_Fetch_Row_Query("select tablename from {$tables["attribute"]} where id = $localattid");
+          $tname = MySql_Fetch_Row_Query(
+                  "select tablename from {$tables["attribute"]} where id = $localattid",
+                  0,
+                  $local_link_identifier
+                  );
           switch ($att["type"]) {
             case "select":
             case "radio":
-              $valueid = Sql_Fetch_Row_Query(sprintf('select id from %slistattr_%s where name = "%s"',
-                $table_prefix,$tname[0],$att["displayvalue"]));
+              $valueid = MySql_Fetch_Row_Query(
+                      sprintf(
+                              'select id from %slistattr_%s where name = "%s"',
+                              $table_prefix,
+                              $tname[0],
+                              $att["displayvalue"]
+                              ),
+                      0,
+                      $local_link_identifier
+                      );
               if (!$valueid[0]) {
-                Sql_Query(sprintf('insert into %slistattr_%s set name = "%s"',
-                $table_prefix,$tname[0],$att["displayvalue"]));
-                $att["value"] = Sql_Insert_id();
+                MySql_Query(
+                        sprintf(
+                                'insert into %slistattr_%s set name = "%s"',
+                                $table_prefix,$tname[0],
+                                $att["displayvalue"]
+                                ),
+                        0,
+                        $local_link_identifier
+                        );
+                $att["value"] = Sql_Insert_id($local_link_identifier);
               } else {
                 $att["value"] = $valueid[0];
               }
@@ -306,12 +491,28 @@
               array_pop($vals);
               $att["value"] = "";
               foreach ($vals as $val) {
-                $valueid = Sql_Fetch_Row_Query(sprintf('select id from %slistattr_%s where name = "%s"',
-                  $table_prefix,$tname[0],$val));
+                $valueid = MySql_Fetch_Row_Query(
+                        sprintf(
+                                'select id from %slistattr_%s where name = "%s"',
+                                $table_prefix,
+                                $tname[0],
+                                $val
+                                ),
+                        0,
+                        $local_link_identifier
+                        );
                 if (!$valueid[0]) {
-                  Sql_Query(sprintf('insert into %slistattr_%s set name = "%s"',
-                  $table_prefix,$tname[0],$val));
-                  $att["value"] .= Sql_Insert_id().',';
+                  MySql_Query(
+                          sprintf(
+                                  'insert into %slistattr_%s set name = "%s"',
+                                  $table_prefix,
+                                  $tname[0],
+                                  $val
+                                  ),
+                          0,
+                          $local_link_identifier
+                          );
+                  $att["value"] .= Sql_Insert_id($local_link_identifier).',';
                 } else {
                   $att["value"] .= $valueid[0].",";
                 }
@@ -320,9 +521,17 @@
               break;
           }
           if ($att["value"]) {
-            Sql_Query(sprintf('replace into %s set
-              attributeid = %d, userid = %d, value = "%s"',
-              $tables["user_attribute"],$localattid,$userid,addslashes($att["value"])));
+            MySql_Query(
+                    sprintf(
+                            'replace into %s set attributeid = %d, userid = %d, value = "%s"',
+                            $tables["user_attribute"],
+                            $localattid,
+                            $userid,
+                            addslashes($att["value"])
+                            ),
+                    0,
+                    $local_link_identifier
+                    );
           }
         }
       }
@@ -330,8 +539,16 @@
     if (is_array($userlists))
     foreach ($userlists as $list) {
       if ($listmap[$list["listid"]]) {
-        Sql_Query(sprintf('replace into %s (listid,userid) values(%d,%d)',
-          $tables["listuser"],$listmap[$list["listid"]],$userid));
+        MySql_Query(
+                sprintf(
+                        'replace into %s (listid,userid) values(%d,%d)',
+                        $tables["listuser"],
+                        $listmap[$list["listid"]],
+                        $userid
+                        ),
+                0,
+                $local_link_identifier
+                );
        } else {
         print $GLOBALS['I18N']->get('no_local_list')." ".$list["name"]."<br/>";
       }
@@ -339,6 +556,8 @@
   }
   print "$totalusers / $totalusers<br/>";
   flush();
+  mysql_close($local_link_identifier);
+  mysql_close($remote_link_identifier);
  # @@@@ Not sure about this one:
    printf('%s %d %s %s %d %s<br/>',$GLOBALS['I18N']->get('Done'),$newcnt,
    $GLOBALS['I18N']->get('new users'),
@@ -346,5 +565,3 @@
    $existcnt,$GLOBALS['I18N']->get('existing users'));
 }
 ?>
-
-
schkovich
phpLister
 
Posts: 5
Joined: 11:54pm, Fri 17 Sep, 2010
Location: Belgrade, Serbia

Next

Return to Advanced Answers, Howtos, Tips & Tricks

Who is online

Users browsing this forum: No registered users and 1 guest