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 ?

  • Luke

    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

    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['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 ! )

  • Luke

    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

    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

  • ynave

    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.