[Resolved] Want to bring in simple data from external DB using WPDB Class

[Author]

Posts

[nicoleL]

I am trying to: Thinking about renewing my license of Toolset. It is very important that I am able to bring in some simple data from an external DB. My question is will Views allow me to utilize WPDB Class, like:

/**
 * Instantiate the wpdb class to connect to your second database, $database_name
 */
$second_db = new wpdb(DB_USER, DB_PASSWORD, $database_name, DB_HOST);
/**

Now will I be able to use Views and Views Shortcodes against $second_db ?

Even if I can't do this directly, can I somehow do a MYSQL query and then use that value in Views and Views Templates?

Also something to note, above I'm referring to an external DB.. I edited this to add the fact that I can also NOT do an external DB, I can have another table IN the same db that the wordpress install is on.. So I'm hoping even in this scenario, Views can access that Table?

I visited this URL:

I expected to see:

Instead, I got:

[Nigel]

Hi Nicole

Neither of your scenarios work, I'm afraid.

Views only has access to standard WordPress tables, to the posts and postmeta tables, the users and usermeta tables, and the taxonomy tables.

It can't access data in tables in 3rd party plugins (although it can access data stored by 3rd party plugins in the post meta, for example).

I'm not sure what you are aiming to do but you might be able to combine the Views API with the data in your external DB.

If, for example, you wanted to filter a View using some value that came from the external DB, then you could use the wpv_filter_query filter to run some code that retrieved the required value and modified the query accordingly.

See https://toolset.com/documentation/user-guides/views-filters/wpv_filter_query/

[nicoleL]

Ok, so ultimately I have:

website.com - (wordpress, all informational website)

management.website.com - (wordpress, Used to house 'values'. I will have numerous authenticated only users sign in here and manage their 'values'. )

individual_website.com - (wordpress, These are individual sites, they have their own visual 'designs' while having 'values' coming from management.website.com )

So the reason for the above, obviously the informational only site is self explanatory. The second site is because I want ONE location that ONLY myself, my managers, and the individual site owners visit and manage just 'values'.
And the individual sites are just that, individual sites that have all of their individual visual design, AND they all pull in values that are stored at management.website.com.

The reason for this is, for example if there are 50 individual sites, myself and managers can manage all of the 'values' for all 50 right at one simple wp install, which is management.website.com.

All individual sites have their own videos, stored in Amazon S3 buckets. I can simply access the urls for these buckets at ONE location, instead of having to login / out of 50 different wp sites to make changes!

[Nigel]

Hi Nicole

I'm not sure how much help Toolset can be here, apart from as a tool used to build the individual sites.

Bridging the different sites will require entirely custom code. There are doubtless a number of ways you might approach that. One, as I alluded to earlier, is to filter the relevant queries so that where necessary you retrieve your 'values' from the management site rather than the local site.

An alternative would be to duplicate the values added to the management site in the local sites so that they are stored much like any other custom field, for example, and in which case they would be available to Views as normal.

For the former option, by way of an example, looking at the Views source code for the wpv-post-field shortcode (used to output a field value in a content template, for example), the custom field is passed through a filter after it has been retrieved from wp_postmeta, giving you an opportunity to intercept it and replace it with a value from another database.

Here are the relevant lines:

		$meta = get_post_meta($post->ID, $name);

		$meta = apply_filters('wpv-post-field-meta-' . $name, $meta);

So for a Types custom field of 'video-url' you could add a filter 'wpv-post-field-meta-wpcf-video-url' that replaces the value from the local site with one from an external site.

So the means exists to create something like you describe, but you will need to be able to write the code to do so yourself.

[nicoleL]

First and foremost thanks Nigel for taking the time to even entertain my questions that are wayyyy outside the scope of Toolset! You've definitely given me enough insight to, if nothing more, utilize google search + WP codex with the right terms to search and study.

You've mentioned wpv_filter_query numerous times, so I'm definitely gonna read up on that now.
But I'm thinking everything dealing with views is within a wp loop? Shouldn't i concentrate on Types API? That way the data, should I get it working, is already accessible to Views as normal.

I think the safest and probably most efficient way is to utilize WP's API for the overall connection between the different installs. I have a developer who is amazing with PHP and API coding.

And after having that secure connection in place, and passing data I can then figure out how to use custom fields + wpv_filter_query. I really wanna use Toolset and Views because if I can get this working, I can use the module manager and very easily manage the functionality and just drop it in on all of the individual_website.com sites.

Anyways, thank you thank you thank you for sharing the knowledge!