Adding Featured Image for Envira Gallery posts in batch

Featured Image

Featured images are stored in meta_value with meta_key='_thumbnail_id' in wp_postmeta table.

SELECT * FROM wp_postmeta WHERE meta_key='_thumbnail_id' and post_id=@post_id

Add featured image direct into database

Find out the id of the image that you want to be a featured image in wp_post table, then insert a record into wp_postmeta table with meta_key='_thumbnail_id'

@image_id = select id from wp_post where post_type='attachment' and post_mime_type='image/jpeg'
insert into wp_postmeta (post_id,meta_key,meta_value) values (147, '_thumbnail_id',@image_id);

Gallery Photos

Photos in Envira Gallery are stored in meta_value with meta_key='_eg_gallery_data' in wp_postmeta table.

select * from wp_postmeta where meta_key='_eg_gallery_data'

Data stored looks like this:

a:3:{s:2:"id";i:3972;s:7:"gallery";
a:8:{i:3973;a:7:{
s:6:"status";s:6:"active";
s:3:"src";
s:102:"http://vincentkempp.com/wp-content/uploads/2020/03/85092119_2768984699811558_8547879668101939200_n.jpg";
s:5:"title";s:47:"85092119_2768984699811558_8547879668101939200_n";
s:4:"link";s:102:"http://vincentkempp.com/wp-content/uploads/2020/03/85092119_2768984699811558_8547879668101939200_n.jpg";
s:3:"alt";s:0:"";s:7:"caption";s:0:"";s:5:"thumb";s:0:"";}

Get the first image inside the gallery:

SELECT p.id gallery_id,
   substring(meta_value, locate(':"http://', m.meta_value)+2,
   locate('";s:5:"title";', m.meta_value)-locate(':"http://',m.meta_value)-2) featured_image
FROM wp_posts p
JOIN wp_postmeta m on m.post_id=p.id and p.post_type='envira' and p.post_status='publish' and m.meta_key='_eg_gallery_data'
ORDER BY p.id

Check if the gallery post already has featured image.

SELECT m.post_id,post_title, m.meta_value image_id
FROM wp_posts p
JOIN wp_postmeta m on m.post_id=p.ID and p.post_type='post' and p.post_content LIKE '[envira-gallery id=%' and p.post_status='publish'
WHERE meta_key='_thumbnail_id'

Get relationship between post_id and gallery_id

SELECT p.id,post_title,
   substring(p.post_content, 21, locate('"]',p.post_content)-21) gallery_id,
   p.post_content
FROM wp_posts p
LEFT JOIN wp_postmeta m on m.post_id=p.ID and meta_key='_thumbnail_id'
WHERE p.post_type='post' and p.post_content LIKE '[envira-gallery id=%' and p.post_status='publish' and meta_key is null
ORDER by p.id

Adding featured images

INSERT INTO wp_postmeta (post_id,meta_key,meta_value)
SELECT p1.id,'_thumbnail_id',img.id FROM
(
   SELECT p.id,post_title,
      substring(p.post_content, 21, locate('"]',p.post_content)-21) gallery_id
   FROM wp_posts p
   LEFT JOIN wp_postmeta m on m.post_id=p.ID and meta_key='_thumbnail_id'
   WHERE p.post_type='post' and p.post_content LIKE '[envira-gallery id=%' and p.post_status='publish' and meta_key is null
) p1                                       -- post_id
JOIN
(
   SELECT p.id gallery_id, substring(meta_value, locate('s:3:"src";',m.meta_value)+17, locate('";s:5:"title";',m.meta_value)-locate('s:3:"src";',m.meta_value)-17) featured_image 
   FROM wp_posts p 
   JOIN wp_postmeta m on m.post_id=p.id and p.post_type='envira' and p.post_status='publish' and m.meta_key='_eg_gallery_data'
) g ON p1.gallery_id=g.gallery_id          -- gallery_id
JOIN
wp_posts img ON g.featured_image=img.guid  -- image_id

Result

Author: IT Team