Table of Contents
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
Comments