WP- DB – Extraction

get counts

-- 1. Master overview: all post types + statuses
SELECT post_type, post_status, COUNT(*) AS count
FROM wp_posts
GROUP BY post_type, post_status
ORDER BY post_type, post_status;

-- 2. Just posts/pages by status
SELECT post_type, post_status, COUNT(*) AS count
FROM wp_posts
WHERE post_type IN ('post', 'page')
GROUP BY post_type, post_status
ORDER BY post_type, post_status;

-- 3. Published posts/pages totals
SELECT post_type, COUNT(*) AS count
FROM wp_posts
WHERE post_type IN ('post', 'page')
  AND post_status = 'publish'
GROUP BY post_type
ORDER BY post_type;

-- 4. Total featured image links
SELECT COUNT(*) AS featured_image_links
FROM wp_postmeta
WHERE meta_key = '_thumbnail_id';

-- 5. Distinct posts/pages with featured images
SELECT p.post_type, COUNT(DISTINCT p.ID) AS count
FROM wp_posts p
JOIN wp_postmeta pm
  ON p.ID = pm.post_id
WHERE p.post_type IN ('post', 'page')
  AND p.post_status IN ('publish', 'draft', 'private')
  AND pm.meta_key = '_thumbnail_id'
GROUP BY p.post_type
ORDER BY p.post_type;

-- 6. Total attachments by status
SELECT post_status, COUNT(*) AS count
FROM wp_posts
WHERE post_type = 'attachment'
GROUP BY post_status
ORDER BY post_status;

-- 7. Attachment taxonomy usage totals
SELECT tt.taxonomy, COUNT(*) AS count
FROM wp_term_relationships tr
JOIN wp_term_taxonomy tt
  ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_posts p
  ON tr.object_id = p.ID
WHERE p.post_type = 'attachment'
GROUP BY tt.taxonomy
ORDER BY tt.taxonomy;

-- 8. Post/page taxonomy usage totals
SELECT p.post_type, tt.taxonomy, COUNT(*) AS count
FROM wp_term_relationships tr
JOIN wp_term_taxonomy tt
  ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_posts p
  ON tr.object_id = p.ID
WHERE p.post_type IN ('post', 'page')
  AND p.post_status IN ('publish', 'draft', 'private')
GROUP BY p.post_type, tt.taxonomy
ORDER BY p.post_type, tt.taxonomy;

-- 9. Taxonomy definition totals
SELECT taxonomy, COUNT(*) AS count
FROM wp_term_taxonomy
WHERE taxonomy IN ('category', 'post_tag', 'attachment_category', 'attachment_tag')
GROUP BY taxonomy
ORDER BY taxonomy;

-- 10. Attachment meta totals for key fields
SELECT pm.meta_key, COUNT(*) AS count
FROM wp_postmeta pm
JOIN wp_posts p
  ON pm.post_id = p.ID
WHERE p.post_type = 'attachment'
  AND pm.meta_key IN (
    '_wp_attached_file',
    '_wp_attachment_image_alt',
    '_wp_attachment_metadata'
  )
GROUP BY pm.meta_key
ORDER BY pm.meta_key;

actual export querires

-- 1. Export content records: posts + pages
SELECT
  ID,
  post_type,
  post_status,
  post_title,
  post_name AS slug,
  post_date,
  post_modified,
  post_excerpt,
  post_content,
  post_parent,
  menu_order
FROM wp_posts
WHERE post_type IN ('post', 'page')
  AND post_status IN ('publish', 'draft', 'private')
ORDER BY ID;

-- 2. Export taxonomy definitions
SELECT
  t.term_id,
  tt.term_taxonomy_id,
  tt.taxonomy,
  t.name,
  t.slug,
  tt.description,
  tt.parent,
  tt.count
FROM wp_terms t
JOIN wp_term_taxonomy tt
  ON t.term_id = tt.term_id
WHERE tt.taxonomy IN ('category', 'post_tag', 'attachment_category', 'attachment_tag')
ORDER BY tt.taxonomy, t.name;

-- 3. Export post/page -> taxonomy relationships
SELECT
  p.ID AS post_id,
  p.post_type,
  p.post_status,
  p.post_title,
  tt.taxonomy,
  t.term_id,
  t.name AS term_name,
  t.slug AS term_slug
FROM wp_posts p
JOIN wp_term_relationships tr
  ON p.ID = tr.object_id
JOIN wp_term_taxonomy tt
  ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_terms t
  ON tt.term_id = t.term_id
WHERE p.post_type IN ('post', 'page')
  AND p.post_status IN ('publish', 'draft', 'private')
  AND tt.taxonomy IN ('category', 'post_tag')
ORDER BY p.ID, tt.taxonomy, t.name;

-- 4. Export featured image links for posts/pages
SELECT
  p.ID AS post_id,
  p.post_type,
  p.post_status,
  p.post_title,
  CAST(pm.meta_value AS UNSIGNED) AS media_id
FROM wp_posts p
JOIN wp_postmeta pm
  ON p.ID = pm.post_id
WHERE p.post_type IN ('post', 'page')
  AND p.post_status IN ('publish', 'draft', 'private')
  AND pm.meta_key = '_thumbnail_id'
ORDER BY p.ID;

-- 5. Export useful media only:
--    attachments that are either featured images OR have attachment taxonomies
SELECT DISTINCT
  p.ID,
  p.post_title,
  p.post_name AS slug,
  p.post_status,
  p.post_date,
  p.post_modified,
  p.post_excerpt,
  p.post_content,
  p.guid,
  p.post_mime_type,
  p.post_parent
FROM wp_posts p
LEFT JOIN wp_postmeta pm
  ON p.ID = CAST(pm.meta_value AS UNSIGNED)
  AND pm.meta_key = '_thumbnail_id'
LEFT JOIN wp_term_relationships tr
  ON p.ID = tr.object_id
LEFT JOIN wp_term_taxonomy tt
  ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE p.post_type = 'attachment'
  AND (
    pm.post_id IS NOT NULL
    OR tt.taxonomy IN ('attachment_category', 'attachment_tag', 'category')
  )
ORDER BY p.ID;

-- 6. Export media -> taxonomy relationships
SELECT
  p.ID AS media_id,
  p.post_title,
  tt.taxonomy,
  t.term_id,
  t.name AS term_name,
  t.slug AS term_slug
FROM wp_posts p
JOIN wp_term_relationships tr
  ON p.ID = tr.object_id
JOIN wp_term_taxonomy tt
  ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_terms t
  ON tt.term_id = t.term_id
WHERE p.post_type = 'attachment'
  AND tt.taxonomy IN ('attachment_category', 'attachment_tag', 'category')
ORDER BY p.ID, tt.taxonomy, t.name;

-- 7. Export useful media meta
SELECT
  pm.post_id AS media_id,
  pm.meta_key,
  pm.meta_value
FROM wp_postmeta pm
JOIN wp_posts p
  ON pm.post_id = p.ID
WHERE p.post_type = 'attachment'
  AND pm.meta_key IN (
    '_wp_attached_file',
    '_wp_attachment_image_alt',
    '_wp_attachment_metadata'
  )
ORDER BY pm.post_id, pm.meta_key;

-- 8. Optional: export ALL attachments instead of only useful media
SELECT
  ID,
  post_title,
  post_name AS slug,
  post_status,
  post_date,
  post_modified,
  post_excerpt,
  post_content,
  guid,
  post_mime_type,
  post_parent
FROM wp_posts
WHERE post_type = 'attachment'
ORDER BY ID;

-- 9. Optional: shortcode scan for posts/pages
SELECT
  ID,
  post_type,
  post_status,
  post_title
FROM wp_posts
WHERE post_type IN ('post', 'page')
  AND post_status IN ('publish', 'draft', 'private')
  AND (
    post_content LIKE '%[su_%'
    OR post_content LIKE '%[/su_%'
  )
ORDER BY ID;

-- 10. Optional: shortcode sample with full content
SELECT
  ID,
  post_type,
  post_status,
  post_title,
  post_content
FROM wp_posts
WHERE post_type IN ('post', 'page')
  AND post_status IN ('publish', 'draft', 'private')
  AND (
    post_content LIKE '%[su_tabs%'
    OR post_content LIKE '%[su_tab%'
    OR post_content LIKE '%[su_spoiler%'
    OR post_content LIKE '%[su_accordion%'
  )
ORDER BY ID
LIMIT 50;