Searching an external database

Hi Douglas,

At this time the plugin supports connecting to external databases in the Data Explorer and Data Publisher. The next release (avialable by the end of this week) supports to connect to other databases from Data Projects as well.

You can use the Data Publisher to add a read-only table to your website. You can use Data Projects to add tables with data entry forms to your website (front-end) or back-end (WordPress dashboard).

But…let’s define “external database” to make sure we’re on the same page! The plugin supports connecting to external databases that are in the same MySQL instance only. The plugin does not support connecting to external databases that run in another instance (e.g. another server). So if you can import the external database into your wpdb instance, it should work.

I hope this helps! Let me know if you need assistance…

Best regards,
Peter

Thank you, Peter. No, our external database is not in the same MySQL instance. It runs on a different server. We created it as entirely separate so outside organizations could access it also without getting into the internals of our primary website. I have seen where it’s possible to create a separate wpdb instance within WordPress https://wordpress.stackexchange.com/questions/1604/using-wpdb-to-connect-to-a-separate-database, but I haven’t seen an easy way to then use well-developed plugins to access that new instance. Is this sort of thing possible?

Hi Douglas,

Thank you for that link! I think this can be added relatively simpel, but I need a little time to do some research and coding. I’ll come back to you asap…

Thank you for your input!

Best regards,
Peter

Hi Douglas,

I have started working on remote database access from WordPress. It is possible, but it needs more time than I initially expected. Due to my daily routine at the university I am not able to work full time on this plugin, so I cannot promise when it is available. I will inform you when it does…

Best regards,
Peter

Thank you Peter, I appreciate this very much. Given the season of the year I think no one can expect rapid responses on things like this. Our database isn’t ready to be made public anyway (I have a number of internal issues with it to address), so there’s no great hurry. I look forward to what you develop!

Hello Peter. How are things coming with this remote database access? I’m not trying to hurry you, I’m just curious. Our database is still being updated (we’re having a discussion with our ISP about getting CLI access to the server so we can do batch updates of records with scripts rather than trying to do everything through phpMyAdmin, as we need to update roughly 1500 records), so we’re not quite ready for it anyway. I really look forward to using your plugin!

Hi Douglas,

The next version is almost ready and supports remote database connections for all features. I just need to update the Data Backup feature, all other features are ready. It has been a lot work as database connections are used all over the plugin. I hope to release the next version next week or the week after. ?? I’ll let you know…

Have a nice weekend!

Best regards,
Peter

Hi Douglas,

I wanted to let you know that version 3 is out and offers full support for remote database access. The new feature is described here:
https://wpdataaccess.com/2020/01/30/remote-database-connections-available-in-all-tools/

Let know if you need help.

Best regards,
Peter

Thank you Peter! I really appreciate all the effort you put into making WP Dada Access the right tool for connecting to external databases from within WordPress. So far as I know yours is the only plugin able to do this cleanly and easily!

I am trying to use the Remote Connection, after I enter all the data, it says Preparing Connection for a long time and nothing happens … Any suggestions where I can look please>?

It is a brilliant plugin an thank you for all your hard work!

• This reply was modified 4 years, 9 months ago by colombo2019.

Hi Columbo,

Hard to say! Just a few places to look:
– Can you connect from your mysql prompt?
– Can you temporarily allow all IP’s? (just to check)
– If your mysql database connection secured?
– Are you connecting to the right port?

Please let me know if the problem remains! And how I can help.

Best regards,
Peter

Hello Peter,

At long last we have our database ready to connect to WordPress. I have started the connection process like this. I’ve configured rdb:interstellar_updates to connect to our database through Data Explorer. The problem is with the MySQL schema field. It has to have something, so I entered “information_schema,” the default MySQL schema name. Testing the connection gives this:

Preparing connection…
Establishing connection…
Connection established…
Counting tables…
Found 0 tables on remote host…

Remote database connection valid

I thought that might be the wrong schema name, so I changed it the schema setting to “interstellar_updates,” the other schema name I found in phpMyAdmin when connecting to our database. Testing that gave exactly the same result as above, but also removed the rdb:interstellar_updates entry from the database list. Any thoughts? Without the proper schema no table evidently can be found. I’m not enough of a DB admin to know where to go from here without some serious poking around, which I’m a bit loathe to do. I did look at the SCHEMATA entry in the information_schema database (still from within phpMyAdmin) and saw only the two schemas I mentioned above listed. Any advice you have will be greatly appreciated!

Hi Douglas,

Sounds like an authorization issue. Please do not connect to information_schema. Make sure you connect to interstellar_updates, presuming this schema contains the tables you want to use within the plugin.

Can you please connect from phpMyAdmin to schema interstellar_updates with the username and password you’re using in WordPress and execute this statement:
show grants;

I’m interested in the output. Please use the contactform on the plugin website (wpdataaccess.com) if you prefer to send me your output in private.

Thanks,
Peter

Hi Mr. Schulz,

I am in the same boat as Douglas was…almost exactly. Would it be possible to get guidance on my situation? I created a db in MySQL and would like to use it in the WP plugin.

I am receiving an identical message:
Preparing connection…
Establishing connection…
Connection established…
Counting tables…
Found 0 tables on remote host…

Any assistance would be greatly appreciated.

Kind regards,
Kary

Hi Kary,

In most cases this is a permission issue. I presume you want to keep permission as tight as possible, but to start it might be a good idea to open up to get your connection up and running first. This solved it for Douglas as well.

Suppose your remote username is user, please start with these credentials:
grant usage on *.* to ‘user’@’%’;

When this works, you can tighten up your credentials by adding the ip address of your server, like:
grant usage on *.* to ‘user’@’a.b.c.d’;

Where ‘a.b.c.d’ is the ip address of your MySQL server. Can you test your connection with the credentials as given above? Please let me know if this works.

Thanks,
Peter