I want to connect wpdb to another database. How do I create the instance and pass it the database name/username/password?
Thanks
-
Another MySQL database, or another database type? Do you still want access to the regular WordPress database, or are you moving the site from one DB to another?EAMann– EAMann2010-09-10 03:39:00 +00:00Commented Sep 10, 2010 at 3:39
-
1Yes, another MySQL database. It's a separate DB on the same server, and it's not a Wordpress one. It's a custom db, with information I want to display inside wordpress.Wadih M.– Wadih M.2010-09-10 03:41:20 +00:00Commented Sep 10, 2010 at 3:41
-
1If you did that with the $wpdb object, if it were even possible, it would disconnect the rest of WordPress from its existing database. So, not recommended. Another option is to create a new instance using EZSQL, which is used by WordPress. I think EZSQL is used because it's a layer that abstracts you from having to use php-pdo-mysql, php-mysql, or php-mysqli, not knowing which might be installed on a given server.Volomike– Volomike2010-09-10 05:18:58 +00:00Commented Sep 10, 2010 at 5:18
-
3Yes it's possible. wpdb can be instantiated to access any database and query any table.Wadih M.– Wadih M.2010-09-10 12:43:21 +00:00Commented Sep 10, 2010 at 12:43
Add a comment
|
6 Answers
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>";
}
echo "</ul>";
-
5Booyah. Too bad all those comments added up on the question itself to block your accurate answer.jerclarke– jerclarke2010-09-11 20:46:41 +00:00Commented Sep 11, 2010 at 20:46
-
@Jeremy Clarke: I agree. Hoping our fellow wordpressers will be more careful to not innocently spread out disinformation.Wadih M.– Wadih M.2010-09-20 16:27:06 +00:00Commented Sep 20, 2010 at 16:27
-
1you can also save time by using
global $wpdb. But before firing $wpdb->get_results method, you must include wp-load.php as:require_once('/your/wordpress/wp-load.php');Junior Mayhe– Junior Mayhe2015-09-19 22:25:12 +00:00Commented Sep 19, 2015 at 22:25 -
Set WPDB prefix to make WP_Query and get_post to generate correct sql query by calling
$mydb->set_prefix('wp_');M-R– M-R2015-10-13 10:58:07 +00:00Commented Oct 13, 2015 at 10:58 -
1I know this is an old thread, but I can't help but feel squashing the
$mydbvariable with a new object could leave a connection open (I could be wrong). I would check to see if$mydbis already instantiated from a previous call, and if so, close the connection before spinning up a new instance. eg (sorry can't do neat Markdown code blocks in the comments):if ($mydb != null) { $mydb->close(); }joehanna– joehanna2020-01-21 01:35:30 +00:00Commented Jan 21, 2020 at 1:35
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.
/**
* 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);
-
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– jerclarke2010-09-11 20:55:02 +00:00Commented Sep 11, 2010 at 20:55
-
Set WPDB prefix to make WP_Query and get_post to generate correct sql query by calling
$second_db->set_prefix('wp_');M-R– M-R2015-10-13 10:58:53 +00:00Commented Oct 13, 2015 at 10:58 -
What will happen to wordpress database and its funtions if I connect like so? I dont want wordpress side to fail while I am doing so. Also feels wrong to be connecting to "database external to wordpress from wordpress".Toniq– Toniq2025-09-07 18:15:28 +00:00Commented Sep 7 at 18:15
-
@Toniq It won't affect the main WP database, which is safely stored in
global $wpdb. As long as you give the second DB object a different name and store it in a different place, the main DB and core queries will be unaffected.jerclarke– jerclarke2025-09-12 16:01:17 +00:00Commented Sep 12 at 16:01 -
@Toniq In terms of it being weird to connect to an external DB: Yes it's unusual and it should be avoided, always better to store things in the main WP database when possible. Personally I use it to share tables between a set of sites on the same server but with separate WP installs. Alternately I could have built it as a WP Multisite, but that comes with a lot of other downsides and complications.jerclarke– jerclarke2025-09-12 16:02:35 +00:00Commented Sep 12 at 16:02
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
$query = $wpdb->prepare('SELECT * FROM dbname.dbtable WHERE 1');
$result = $wpdb->get_results($query);
-
From my experience, this only works to get data, i.e. using
SELECT. You can't insert data.Protector one– Protector one2015-06-28 15:06:51 +00:00Commented Jun 28, 2015 at 15:06 -
it will not work externally,Wasim A.– Wasim A.2019-01-31 13:57:21 +00:00Commented Jan 31, 2019 at 13:57
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
$wpdb->select('database_name');
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.
-
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 (likeget_post_meta(),get_posts()etc) seems to work just fine butwp_get_post_terms()seems to work towards theDB_NAMEdatabase. Any ideas?powerbuoy– powerbuoy2013-07-09 02:54:58 +00:00Commented Jul 9, 2013 at 2:54
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
-
ezSQL was really frustrating for me, coming from WPDB. No "prepare" statements, no "insert" or "update"... I like to use the entire WPDB class as it exists, which is possible by including a couple files out of BackPress in your project.goldenapples– goldenapples2011-04-22 19:23:30 +00:00Commented Apr 22, 2011 at 19:23
-
@gabrielk The link is dead - new one is: [1] [1]: justinvincent.com/ezsqlHexodus– Hexodus2013-11-23 08:20:16 +00:00Commented Nov 23, 2013 at 8:20
I was struggling with using $wpdb to connect to a second blog database from a parent site that needs to update two blogs. I used $wpdb->select($dbname, $dbh) to select the second database, but I was still getting results from the first database.
I resolved the problem by calling wp_cache_flush() to clear the WordPress cache before calling WP functions on the second database.
