Entering large number of Envira Galleries at a time

Problem

Over a few years there are large number of photos in facebook. The MPP want to copy all the photos in facebook to his own gallery on his website.

The gallery on the site is using plugin - Envira Gallery. Normally, we create a gallery by drag-drop photos one by one, then place the shortcode on to a post or page.

Now we have created all the gallery, the next step is to create posts to acommodate these galleries. The problems left are

1. create posts for each gallery
2. how to please handreds of gallery posts to the menu
3. add featured image to each post

Basic informaiton

Envira Gallery stores gallery in wp_posts table with post_type = 'envira'.

Regular posts are stored in the same table with post_type='post'.

The data for the photos in a gallery is stored in wp_postmeta table with meta_key = '_eg_gallery_data'

Category information is stored in wp_term_replationships table with object_id = post_id, term_taxonomy_id identifies its category, for gallery term_taxonomy_id=32.

Solution

Create post list using plugin Advanced Post List.

Add posts for each gallery in batch

List gallery title, gallery ID in excel in order to generate sql statement to create posts. The formula for cell D3 as following.

=$D$1&"(1,'"&YEAR(C3)&"-"&MONTH(C3)&"-"&DAY(C3)&"', '"&YEAR(C3)&"-"&MONTH(C3)&"-"&DAY(C3)&"','[envira-gallery id=\"""&A3&"\""]','"&B3&"','','publish','closed', 'closed','','"&SUBSTITUTE(LOWER(B3)," ","-")&"','','','"&YEAR(C3)&"-"&MONTH(C3)&"-"&DAY(C3)&"','"&YEAR(C3)&"-"&MONTH(C3)&"-"&DAY(C3)&"','',0,'http://domain.com/?page_id=', 0,'post');"

The formula on cell C3 as following.

="2018-"&IFERROR(MONTH(LEFT(RIGHT(B3,6),3)&" 1"),MONTH(LEFT(RIGHT(B3,5),3)&" 1"))&"-"&RIGHT(RIGHT(B3,6),2)

Change create date for galleries. This is not necessory, but to do this we can use this script in excel cell F3.

="update wp_posts set post_date='"&YEAR(C3)&"-"&MONTH(C3)&"-"&DAY(C3)&"' where id="&A3&";"

Set Featured Image to posts

Using the first photo in gallery as the featured image. Detail information in this article.

Set category for posts

insert into wp_term_relationships (object_id,term_taxonomy_id)
   SELECT p.ID,32
   FROM wp_posts p 
   LEFT JOIN wp_term_relationships r on r.object_id=p.ID
   WHERE r.object_id is null and p.post_content LIKE '[envira%' and p.post_status='publish'

The condition r.object_id is null is to exclude the posts that already have category specified.

Final Gallery List

Display settings

<a class="gallery-link" href="[post_permalink]" target="_blank">
   <li class="gallery-list">
      <img class="gallery-image" src="[post_thumb]" />
      <div class="gallery-date-title">
         <div class="gallery-date">[post_date]</div>
         <div class="gallery-title">[post_title]</div>
         <div class="gallery-author">by [post_author]<br></div>
      </div>
      <div style="clear:both"></div>
   </li>
</a>

Stylesheet

.gallery-list{ margin:10px; list-style-type:none }
.gallery-list:hover{ background-color:#eee; border:solid 1px #eee; }
.gallery-image{ float:left }
.gallery-date-title{padding:10px 10px 10px 165px; }
.gallery-link{ }
.gallery-date{ font-size: 25px;font-family: fantasy; color: #2c6a8c; }
.gallery-title{ font-size: large; }
.gallery-author{ font-size:smaller; font-weight:normal; }

Author: IT Team