New to the community? Start here

Prefix with database name

If you made a query on db_global.tableXXX with subquery on wp_2_posts (for example ) that doesn’t work.

So is there a way to add a function wich prefix db name in the query ?

Or have separated function wich return database name with blogid ?

Mon, 21 Jan 2008 10:00:11

Luke
- The Crimson Coder
- 3 pts: Hero points 638 pts: Rep. points LEVEL 6
Perhaps it would be better if you explained what you are trying to accomplish, and provide some query examples?

I can’t honestly see where a situation would come up where you need some information like that, that isn’t already available in the global scope in one form or another. Not that it couldn’t be possible, but a query like you are trying to show by example doesn’t seem exactly efficient.
ynave
- New Recruit
- 0 pts: Hero points 9 pts: Rep. points LEVEL 0
For example, I use simpletag plugin for each blog.

And I need to make a cloud of all blogs tag on the homepage of the first blog.

(Sorry for all code tag but I put backticks on my field name )

So in db_global I create :
```
CREATE TABLE wp_tag_global

(

<code>blog_id</code> int(11),

<code>tag_name</code> varchar(255),

<code>tag_slug</code> varchar(255),

<code>count</code> int(11),

PRIMARY KEY(<code>blog_id</code>,<code>tag_slug</code>)

);
```
and when post is publish I delete all tag for the blogid, and

insert new like that :
```
INSERT INTO <code>{$this->table}<code>(</code>blog_id<code>,</code>tag_name<code>,</code>tag_slug<code>,</code>count
```
)

SELECT {$wpdb->blogid} AS blog_id, t.name, t.slug, tt.count

FROM {$wpdb->terms} AS t

INNER JOIN {$wpdb->term_taxonomy} AS tt ON t.term_id = tt.term_id

WHERE tt.taxonomy = ‘post_tag’ AND tt.count > 0
```
But without table prefixed by database name => that doesn't work.

So for now I'm making two query  so it's less efficient :
```
// Get existing global tag for this blog

$query= “

SELECT {$wpdb->blogid} AS blog_id<code>, t.name, t.slug, tt.count`

FROM {$wpdb->terms} AS t

INNER JOIN {$wpdb->term_taxonomy} AS tt ON t.term_id = tt.term_id

WHERE tt.taxonomy = ‘post_tag’ AND tt.count > 0″;

$existings= $wpdb->get_results($query, ARRAY_A) or die(mysql_error());

// Insert existing global tag for this blog

if(is_array($existings)){

foreach($existings as $existing){

$query= “

INSERT INTO {$this->table} (blog_id,tag_name,tag_slug,count) VALUES

(‘{$existing}’,'{$existing}’,'{$existing}’,'{$existing}’:wink:”;

$wpdb->query($query) or die(mysql_error());

}

}

`

Yes I know delete all and insert again isn’t very efficient, but it’s only made when there is post edition or tag deleted. I use add_action to accomplish that. Yes there is action when tag is deleted but there is no way to know what tag is deleted unless to change some code in taxonomy.php so I don’t want.

So I think this way is better for efficient / clean code.

And prefixed table name with database name is the “standard” way to access table in other database ( If user has rights ! )
Luke
- The Crimson Coder
- 3 pts: Hero points 638 pts: Rep. points LEVEL 6
Here is where your logic fails, unfortunately.

a) the username/password has to be the same across all databases (bad juju).

b) all databases have to be on the same server (not always the case).

With the above being the case, IMHO, it would be bad form/practice to try an support such a minority where this may be a problem.

Honestly, I’m running a global tag system without any of these problems at all. Granted, it’s purely custom code and not anything close to a publicly available solution, but it works just fine without such queries.

Even from your example above, I still can’t see a need for a join on a global and a user table. Granted, I’m not going to rewrite your system for you here for free, but there are additional options that you may not have thought out/considered.

If it really is the way you feel you need to go, you could simply modify the plugin for your particular situation. However, overall, it wouldn’t be something I’d personally vote for/suggest for a plugin with such a wide audience.
ynave
- New Recruit
- 0 pts: Hero points 9 pts: Rep. points LEVEL 0
Ok so sorry for you if you can’t see the need.

I didn’t ask you to rewrite my system and yes I can do it myself, but I think about having easy updates too :wink:.

It was just a suggestion to move the code wich get the database name to a method in wp-db, just think it will be cleaner and helpful and not a big rewrite.

I don’t think it’s very particular to have only one database server for a medium wordpress mu plateform.

So perhaps it’s no good to have same user for multiple database for security… but for a same project it seems more clean. (IMHO).

And now I’m proud to have your mind for my “failed” logic :wink:
Andrew
- Champion of Loops
- 2,117 pts: Hero points 5,319 pts: Rep. points LEVEL 23
There’s actually no need to add a function to get the db name for a particular blog. If you have the blog id then you can grab the db name from the db array.

Thanks,

Andrew
Luke
- The Crimson Coder
- 3 pts: Hero points 638 pts: Rep. points LEVEL 6
No, I don’t see a need.

Well, other than poorly written code and poorly structured tables which require such a thing.
ynave
- New Recruit
- 0 pts: Hero points 9 pts: Rep. points LEVEL 0
There’s actually no need to add a function to get the db name for a particular blog. If you have the blog id then you can grab the db name from the db array.

Thanks,

Andrew

And how ?

No, I don’t see a need.

Well, other than poorly written code and poorly structured tables which require such a thing.

Hey GOD ! Happy for you having many server for many database. Enjoy with it ! Respect to you expert of poorly written code.
Andrew
- Champion of Loops
- 2,117 pts: Hero points 5,319 pts: Rep. points LEVEL 23
There’s already an example inside the custom wpdb.php. Look for the bit that decides which db to connect to. I’m away from my desk at the moment but i’ll look up line numbers tomorrow.

Thanks,

Andrew

Ready to get started?

How do you rate me?

Thank you for rating your experience!