connect via wpdb to another database :: how to combine two wp-pages - a approach that works

[dil_bert]

hello dear php-experts, good day, 

i want to connect two pages  on which a plugin like

1. participants_database or 
2. wp-job-manager runs 

i want to do that via wpdb to another database :: how to combine two wp-pages - here i want to discuss a approach that works  - see https://wordpress.stackexchange.com/questions/1604/using-wpdb-to-connect-to-a-separate-database

If they are all on the same server under the same hosting account,  that is helpful. In a case like that, you could write your custom code to access the other site’s database directly. This is discussed here, and it’s quite feasible:  link: Using wpdb to connect to a separate database

[/QUOTE]....

question: I want to connect wpdb to another database. How do I create the instance and pass it the database name/username/password?

Answer1: 134 votes  Yes it's possible. The wpdb object can be used to access any database and query any table. Absolutely no need to be Wordpress related, which is very interesting. The benefit is the ability to use all the wpdb classes and functions like get_results, etc so that there's no need to re-invent the wheel.  Here's how:

 

$mydb = new wpdb('username','password','database','localhost');

$rows = $mydb->get_results("select Name from my_table");

echo "<ul>";

foreach ($rows as $obj) :

   echo "<li>".$obj->Name."</li>";

endforeach;

echo "</ul>";[/CODE]

comment 1: you can also save time by using global $wpdb. But before firing ;[/CODE]$wpdb->get_results method,;[/CODE]

you must include wp-load.php as: [CODE]require_once('/your/wordpress/wp-load.php');[/CODE]

comment 2: Set WPDB prefix to make WP_Query and get_post to generate correct sql query by calling $mydb->set_prefix('wp_');

 

answer2: [ 30 votes ] Connecting to a second database is easy in WordPress, you simply create a new instance of the WPDB class and use it the same way you would use the standard $wpdb instance we all know and love.

Assuming the second database has the same login information as the main WP one you can even use the predefined constants

from wp-config.php to avoid hardcoding the login information.

[CODE]/**

 * Instantiate the wpdb class to connect to your second database, $database_name

 */

$second_db = new wpdb(DB_USER, DB_PASSWORD, $database_name, DB_HOST);

/**

 * Use the new database object just like you would use $wpdb

 */

$results = $second_db->get_results($your_query);[/CODE]

 

comment 1: This is somewhat redundant to Wadih's answer but I think my code example is a bit clearer and its also important to remember the db login constant's as they are almost always the right ones to use and otherwise you risk issues when moving from dev->stage->live environments where the login details might change. – jerclarke Sep 11 '10 at 20:55

comment 2: Set WPDB prefix to make WP_Query and get_post to generate correct sql query by calling [CODE]$second_db->set_prefix('wp_'); [/CODE]

 

answer3: [ 21 votes ] no one has said this so I thought I'd add an even easier way..as long as your additional database has the same user/pass details to access it as your wordpress database you can use the database name before the table name like this

[CODE]$query = $wpdb->prepare('SELECT * FROM dbname.dbtable WHERE 1');

$result = $wpdb->get_results($query);[/CODE]

comment 1: From my experience, this only works to get data, i.e. using SELECT. You can't insert data.

comment 2: it will not work externally,

 

answer4: [ 7 votes ] I can't comment yet, but I wanted to expand on Wadih M.'s answer (which is great). WP's database class is a customized version of Justin Vincent's ezSQL. If you like the interface and you're wanting to do a site that's  not WordPress-based, you might want to check it out: http://justinvincent.com/ezsql

answer5: [ 6 votes ] While these will work, you'll lose the ability to use the "other" custom features such as get_post_custom and wordpress queries. The simple solution is [CODE] $wpdb->select('database_name');;[/CODE]

which changes the database system-wide (a mysql select_db). The database.table method works if you just want to make a simple query,  but if you want to access another wordpress blog you can use select. You'll just need to change it back when you're done or your blog may do strange things.

comment: I'm using this solution and it works great, except for one thing. For some unknown reason wp_get_post_terms() doesn't seem to use the newly selected DB?? Every other function I've tried (like [CODE]get_post_meta(), get_posts() etc);[/CODE] seems to work just fine but ;[/CODE]wp_get_post_terms() ;[/CODE]seems to work towards the ;[CODE]DB_NAME database;[/CODE]. Any ideas?

I was struggling with ;[CODE]using $wpdb ;[/CODE]to connect to a second blog database from a parent site that needs to update two blogs. I used ;[CODE]$wpdb->select($dbname, $dbh);[/CODE] to select the second database, but I was still getting results from the first database.

comment: I resolved the problem by calling wp_cache_flush() to clear the WordPress cache before calling WP functions on the second database.

 

Conclusio: well i like this approach very very much.  The good thing - i am with the sites in question - i am on the same server - so i guess that this will work for me.

 

any idea any experience !? 

love to hear from you

 

 

[Barand]

You only need two connections if the databases you want to access are on separate servers. When you make a connection you connect to a server, the database specified is just the default database.

Suppose you have a server with two databases

When you connect you specify DB1

$conn = mysqli_connect(HOST, USER, PWD, 'DB1');

Now when you want to query tableA you would have

$sql = "SELECT cola, colb FROM TableA"

This works fine because TableA is in DB1, the default. If, however you want to select something from TableB then all you need to do is specify that it is in DB2

$sql = "SELECT cola, colb FROM DB2.TableB";

You can even mix them in the same query

$sql = "SELECT a.cola
             , b.colb
        FROM tableA as a
             INNER JOIN
             DB2.TableB as b USING (cola)
        ";

To summarize - to access multiple databases on the same server prefix the table names with the database names.

[dil_bert]

hello dear Barand,

 

as usual  - your answers are very very helpful and supportive. Many thanks for all!! 

 

convinced:   If the sites are all on the same server then we just cam connect to the same database from each site, that's all there is to it.  If the sites are on different servers then we would need to build an API, but  - if they're (luckily) on the same server then just connect to whatever database we want to connect to.  There are no rules. We can connect to a database - and yes - we also can use a db object to connect to more than only one db. PHP doesn't care how many database connections we have. 

 

Many thanks Barand for your help. i am convinced. 

 

have a great day. 

 

 

[Barand]

1 hour ago, dil_bert said:

If the sites are on different servers then we would need to build an API

Not necessarily. The sites can be spread all over the world. It is the location of the databases that is significant here. If the databases are on the same database server then only a single connection would be required. As I said in my earlier reply, the connection is to the server, not to the database.