Why does get_posts() go to wrong database?

Well, by default specifying the database is done in each install’s wp-config.php.

So, this probably seems like a silly question, but does each blog have a different table_prefix defined in wp_config.php?

Actually, no. Since there are two separate databases, I don’t see how that could matter. But I will go ahead and give it a shot. I’ve tried everything else…

well good point. If they’re truly two sep db’s, it won’t help. However, if they’re sharing a database, then perhaps it’ll matter.

DB_Name is different for each?

I prefixed the username_wp12 database tables with wp12 by making this change in wp-config:

$table_prefix = 'wp12_'

Now I’m getting these kind of errors from the loop:

WordPress database error: [Table ‘username_wp11.wp12_posts’ doesn’t exist]
SELECT COUNT(DISTINCT ID) FROM wth_posts WHERE 1=1 AND post_date_gmt <= ‘2006-06-14 13:57:59’ AND (post_status = “publish” OR post_author = 1 AND post_status != ‘draft’ AND post_status != ‘static’) AND post_status != “attachment”

So it still wants to go after the wrong database.

Interestingly, get_posts() appears to be working.

I think the issue may have something to do with another script I use in the page. I open a connection to wp11 and select some things (that I could not do with a template tag). The script connecta to the database, selects the wp11 databse, and runs a query. Then, later in that page, I try to use get_posts(), expecting it to use the database for the current blog which is stored in wp12. My guess is that whatever is behind get_posts does NOT select the database f1rst, and therefore since I last selected wp11, it by default uses wp11.

That might explain the errors. The question remains, however: If I am making my own calls to one database, and expecting wp to use another database – in the same page, how do I ensure that the db1 databse is selected, the conx gets closed and the other db is selcted? Shoud I modify the $get_posts function?

My guess is that whatever is behind get_posts does NOT select the database f1rst, and therefore since I last selected wp11, it by default uses wp11.

get_posts(), like everything else, uses the global $wpdb variable to connect to the database and run queries and such. If you don’t screw around with $wpdb, then you shouldn’t have any problems.

Perhaps that is precisely the problem: the $wpdb variable needs to be reset after a user script is run against a different database.

Template tags used in pages that belong to a particular blog do not reselect that blog’s database on each use.

Therefore, since I’m running scripts in one blog’s page that select data from another blog’s database, the $wpdb variable needs to be reset after running these scripts.

Then all my template tags will work perfectly and we’ll have world peace and no more baby seals will die.

Please. Think of the baby seals.

Perhaps that is precisely the problem: the $wpdb variable needs to be reset after a user script is run against a different database.

Depends on the user script. If you’re modifying the $wpdb variable in your script, then yes, clearly you should put it back the way it was before you’re done.

If you’re connecting directly and not using WP functions to do it, then no, it shouldn’t modify $wpdb and thus won’t break anything.

If you’re connecting directly and not using WP functions to do it, then no, it shouldn’t modify $wpdb and thus won’t break anything.

Or so I thought. I am, in fact, connecting directly:

mysql_select_db('wp11');
$sql = "select cat_id, cat_name ..."
$result = mysql_query($sql);
...my code here...

This script is called in header.php in blog12 (which uses database wp12):

<?php include $myscript.php ?>

The inclusion of this script breaks ALL template tags (not just get_posts) that appear after the inclusion of the script.

(The Loop does not appear to be encumbered.)

As a work-around, I insert this code immediatley after the inclusion of the script:

$db = mysql_connect('localhost','username','password');
if ($db) { mysql_select_db('wp12'); mysql_close; }

This is a hack, to be sure, and I’m still trying to figure out what’s going on and the best way to resolve it. Suggestions welcome.

If you’re wondering why I’m calling a script run against one blog’s database while in a page of another, the purpose is this: blog11 is like a “front page” that contains several categores. Some of these “categories” are not wp categories per se, but rather separate blogs in and of themselves (it’s all transparent to the user).

For example, when a user clicks on a link to “Category X” (in the “front page” blog11), he may be taken to the posts in blog11 that are in that category, or he may be taken to a separate blog, such as blog12 in the above example.

This configuration has two key advantages: it makes it easy to segregate stuff so I can break off a database into it’s own site if I want, and it reduces database size.

I tried using this:

$wpdb->flush();

instead of code to re-select database wp12

no luck.

Or so I thought. I am, in fact, connecting directly:

mysql_select_db(‘wp11’);
…

That’s not a connection, that’s a select. You are indirectly modifying $wpdb by doing that.

From here: https://us2.php.net/mysql_select_db

bool mysql_select_db ( string database_name [, resource link_identifier] )
…
link_identifier
The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect() is assumed.

Use mysql_connect() to make a new connection and then use that connection instead of letting it use your existing one.

Alternatively, create a new wpdb object for your own use, like so:
$yourwpdb = new wpdb(DB_USER,DB_PASSWORD,"wp12",DB_HOST);
then use $yourwpdb->query(sql_goes_here) to run a query, and $yourwpdb->get_row(…) to get rows and such… Bit simpler than rolling your own mysql code.

I didn’t include the code that made the connection. Here’s a less abbreviated version:

$db = mysql_connect(...);
if ($db)
mysql_select_db('wp11');
$sql = "select ...";
$result = mysql_query($sql);
while ($var = mysql_fetch_array($result))
...

So I am making my own connection.

Apparently template tags do not specify a link and, as the manual points out, use the last link opened by mysql_connect (which is the one my script makes to wp11). It is clear now that this is the case. So I need to select wp12 (or whatever) before my script concludes.

What makes this cumbersome is that the script uses categories retrieved from wp11 to create a navbar that is common to all blogs. So I never know which db needs to be switched back to. If I use that work-around I posted, that means establishing another connection just to select the right db.

Perhaps I can find a way to determine what the current link is at the start of my script, then just switch back to it before I close that connection.

developing…

Apparently template tags do not specify a link…

That really doesn’t make a whole lot of sense, but I’ll roll with it.

Template tags usually end up making a query via $wpdb. I traced some of it through and found this in wpdb->query():
mysql_query($query, $this->dbh);

So those requests *are* specifying their database handles. I think your select is simply modifying the wrong handle, somehow.

Try changing your code to this:
...
mysql_select_db('wp11', $db);
...
mysql_query($sql, $db);
...

Thanks for the tip, I’ll give it a shot.

I was able to id the link with this code:

$cnx = mysql_connect('localhost','username','password');
if ($cnx)
{
$link = mysql_list_dbs($cnx);
while ($objdb = mysql_fetch_object($link))
{
$db = $objdb->Database;
}
mysql_select_db('wp11');
...
}
mysql_select_db($db); //back to orig link
mysql_close;

but if $wbdb is specifying the link, something else needs to be adjusted.

still developing…