Create a custom plugin with dynamic child pages listing database records

Question

I am developing a simple lean and mean custom plugin which has the purpose to list records from some database tables. Goal is to list data via url /teams/{category}/{id}.

I've created a basic plugin and a "teams" page and added a short code to fetch a list of teams:

// upcoming events shortcode
function test_teams_overview() {
    $output = '';
    global $wpdb;
    $teams = $wpdb->get_results('SELECT * FROM Teams');
    if(count($teams)){
        $output .= '<ul>';
        foreach($teams as $team){
            $output .= '<li><a href="/' . $team->url . '">' . $team->team . '</a></li>';
        }
        $output .= '</ul>';
    }
    else{
        $output .= '<p>No teams :( </p>';
    }
    return $output;
}
add_shortcode('teams', 'test_teams_overview');

This works, but I am stuck on the next part, which action/hook to use to list dynamic subpages. Clicking in the above example leads to an URL like /teams/alpha. By adding an custom hook I can get control over the URI requested, however I can't get the output "injected" in the template/page (gives a 404), this is what I have:

add_action( 'init', 'teams_custom_router' );
function teams_custom_router( $query ) {
    global $wp_query;
    exit(print_r($wp_query,1)); 
}

The teams_custom_router() can't help me any further as $wp_query remains empty. I can't get it to work to inject a custom/view template listing database records based upon the URI. I've also worked with taxonomy and custom rewrite rules, but no working proof of concept so far.

Question

• How can I create a setup whereas I can call my plugin via a shortcode (e.g. [teams]) which subsequently lists records from separate db tables via a dynamic URL (/teams/{category}/{id}/{more params}

Unfortunately I can't find any snippet or good reference (plugin example) for this use case. Anyone some clever ideas?

Thanks!

Answer 1

A basic implementation could go like this:

Setup public query variables

This lets you fetch these later through get_query_var()

add_filter( 'query_vars', function( $vars ) {
    $vars[] = 'team_category';
    $vars[] = 'team_id';
    
    return $vars;
} );

Add a rewrite rule

This is responsible for turning a url like /team/allstars/999 into a format WordPress can handle. Make sure to replace your_page_id with the actual ID of your teams page.

add_action( 'init', function() {
    add_rewrite_rule( '^teams/([^/]*)/([^/]*)/?', 'index.php?page_id=your_page_id&team_category=$matches[1]&team_id=$matches[2]', 'top' );
} );

Add your shortcode

Just an example of grabbing and outputting your query variables.

add_shortcode( 'teams', function() {
    $team_category = get_query_var( 'team_category' );
    $team_id = get_query_var( 'team_id' );
    
    return "{$team_category}: {$team_id}";
} );

Heads up: when tinkering with rewrite rules, make sure to trigger a permalink refresh by clicking Save Changes in your Settings → Permalinks screen (without making any changes, even).