IT Blog

  • Blog
  • Technology
    • Technology
    • Architecture
    • CMS
    • CRM
    • Web
    • DotNET
    • Python
    • Database
    • BI
    • Program Language
  • Users
    • Login
    • Register
    • Forgot Password?
  • ENEN
    • 中文中文
    • ENEN
Experience IT
In a World of Technology, People Make the Difference.
  1. Home
  2. Technology
  3. CMS
  4. Wordpress
  5. Plugin
  6. Adding Featured Image for Envira Gallery posts in batch

Adding Featured Image for Envira Gallery posts in batch

2020-03-29 774 Views 0 Like 0 Comments

Table of Contents

  • Featured Image
    • Add featured image direct into database
  • Gallery Photos
    • Get relationship between post_id and gallery_id
    • Adding featured images
    • Result

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

Envira Gallery and Featured Image

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:"";}

gallery-photos-in-db

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

first-photo-in-gallery

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'

posts-with-featured-image

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

relationship-between-post_id-gallery_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

without_featured-image

with-featured-image

 

Loading

error
fb-share-icon
Tweet
fb-share-icon
IT Team
Author: IT Team

Tags: None
Last updated:2020-03-29

IT Team

This person is lazy and left nothing

Like
< Previous
Next >

Comments

Cancel reply
Newest Hotspots Random
Newest Hotspots Random
Rich editor not working Making web page scroll down automatically Getting data from Dapper result All Unicode Chars How to keep and display contact form 7 data Common Regular Expressions
Python - Crawling images from json file 手机邮件设定 Generating Test Data with SQL Scripts Develop UNDO feature with Entity Framework 6 and trigger PHP 7.2 issue - count() Data visualization with Flask
Categories
  • Architecture
  • BI
  • C#
  • CSS
  • Database
  • DotNET
  • Hosting
  • HTML
  • JavaScript
  • PHP
  • Program Language
  • Python
  • Security
  • SEO
  • Technology
  • Web
  • Wordpress

COPYRIGHT © 2021 Hostlike IT Blog. All rights reserved.

This site is supported by Hostlike.com