close ad
Install the LAtest Updates to Work with CC 2017 and CC 2018
open ad
View Menu

Web development tutorial

Using your own users table for PowerMessenger (experimental)

Tutorial created by Willi Schneider, rw2 Robert & Willi Schneider GbR

Categories: PowerMessenger, MySQL, PHP

rating

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.

arrow downGetting started

What we need:

  1. Make backups of all mentioned tables and files!
  2. Have PM installed on your server.
  3. PHPMyAdmin or any other appropriate database tool
  4. An existing users table, that will be modified slightly

Step 1: Compare the table structures

This screenshot shows the structure of my old and working users table and PM's pm1_users table

Step 2: Alter existing users table - Be careful!

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!

Step 3: Renaming "pm1_users" table

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:

Click OK

Step 4: Creating custom view

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

Step 5: Congrats!

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!!

arrow downThings to remember!

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.

arrow downReviews and comments

Comments will be sent to the author of this tutorial and may not be answered immediately. For general help from WebAssist, please visit technical support.

Sign in to add comments
rating

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.