9 pointsGetting my WPMU DEV WingsI'm new here
ynave
Member
—
21st January 2008 (4 years ago)
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 ?
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 ?
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.
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 )
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['blog_id']}','{$existing['name']}','{$existing['slug']}','{$existing['count']}')";
$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 ! )
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.
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 ;).
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 ;)
7430 pointsLike some sort of WPMU DEV GodMindblowingly helpful memberLifetime member
Erstwhile founder
—
22nd January 2008 (4 years ago)
#
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.
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.
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.
7430 pointsLike some sort of WPMU DEV GodMindblowingly helpful memberLifetime member
Erstwhile founder
—
23rd January 2008 (4 years ago)
#
And how ?
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.
Responses (8)
Member — 21st January 2008 (4 years ago) #
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.
Member — 22nd January 2008 (4 years ago) #
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 :
and when post is publish I delete all tag for the blogid, and
insert new like that :
)
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
// 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 tINNER JOIN
{$wpdb->term_taxonomy}AS tt ON t.term_id = tt.term_idWHERE 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['blog_id']}','{$existing['name']}','{$existing['slug']}','{$existing['count']}')";
$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 ! )
Member — 22nd January 2008 (4 years ago) #
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.
Member — 22nd January 2008 (4 years ago) #
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 ;).
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 ;)
Erstwhile founder — 22nd January 2008 (4 years ago) #
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.
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
Member — 22nd January 2008 (4 years ago) #
No, I don't see a need.
Well, other than poorly written code and poorly structured tables which require such a thing.
Member — 22nd January 2008 (4 years ago) #
And how ?
Hey GOD ! Happy for you having many server for many database. Enjoy with it ! Respect to you expert of poorly written code.
Erstwhile founder — 23rd January 2008 (4 years ago) #
And how ?
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
Become a member