Custom Query (8 posts)

  1. Scott
    Posted 2 years ago #

    I am not a programmer and I am probably missing something really obvious. I have custom tags installed and setup for my groups. I want to run a query that displays the total count of how many groups has a certain tag.

    I have been trying something like this. Any ideas?

    function TagCount() {
    global $wpdb, $bp;
    $var_count = $wpdb->get_var("SELECT count( * ) FROM $bp->groups_groupmeta WHERE meta_key = 'gtags_group_tags' AND meta_value = '%MyTag%';");
    echo 'count is ' . $var_count . '';
    add_shortcode('tagcount', 'TagCount');
  2. bellasys
    Posted 2 years ago #

    $var_count = $wpdb->get_var( $wpdb->prepare( "SELECT count( * ) FROM $wpdb->groups_groupmeta WHERE meta_key = 'gtags_group_tags' AND meta_value = %s,
    $My_Tags" ) );

    A couple comments on this:
    My style when answering is to change as few things as possible, even where completeness or thoroughness may dictate otherwise. You could substitute other elements in your query str with vars as well, but probably no need.
    Set $My_Tags before entering this query appropriately.

    Another comment- I'm not sure if you mean by the global $bp that you are referencing another Database? If so, you aren't going anywhere until that is handled. Assuming you are accessing the SAME db as your wp then $wpdb->(table_name) will work, where table_name does not have the leading parts that are common to all the wp tables. This varies per server and setup. Mine is just wp_(table_name) but other fully qualified table names are wp_db2371_users for example, so you'd put $wpdb->users ordinarily...

    A final comment- the reason I'm on this forum today and hopefully this will shed some light moving forward:

    I am able to "get away" with lots of stuff on my favorite servers, in fact I just do this:

    $My_var = "blah";
    $querystr_k = "SELECT * FROM wp_cde WHERE meta_col = 'someval' AND meta_col2 = $My_var";
    $wpdb->get_results($querystr_k, ARRAY_A);

    But then on others it's a federal issue to declare an open variable- it makes me single quote the column names, or single quote the var ($My_var) inside the double quotes (huh? why ever would that make sense)). Some servers I can't even declare my table name unless it's in a var name, and then it has to either be in quotes itself- or not.

    The moral of this is I am seeming to have to do an unusual amount of trial and error with each new server/PHP/MySQL setup (and all of them are 5+ btw) I encounter. I know that it depends whether I'm calling the custom query from inside a Class, or it's object instantiated on a page, or a template page, etc. So of course the context in page matters, but nevertheless it seems voodoo to me at this point how to avoid the trial and error...

    I have been all over helpful sites like WP Engineer which is of rudimentary help, and of course WordPress Answers which offers typically more contextual help.

    Still I find that there aren't really cut & dried all purpose solutions which always work- sometimes the code in the codex works out of the box, sometimes it doesn't. There are only the basic principles, and then learning to apply them in context in your theme/plugin/whatever.

  3. bellasys
    Posted 2 years ago #

    Oh- I mentioned the official Codex but did not link its resource:

    WPDB Class Reference

    Again, I have had varied experience with those recommendations working, but it is certainly one piece of the puzzle :)

  4. bellasys
    Posted 2 years ago #

    Ok my post is too old to edit. I forgot you're using a custom table so you may need to configure

    $my_table = $wpdb->prefix . 'groups_groupmeta';

    for use in your query.

  5. Scott
    Posted 2 years ago #

    Thank you! Those are great websites that I hadn't seen yet. Someone was able to help me out a little bit but I cleaned up my code some using your tips. This is my result.

    add_shortcode('factioncount', 'FactionCount');
    function FactionCount() {
    global $wpdb;
    //my table
    $table = $wpdb->prefix . "bp_groups_groupmeta";
    //query for my tags
    $ald_tags = $wpdb->get_results("SELECT * FROM {$table} WHERE meta_key = 'gtags_group_tags' AND meta_value LIKE '%aldmeri-dominion%'");
    $ald_tags = (array_filter($ald_tags));
    $ald_tags = count(array_keys($ald_tags));
    return $ald_tags;
  6. bellasys
    Posted 2 years ago #

    Hey Scott,

    XLNT. Glad u got it working, and seeing your final code makes this a complete experience.

    Since code like this has not worked in my setups- can you share the environment it is working in? Just the basic sketch, like your server type/OS MySql/PHP versions etc. Nothing says you need to do this, but I wonder if this would be helpful for others to compare setups.

  7. Scott
    Posted 2 years ago #

    Oh no problem.

    CENTOS 5.9 x86_64
    Apache 2.2.23
    MySQL 5.0.96-community
    PHP 5.3.21

  8. bellasys
    Posted 2 years ago #

    CENTOS 6.1 x86_64
    Apache 2.2.23
    PHP version 5.2.17
    MySQL version 5.5.30-log

Topic Closed

This topic has been closed to new replies.

About this Topic