Custom SQL Query

I am wondering what I am doing wrong with PM's custom query functionality. I try a simple test query directly on my Godaddy server using the WA PM database and phpmyAdmin:

SELECT * FROM pm1_users WHERE UserFirstName = "Robert"

This query works on Godaddy bringing up three "Robert's" in my database.

When I use the same query in PM's custom query field, I receive the error message"
Invalid JSON: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM pm1_users WHERE UserFirstName = \"Robert\"' at line 1"

Just in case, I tried the same test for a new column that I have added to the database. The same basic problem occurs in PM, but I am successful with the exact same query code on Godaddy.

The UserFirstName search works in PM Audience Filter, so the connection to the Godaddy database from PM is working.

Any suggestions? Thanks.

use single quotes:
SELECT * FROM pm1_users WHERE UserFirstName = 'Robert'

Thanks for your reply. I tried with single quote, double quotes and not quotes. With single quotes i still receive the message: "Invalid JSON: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM pm1_users WHERE UserFirstName = \'Robert\'' at line 1"

BUT when I try the same code with single or double quotes directly on the Godaddy website in phpmyAdmin, it works fine.

You should only be entering the WHERE clause:
WHERE UserFirstName = 'Robert'

the select statement is implied

Originally Said By: Jason Byrnes

  You should only be entering the WHERE clause:
WHERE UserFirstName = 'Robert'

the select statement is implied  

Hi Jason

Is there a way to add a completely custom query?
I would like to query a different table - not "pm1_users", but my own "users" table, as this holds my registered users for that website.

No, Power Messenger cannot use a different table for the users.

Hi Jason

Thanks for your reply.
o.k. so PM cannot use another table, but pm1_users...

What about renamimg pm1_users to a different name and creating a custom view like:

CREATE VIEW pm1_users AS SELECT
IDUser AS UserID,
NameUser AS UserLastName,
EMailUser AS UserEmail
FROM users

In phpMyAdmin the results are as expected.

I suppose that could work, sounds like it should, but it is not supported.

Originally Said By: Willi Schneider

  Hi Jason

Thanks for your reply.
o.k. so PM cannot use another table, but pm1_users...

What about renamimg pm1_users to a different name and creating a custom view like:

CREATE VIEW pm1_users AS SELECT
IDUser AS UserID,
NameUser AS UserLastName,
EMailUser AS UserEmail
FROM users

In phpMyAdmin the results are as expected.  

Originally Said By: Jason Byrnes

  I suppose that could work, sounds like it should, but it is not supported.  

FYI

After some testing I got this running by creating a view, similar to the one above. The custom view seems to need all the fields from the original pm1_users table. So, adding these to my users table with "NULL" as default value, worked. Powermessenger simply expects the fields to be there. Those column names from my users table, that were different to those from pm1_users must be selected like: "IDUser AS UserID" to correspond the correct names.

It might need some further testing, because, for example, the unsubscribe links from tokens would not work. I guess the easiest way would be to add a custom unsubscribe page with DataBridge...

But for my purpose, to integrate PM into an existing site with another "users" table, this works perfect.

Greetings

Willi