HOWTO: import from XYZ database directly to phpList MySql

Technical discussion for developers and service providers

HOWTO: import from XYZ database directly to phpList MySql

Postby user123 » 1:00pm, Tue 11 Feb, 2014

Hi guys,

i have a few sites each with it's own DB structure and user data.
I could export all of those DBs to CSV with attributes and import them to phpList, but it would be way too much work to do this daily. So i'm looking for a method to insert it directly into phpList database and call it hourly via cron.

Before i start writing my php code, i would kindly ask you, if any1 is already aware of such a script, which would allow importing from other databases with different table structure?

If not, i was thinking about selecting users and XY attributes from remote DB and inserting them directly into phplist_user_user and phplist_user_user_attribute tables. Must i add records to any other table?

Also what hash function is used for uniqid? Is it just a random string of specified lenght?
user123
phpList newbie
 
Posts: 2
Joined: 12:41pm, Tue 11 Feb, 2014

Re: HOWTO: import from XYZ database directly to phpList MySq

Postby user123 » 1:45pm, Tue 11 Feb, 2014

Looking at the DB we _need_ to insert data into:
phplist_user_user_attribute
phplist_user_user
phplist_listuser

These i guess are optional:
phplist_user_user_history


Can anyone confirm my assumptions?

It looks like it will be pretty easy to do it.
user123
phpList newbie
 
Posts: 2
Joined: 12:41pm, Tue 11 Feb, 2014

Re: HOWTO: import from XYZ database directly to phpList MySq

Postby iseande » 2:38pm, Mon 17 Feb, 2014

I have been playing with this today and so far can add to pl_user_user and pl_listsuer
as the phplist_user_user_attribute was empty and i already have 30 or so users i assume this is not needed! Although the user i just added will not show in the admin panel!

So far i have

$result = mysql_query("SELECT * FROM `pl_user_user` ORDER BY userid DESC LIMIT 1");
while ($row = mysql_fetch_array($result))
{
$userid = $row['userid'];
}
/////
$addemail1 = "INSERT INTO `pl_listuser` (`userid`, `listid`, `entered`, `modified`) VALUES ('$userid', '2', '$added', '$added')";
mysql_query($addemail1) or die("could add news 1");

will update you if i get it working
iseande
phpList newbie
 
Posts: 3
Joined: 1:48pm, Mon 17 Feb, 2014

Re: HOWTO: import from XYZ database directly to phpList MySq

Postby iseande » 2:41pm, Mon 17 Feb, 2014

sorry missed bit of the code!

include("connectnews.php");
$added = date("Y-m-d g:h:i");
$addemail2 = "INSERT INTO `pl_user_user` ( `email`, `confirmed`, `entered`, `modified` , `uniqid`) VALUES ('$email', '1', '$added', '$added' , '$uniqid')";
mysql_query($addemail2) or die("could add news 2");
///get userid
$result = mysql_query("SELECT * FROM `pl_user_user` ORDER BY userid DESC LIMIT 1");
while ($row = mysql_fetch_array($result))
{
$userid = $row['userid'];
}
/////
$addemail1 = "INSERT INTO `pl_listuser` (`userid`, `listid`, `entered`, `modified`) VALUES ('$userid', '2', '$added', '$added')";
mysql_query($addemail1) or die("could add news 1");
iseande
phpList newbie
 
Posts: 3
Joined: 1:48pm, Mon 17 Feb, 2014


Return to Developers Space

Who is online

Users browsing this forum: No registered users and 1 guest