Display post number by category

Question

I try to add auto increment post numbering by category. Like cat="myID" > post 1, post 2, post 3...

Display post number not post ID number

In find this solution, and use post_meta looks a good way. But this solution work only for all category. I try to change it like this, but it's doesn't work.

$querystr = "SELECT $wpdb->posts.* FROM $wpdb->posts 
             WHERE $wpdb->posts.post_status = 'publish' 
             AND $wpdb->posts.post_type = 'post' 
             AND $wpdb->posts.post_cat = '35'
             ORDER BY $wpdb->posts.post_date ASC"

Answer 1

Add this code to functions.php. It should add meta value to posts from given category.
You can use hardcoded category slug/ID, but maybe it would be better to store it in options table and allow to change from dashboard.

Change "ID" / "category_slug" to your own.

/**
 * @param int     $post_ID      Post ID.
 * @param WP_Post $post_after   Post object following the update.
 * @param WP_Post $post_before  Post object before the update.
 */
function updateNumbers($post_id, $post_after, $post_before) {
    global $wpdb;

    if ( $post_after->post_type != 'post' )
        return;

    $selected_cat_ID = 123; // <-- ID of your category with numbering

    // -- select category ID by slug --
    // $selected_cat = 'category_slug';
    // $selected_cat_ID = get_terms(['slug' => 'category_slug', 'fields'=> 'ids', 'taxonomy' => 'category', 'hide_empty' => false,]);
    // if ( !is_array($selected_cat_ID) || empty($selected_cat_ID) ) 
    //     return; 
    // $selected_cat_ID = $selected_cat_ID[0];
    // -- end: select category ID by slug --

    $incr_number = get_post_meta($post_id, 'incr_number', true);

    //
    // get posts assigned to your category
    $query = "SELECT p.id FROM {$wpdb->posts} p"
                . " LEFT JOIN {$wpdb->term_relationships} tr ON tr.object_id = p.id"
                . " LEFT JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id"
                . " LEFT JOIN {$wpdb->terms} t ON t.term_id = tt.term_id"
            . " WHERE p.post_status = 'publish' AND p.post_type = 'post'"
            // if you want use slug instead ID change "AND t.term_id = %d" to "AND t.slug = %s"
            . " AND tt.taxonomy = 'category' AND t.term_id = %d"  
            . " ORDER BY p.post_date ASC";
    $ids = $wpdb->get_results( $wpdb->prepare($query, $selected_cat_ID), OBJECT );

    // is post assigned to category after update
    $in_category = false;
    $assigned_posts = [];

    foreach( $ids as $row ) {
        $assigned_posts[] = $row->id;
        if ( $row->id == $post_id )
            $in_category = true;
    }

    if ( !$in_category && $incr_number === '' ) {
        //
        // after the update post is not in the category, before the update was not either
        return;
    }
    else if ($in_category && $incr_number !== '' && $post_before->post_date_gmt == $post_after->post_date_gmt ) {
        //
        // after the update, post is still in the category,
        // publish date has not changed,
        // numbering doesn't need to be changed
        return;
    }
    else if ( !$in_category && $incr_number !== '' ) {
        //
        // post has he been removed from category
        delete_post_meta($post_id, 'incr_number');
    }

    // update numbering
    if ( !empty($assigned_posts) ) {
        $counts = 0;
        foreach( $assigned_posts as $pid )
            update_post_meta($pid, 'incr_number', ++$counts);
    }
}
add_action( 'post_updated', 'updateNumbers', 10, 3);

Answer 2

There's no field in wp_posts called "post_cat".

Try "post_category" instead.