Orphaned Content Report

add_action( 'admin_menu', function () {
    add_management_page( 'Orphaned Content', 'Orphaned Content', 'edit_others_posts', 'wpide-orphans', function () {
        global $wpdb;

        if ( isset( $_POST['wpide_orphans_refresh'] ) && check_admin_referer( 'wpide_orphans_refresh' ) ) {
            delete_transient( 'wpide_orphans' );
        }

        $orphans = get_transient( 'wpide_orphans' );

        if ( false === $orphans ) {
            $rows   = $wpdb->get_results( "SELECT ID FROM {$wpdb->posts} WHERE post_status = 'publish' AND post_type IN ('post','page') ORDER BY post_date DESC LIMIT 3000" );
            $ids    = wp_list_pluck( $rows, 'ID' );
            $by_url = array();
            _prime_post_caches( $ids, false, false );

            foreach ( $ids as $id ) {
                $by_url[ untrailingslashit( (string) wp_parse_url( get_permalink( $id ), PHP_URL_PATH ) ) ] = (int) $id;
            }

            $linked = array();
            $front  = (int) get_option( 'page_on_front' );
            $host   = wp_parse_url( home_url(), PHP_URL_HOST );

            foreach ( array_chunk( $ids, 200 ) as $chunk ) {
                $contents = $wpdb->get_results( "SELECT ID, post_content FROM {$wpdb->posts} WHERE ID IN (" . implode( ',', array_map( 'intval', $chunk ) ) . ')' );

                foreach ( $contents as $row ) {
                    if ( ! preg_match_all( '#href=["\']([^"\'\#]+)#i', $row->post_content, $m ) ) {
                        continue;
                    }

                    foreach ( $m[1] as $href ) {
                        $parts = wp_parse_url( $href );

                        if ( ! empty( $parts['host'] ) && $parts['host'] !== $host ) {
                            continue;
                        }

                        parse_str( $parts['query'] ?? '', $query );
                        $target = isset( $query['p'] ) ? (int) $query['p'] : ( isset( $query['page_id'] ) ? (int) $query['page_id'] : ( $by_url[ untrailingslashit( $parts['path'] ?? '' ) ] ?? 0 ) );

                        if ( $target && $target !== (int) $row->ID ) {
                            $linked[ $target ] = true;
                        }
                    }
                }
            }

            $orphans = array_values( array_diff( array_map( 'intval', $ids ), array_keys( $linked ), array( $front ) ) );
            set_transient( 'wpide_orphans', $orphans, 12 * HOUR_IN_SECONDS );
        }

        echo '<div class="wrap"><h1>Orphaned Content</h1><p>' . esc_html( sprintf( '%d published posts and pages have no internal links pointing to them.', count( $orphans ) ) ) . '</p>';
        echo '<form method="post">';
        wp_nonce_field( 'wpide_orphans_refresh' );
        echo '<p><button class="button" name="wpide_orphans_refresh" value="1">Refresh</button></p></form>';

        if ( $orphans ) {
            echo '<table class="widefat striped"><thead><tr><th>Title</th><th>Type</th><th>Published</th></tr></thead><tbody>';

            foreach ( array_slice( $orphans, 0, 500 ) as $id ) {
                printf( '<tr><td><a href="%1$s">%2$s</a></td><td>%3$s</td><td>%4$s</td></tr>', esc_url( get_edit_post_link( $id ) ), esc_html( get_the_title( $id ) ), esc_html( get_post_type( $id ) ), esc_html( get_the_date( '', $id ) ) );
            }

            echo '</tbody></table>';
        }

        echo '</div>';
    } );
} );