This tutorial is experimental, so make backups before, and try at your own risk.
This tutorial applies to community sites like, for example, a sports club, where you have several authors contributing and working on that site...
Imagine, you have've had your website up and running already for quite a while. You have a working but simple login system based on your own users table to allow your authors to post articles...
Now you want to integrate PowerMessenger (*PM) to send info to these users.
I'm not talking about a regular newsletter service, that PM was originally made for.
I want to simply inform my authors and colleagues about changes to the admin section or new functions.
o.k. You could just install PM and use the built-in login system. But then you would have to manage two users tables: The one you had already set up and running plus PM's new users table "pm1_users".
Not too good, though...
If any of your authors quits, you would have to delete his data from two tables.
So why not "pimp" the simple and old users table to be used with PM?
Well that sounds easier, than it actually is. PM will only send messages to the users listed in the integrated "pm1_users" table, that comes with the software. And although PM allows custom queries to select a certain audience, these queries will only be performed in that table.
So, we would need to create a workaround.
What we need:
This screenshot shows the structure of my old and working users table and PM's pm1_users table
To prepare the old users table, to be used as the recipients table in PM, we will have to add some columns.
We are not going to change any of the existing column names or values, as this would result in our login system not working anymore!
We are just adding some missing columns (I've experimented on this issue and found out, that PM obviously "needs" these columns to be present, even if they hold empty or "NULL" values)
O.k. let's do it:
Referencing the above screenshot with the two table structures, we can see, that we already have corresponding/matching columns:
IDUser --> UserID
NameUser --> UserLastName
VornameUser --> UserFirstName
EMailUser --> UserEmail
PasswortUser --> UserPassword
So we dont' even touch these columns!!
Now we are going to add the missing ones:
Open PHPMyAdmin, select your old users table and click on structure...
Perform the settings as shown in the following screenshot:
Click OK when you're done.
The following screenshot shows how the table will look like after the changes.
The default types and valuse in this screenshot have worked for me, so just make sure, you have the same data types, lenghts etc. for the added fields
Time for a break! You might want to check, if your old login system is still working... Just kidding!
We are almost done...
Now it's time to rename the original pm1_users table that comes with PM, before we can create our custom view.
Still in PHPMyAdmin click on the pm1_users table and rename it:
Now comes the important part. We will create a custom view in MySQl to be able to use the data from our modified old users table in PM:
Open PHPMyAdmin and click on the SQL tab:
Study the following SQL statement:
CREATE VIEW `pm1_users` AS select `users`.`IDUser` AS `UserID`,`users`.`EMailUser` AS `UserEmail`,`users`.`PasswortUser` AS
`UserPassword`,`users`.`VornameUser` AS `UserFirstName`,`users`.`NameUser` AS `UserLastName`,`users`.`UserCity` AS
`UserCity`,`users`.`UserState` AS `UserState`,`users`.`UserZip` AS `UserZip`,`users`.`UserEmailVerified` AS
`UserEmailVerified`,`users`.`UserRegistrationDate` AS `UserRegistrationDate`,`users`.`UserVErificationCode` AS
`UserVerificationCode`,`users`.`UserIP` AS `UserIP`,`users`.`UserPhone` AS `UserPhone`,`users`.`UserFax` AS
`UserFax`,`users`.`UserCountry` AS `UserCountry`,`users`.`UserAddress` AS `UserAddress`,`users`.`UserAddress2` AS
`UserAddress2`,`users`.`UserGroupID` AS `UserGroupID`,`users`.`UserSubscribed` AS `UserSubscribed` from `users`
Adjust this matching your column names!
Notice the "MyColumnName" AS "pm1_columnName" parts. Example: `users`.`IDUser` AS `UserID`
It's very, very important, that these match your needs!. O.K. if anything goes wrong, you'll hopefully just get error messages from MySQl and try again..., But this is really the essential part of the whole action.
Once you're done click OK
If everything was done correctly, you should now be able to use your existing users table as a base for PM's recipients/audience list.
Give it a try and tell me, if that's ok for you!!
This tutorial is experimental, so make backups before, and try on your own risk!
The column names of my tables will be different to yours. So use your illusion to transfer and modify the needed steps to match your namimg conventions.
Webassist will not be supporting this scenario!
If you intend to have a regular messaging/newsletter system for your site's visitors or any other purpose, PM was originally intended, you will have to install another instance of PM on that same server, but in a different directory, of course. (Please refer to the license of PM as Webassist describes it)
You will/should/must have a second database for that instance of PM, since it will otherwise use the same tables. Maybe WA can comment on this.