Recent Topics

SQL and Tag questions

Started by on Jan 19, 2010 – Contents updated: Jan 19, 2010

Jan 19, 2010 18:16    

Version: 3.3.2

I have two sort-of related questions, in that they are both popping up on a plugin I've coded. This plugin is designed to display all the tags on a given blog in the back office (in Blog Settings -> Plugin Settings, which is not my favorite place to put it but works for now). It does this just fine, and I'm in the process of refining it now, adding links to all the posts for a given tag, adding a summary, etc.

My questions are:
1. Is there a way to display all untagged posts? I tried taking the tag name out of the URL, and that just displays all posts.

2. How can I get to the variable containing the table prefix? I assume it's out there somewhere, as it's in the config file and I was able to find other data in the config file (host name, database name, etc) under $DB, but I can't track down the table prefix.

Jan 19, 2010 20:54

1. Is there a way to display all untagged posts? I tried taking the tag name out of the URL, and that just displays all posts.

PHP

global $DB;
$items_with_tags $DB->get_col('SELECT itag_itm_ID FROM T_items__itemtag');
 
$items_without_tags $DB->get_col('SELECT post_ID FROM T_items__item WHERE post_ID NOT IN ( '.implode','$items_with_tags ).' )');

Once you get item IDs you can use ItemList and set_filters

PHP

$ItemList->set_filtersarray(
    'post_ID_list' => implode','$items_without_tags ),
) );

2. How can I get to the variable containing the table prefix? I assume it's out there somewhere, as it's in the config file and I was able to find other data in the config file (host name, database name, etc) under $DB, but I can't track down the table prefix.

Use T_ prefix in your queries, it will be automatically replaced with the real tables prefix

PHP

$DB->query('SELECT * FROM T_blogs');

Jan 19, 2010 20:59

The actual code will be

PHP

global $DB$Blog;
    
    $items_with_tags $DB->get_col('SELECT itag_itm_ID FROM T_items__itemtag');
    
    $items_without_tags $DB->get_col('SELECT post_ID FROM T_items__item WHERE post_ID NOT IN ( '.implode','$items_with_tags ).' )');
 
    // Create empty List:
    $ItemList = & new ItemList2$BlogNULLNULL );
 
    // Filter list:
    $ItemList->set_filtersarray(
            'post_ID_list' => implode','$items_without_tags ),
        ) );
 
    // Get ready for display (runs the query):
    $ItemList->display_init();
 
    if( $ItemList->result_num_rows )
    {
        while( $Item = & $ItemList->get_item() )
        {
            echo $Item->title.'<br />';
        }
    }

Jan 22, 2010 17:15

Ah-ha. And thank you for the impromptu lesson on how to use the built-in b2evo SQL functions, which was my next bit of work. :)


Form is loading...

Run your own website! – This forum is powered by b2evolution CMS, a complete engine for your website.