get_results(
"SELECT post_id, meta_value AS keyword
FROM {$wpdb->postmeta}
WHERE meta_key = '_awb_seo_focus_keyword'
AND meta_value != ''
ORDER BY meta_value"
);
if ( ! $rows ) return [];
// Group by normalised keyword.
$groups = [];
foreach ( $rows as $row ) {
$key = self::normalise( $row->keyword );
$groups[ $key ][] = $row->post_id;
}
// Only return groups with conflicts (≥2 posts).
$conflicts = [];
foreach ( $groups as $keyword => $post_ids ) {
if ( count( $post_ids ) < 2 ) continue;
$posts = [];
foreach ( $post_ids as $pid ) {
$p = get_post( $pid );
if ( ! $p || $p->post_status !== 'publish' ) continue;
$posts[] = [
'id' => $pid,
'title' => $p->post_title,
'url' => get_permalink( $pid ),
'date' => $p->post_date,
'modified' => $p->post_modified,
'words' => str_word_count( wp_strip_all_tags( $p->post_content ) ),
];
}
if ( count( $posts ) < 2 ) continue;
$conflicts[] = [
'keyword' => $keyword,
'post_count' => count( $posts ),
'posts' => $posts,
'explanation' => self::explain( $posts ),
'resolution' => self::recommend( $posts ),
];
}
usort( $conflicts, fn( $a, $b ) => $b['post_count'] <=> $a['post_count'] );
return $conflicts;
}
private static function normalise( $kw ) {
return mb_strtolower( trim( preg_replace( '/\s+/', ' ', $kw ) ) );
}
private static function explain( $posts ) {
$count = count( $posts );
/* translators: %d = number of competing published pages */
return sprintf( __( 'You have %d published pages targeting the same keyword. Google will struggle to determine which page to rank, so they compete against each other and both rank lower than they could.', 'adworksbid-seo' ), $count );
}
private static function recommend( $posts ) {
// Find the strongest post (most words, most recently modified).
usort( $posts, fn( $a, $b ) => $b['words'] <=> $a['words'] );
$primary = $posts[0];
$secondaries = array_slice( $posts, 1 );
$recs = [];
/* translators: 1: linked page title, 2: word count */
$recs[] = sprintf(
__( 'Keep %2$s as the primary page (it has the most content: %3$d words).', 'adworksbid-seo' ),
esc_url( $primary['url'] ),
esc_html( $primary['title'] ),
(int) $primary['words']
);
foreach ( $secondaries as $s ) {
/* translators: 1: URL, 2: linked page title */
$recs[] = sprintf(
__( 'For %2$s: either merge its unique content into the primary, change its focus keyword to a related but distinct term, or set a canonical pointing to the primary page.', 'adworksbid-seo' ),
esc_url( $s['url'] ),
esc_html( $s['title'] )
);
}
return $recs;
}
/**
* Check if a specific post's keyword conflicts with any other post.
*/
public static function check_post( $post_id ) {
$keyword = AWBSEO_Meta_Manager::get( $post_id, 'focus_keyword' );
if ( ! $keyword ) return [];
global $wpdb;
$conflicts = $wpdb->get_col(
$wpdb->prepare(
"SELECT p.ID FROM {$wpdb->postmeta} pm
JOIN {$wpdb->posts} p ON p.ID = pm.post_id
WHERE pm.meta_key = '_awb_seo_focus_keyword'
AND LOWER(pm.meta_value) = LOWER(%s)
AND pm.post_id != %d
AND p.post_status = 'publish'",
$keyword, $post_id
)
);
return $conflicts;
}
}