Wordpress Categories: Function using custom SQL to return array of specific category IDs

Question

I'm developing a custom theme which uses categories for positioning, all of which begin with an underscore (e.g. _position1). I have an SQL query as below to get a list of these categories (those starting with an underscore).

SELECT name,term_id FROM `wp_terms`
WHERE name LIKE '\_%';

How would I go about turning this into a function which returns a list of the category IDs?

The idea being that this list of IDs can be passed to wp_list_categories() to be excluded e.g. (where get_positional_ids would use the SQL above to return a list of IDs)

<?php 
$excludeids = get_positional_ids();
$args = array('exclude'=> $excludeids);
wp_list_categories($args); 
?>

Any ideas on how to do this? Thanks.

Answer 1

If you use the built in get_terms function you end up with a quick one liner:

function get_positional_ids(){
    return get_terms( 'category', array('fields' => 'ids', 'name__like' => '_'));
}

no custom SQL, safe and simple.

Answer 2

A better idea would be to create a custom taxonomy as 'Positions', and put all your position values under this taxonomy. It will be easier to handle this than to write custom SQL. But if you still want to go with the SQL way, try this...

<?php
function get_positional_ids()
{
    global $wpdb;
    $pos_query = SELECT term_id FROM `wp_terms` WHERE name LIKE '\_%';

    //This will return an array of objects with the values returned by the query
    $pos_id_objs = $wpdb->get_results($pos_query);
    $pos_ids = array();

    //we loop through the array of objects and form an array of ids we want to return
    foreach($pos_id_objs as $pos_id_obj)
    {
        $pos_ids[] = $pos_id_obj->term_id;
    }

    return $pos_ids;
}

Put this in functions.php Haven't tested, but should work. Check out the doc for $wpdb class.