A selection of the SQL code used to maintain this site.


The SQL code below assumes the use of two specific WordPress plugins – Adminer and Unite Gallery;

Adminer – An excellent, full featured MySQL management tool. This is needed to run the SQL code below.

Unite Gallery – A picture gallery plugin.

Other plugins referenced by some of the SQL code;

Easy Watermark – Adds image watermarks.

Simple Map – Displays a map for a place name or set of coordinates.

Weather Underground – Displays the weather for a town or city.


The Code

#Count of pictures in each gallery.

SELECT i.type, c.title, c.id, count(i.catid)
FROM wp_bzvd_unitegallery_items i right join wp_bzvd_unitegallery_categories c on i.catid = c.id
WHERE c.title like ‘%’
ORDER BY i.type, title;


#Dates in each gallery. Assumes each picture is prefixed with a date.

SELECT distinct(c.title), left(i.title,10)
FROM wp_bzvd_unitegallery_items i join wp_bzvd_unitegallery_categories c on i.catid = c.id
WHERE c.title like ‘%’
ORDER BY c.title, left(i.title,10);


#In more than one gallery (ie duplicate entries, although some will be ok).

SELECT wp_bzvd_unitegallery_items.imageid, wp_bzvd_unitegallery_items.title, wp_bzvd_unitegallery_items.catid, cats.title
FROM wp_bzvd_unitegallery_items INNER JOIN (SELECT title from wp_bzvd_unitegallery_items WHERE wp_bzvd_unitegallery_items.catid not in (23, 27, 28, -1,159,160) GROUP BY title
HAVING count(id) > 1 ) dup ON wp_bzvd_unitegallery_items.title = dup.title JOIN wp_bzvd_unitegallery_categories cats ON cats.id = wp_bzvd_unitegallery_items.catid
ORDER BY wp_bzvd_unitegallery_items.title;


#Find duplicate images.

SELECT substring_index(substring_index(guid, ‘/’, -1),’.jpg’, 1) fname
FROM wp_bzvd_posts i JOIN (SELECT substring_index(substring_index(guid, ‘/’, -1),’1.jpg’, 1) noone FROM wp_bzvd_posts WHERE post_mime_type=’image/jpeg’ and GUID like ‘%1.jpg’) ones ON substring_index(substring_index(guid, ‘/’, -1),’.jpg’, 1)=ones.noone
WHERE post_mime_type=’image/jpeg’;


#Uploaded but not in gallery and In a gallery but deleted from upload directory.

First create a couple of views.

create view vw_post_images as select `wp_bzvd_posts`.`ID` AS `id`,`wp_bzvd_posts`.`post_title` AS `post_title`,`wp_bzvd_posts`.`post_name` AS `post_name`,`wp_bzvd_posts`.`guid` AS `guid` from `wp_bzvd_posts`
WHERE (`wp_bzvd_posts`.`post_mime_type` = ‘image/jpeg’);

create view vw_gallery_images as select `wp_bzvd_unitegallery_items`.`catid` AS `catid`,`wp_bzvd_unitegallery_items`.`imageid` AS `imageid`,`wp_bzvd_unitegallery_items`.`url_image` AS `url_image`
from `wp_bzvd_unitegallery_items`
WHERE (`wp_bzvd_unitegallery_items`.`type` = ‘image’);


#In a gallery but deleted from upload directory

SELECT catid, title, url_image from vw_gallery_images JOIN wp_bzvd_unitegallery_categories c ON c.id = catid
WHERE not exists (select ‘x’ from vw_post_images where vw_post_images.id = vw_gallery_images.imageid)
ORDER BY url_image;


#Uploaded but not in gallery

FROM vw_post_images
WHERE not exists (select ‘x’ from vw_gallery_images where vw_post_images.id = vw_gallery_images.imageid)
ORDER BY guid;


#Change Gallery Ordering. Copy results from this script into an editor, change ‘ordering’ number and then run in Adminer.

SELECT CONCAT(‘update wp_bzvd_unitegallery_categories set ordering = ‘,ordering, ‘
WHERE id=’,id, ‘ and title=”’, title,”’;’)
from wp_bzvd_unitegallery_categories
ORDER BY title;


#Page Title vs Post Name vs Gallery Title. Gives a full(ish) summary of each page. NOTE: Depends heavily on standard formatting in each page.

wp_bzvd_posts.id Page_ID,
post_title Page,
post_name Slug,
c.title Gallery,
LEFT(substring_index(substring_index(post_content, ‘<p style=”text-align: left;”>’, -1),'</p><p>&nbsp;</p><p><!–more–></p>’, 1),100) Head_Text,
LEFT(substring_index(substring_index(post_content, ‘center;”><a href=”http://www.planetpicture.net/photo-diary/’, -1),'”>&lt;’, 1),50) Last_page,
LEFT(substring_index(substring_index(post_content, ‘<a href=”http://www.planetpicture.net/photo-diary/’, -1),'”>Next Page&gt;’, 1),50) Next_page,
LEFT(substring_index(substring_index(post_content, ‘lat=”‘, -1),'”]’, 1),50) Coords,
LEFT(substring_index(substring_index(post_content, ‘location=”‘, -1),'” layout=’, 1),50) Weather,
post_status Status
FROM wp_bzvd_posts left join wp_bzvd_unitegallery_categories c on SUBSTRING_INDEX(mid(post_content,instr(post_content, ‘catid=’)+6,3),’]’,1) = c.id
WHERE post_type = ‘page’
and post_content like ‘%/photo-diary/%’
and post_title != ‘Diary’
and post_title like’%’
ORDER BY post_title;


#Posts that have a header/featured image.

SELECT m.post_id, p.post_type, p.post_title, meta_value image_id,w.post_id wmark, i.guid FROM wp_bzvd_postmeta m join wp_bzvd_posts p on m.post_id = p.id join wp_bzvd_posts i on m.meta_value = i.id left outer join (select post_id FROM wp_bzvd_postmeta where meta_key = ‘_ew_watermarked’) w on w.post_id = m.meta_value where meta_key = ‘_thumbnail_id’ order by post_type, post_title;


#Posts MISSING a header/featured image.

SELECT p.id, p.post_title
FROM wp_bzvd_posts p
WHERE p.id NOT IN (select post_id from wp_bzvd_postmeta WHERE meta_key = ‘_thumbnail_id’) AND post_type = ‘page’
ORDER BY post_title;


#Watermarked images.

FROM `wp_bzvd_postmeta`
WHERE meta_key = ‘_ew_watermarked’;