I have a wordpress Mu site which serves as my personal bookmarks (tahnks to the excellent Pressmark Wordpress Template which uses a bookmarklet to digg an article for each blog).

This website contains hundreds of blogs as I create a Blog for each category. So it became a daunting task (if it takes 5 minutes for each blog, I would have wasted 83 hours non-stop of my time for 1000 blogs ), after creating a new blog, to repeat this process again and again:

delete the hello world post and comment
delete the blogroll wordpress links
change the Blog’s title
change the Blog’s subtitle
change the template

So here’s how I automated it.

Pre-Requisite: you must allow Remote MySQL Access on your webhosting CPanel see “How to connect to your remote Wordpress Database from the comfort of your Desktop“.

Let’s first try to do things in Rebol’s console before writing the whole script.

We need to list all Blogs to get their Id because Wordpress Mu Tables contain their Blog id in their name (for example wp_4_posts is the table which stores the posts for Blog id 4):

do http://reboltutorial.com/source/mysql-protocol.r

read/custom mysql://johndoe:123@72.64.23.123/wordpress_db ["SELECT DISTINCT path, blog_id from wp_blogs"]

This will print in Rebol’s console:

==[
    ["/" "1"]
    ["/ranting/" "2"]
    ["/scripts/" "3"]
    ["/test/" "4"]
]

Now I’m going to list the posts of test blog in wp_4_posts table:

read/custom mysql://johndoe:123@72.64.23.123/wordpress_db ["SELECT DISTINCT post_title from wp_4_posts WHERE post_type='post'"]

This should return if the blog is brand new:

[
    ["Hello world!"]
]

Let’s delete all the posts (not the pages):

read/custom mysql://johndoe:123@72.64.23.123/wordpress_db ["DELETE from wp_4_posts WHERE post_type='post'"]
read/custom mysql://johndoe:123@72.64.23.123/wordpress_db ["SELECT DISTINCT post_title from wp_4_posts WHERE post_type='post'"]

Last instruction should return empty block:

[]

Let’s delete all comments:

read/custom mysql://johndoe:123@72.64.23.123/wordpress_db ["DELETE from wp_4_comments]

Let’s delete all the links:

read/custom mysql://johndoe:123@72.64.23.123/wordpress_db ["SELECT DISTINCT link_name from wp_4_links'"]
if ((ask "delete? ") = "Y") [
read/custom mysql://johndoe:123@72.64.23.123/wordpress_db ["DELETE from wp_4_links"]
]

Let’s change the title:

read/custom mysql://johndoe:123@72.64.23.123/wordpress_db ["SELECT DISTINCT option_value from wp_4_options where option_name='blogname'"]
if (length? (New_Title: ask "New Title? ") > 0) [
read/custom mysql://johndoe:123@72.64.23.123/wordpress_db append [] rejoin ["UPDATE wp_4_options  SET option_value='" New_Title "' where option_name='blogname'"]
]
read/custom mysql://johndoe:123@72.64.23.123/wordpress_db ["SELECT DISTINCT option_value from wp_4_options where option_name='blogname'"]

Same kind of code for the sub-title:

read/custom mysql://johndoe:123@72.64.23.123/wordpress_db ["SELECT DISTINCT option_value from wp_4_options where option_name='blogdescription'"]
if (length? (New_Description: ask "New Description? ") > 0) [
read/custom mysql://johndoe:123@72.64.23.123/wordpress_db append [] rejoin ["UPDATE wp_4_options  SET option_value='" New_Description "' where option_name='blogdescription'"]
]
read/custom mysql://johndoe:123@72.64.23.123/wordpress_db ["SELECT DISTINCT option_value from wp_4_options where option_name='blogdescription'"]

Changing the template (mine is DarkRed):

if (length? (New_Template: ask "New Template? ") > 0) [
read/custom mysql://johndoe:123@72.64.23.123/wordpress_db append [] rejoin ["UPDATE wp_4_options  SET option_value='" New_Template "' where option_name='template'"]
read/custom mysql://johndoe:123@72.64.23.123/wordpress_db append [] rejoin ["UPDATE wp_4_options  SET option_value='" New_Template "' where option_name='stylesheet'"]
]

We can now write the whole script. As pre-requisite create the configuration file below to store and load your db settings (see “How to create an object dynamically from a persistent storage and save it back (Application Configuration File)“) . Save it under domaindb.preferences.txt for this example:

user: "johndoe"
password: "123"
db-path: "72.64.23.123/wordpress_db"

You can test the script below by typing in Rebol Console:
do http://reboltutorial.com/source/wordpressmu-newblog

rebol [
  title: "Automate Wordpress Mu New Blog"
  author: "http://reboltutorial.com/blog/automate-wordpress-mu/"
  version: 1.0.0
]

;load mysql library
do http://reboltutorial.com/source/mysql-protocol.r
;you can also download it in your rebol install directory, comment the line above
;and uncomment the line below
;do mysql-protocol.r

;change the file-name accordingly to your choice
prefs-file: %domaindb.preferences.txt

either exists? prefs-file [
    prefs: construct load prefs-file; see article application configuration file
    user: prefs/user
    password: prefs/password
    db-path: prefs/db-path
][
    user: ask "User: "
    password: ask/hide "Password: "
    db-path: ask "DB-Path: "
]

mysql-db: rejoin [mysql:// user ":" password "@" db-path]

  Print "list all blogs id"
  Probe blogs-list: read/custom mysql-db ["SELECT DISTINCT path, blog_id from wp_blogs"]
  write clipboard:// mold blogs-list
  Print "copied to clipboard"

  ;choose Blog id from resulting list above
  id: ask "Blog id? "

  Print "delete post and comment if it is Hello World"
  if/else (["Hello world!"] = pick posts-block: read/custom mysql-db append [] rejoin ["SELECT DISTINCT post_title from " "wp_" id "_posts" " WHERE post_type='post'"] 1) [
      ;delete Hello World posts and all comments
      read/custom mysql-db append [] rejoin ["DELETE from " "wp_" id "_posts" " WHERE post_title='Hello world!'"]
      read/custom mysql-db append [] rejoin ["DELETE from " "wp_" id "_comments"]
      print "Hello World deleted"
  ][
      print "No Hello World! Post"
      probe posts-block
  ]

  Print "list and delete links after confirmation"
  probe read/custom mysql-db append [] rejoin ["SELECT DISTINCT link_name from " "wp_" id "_links"]
  if ((ask "delete links? ") = "Y") [
  read/custom  mysql-db append [] rejoin ["DELETE from " "wp_" id "_links"]
  ]

  Print "change title and subtitle"
  probe read/custom mysql-db append [] rejoin  ["SELECT DISTINCT option_value from " "wp_" id "_options" " where option_name='blogname'"]
  if ((length? (New_Title: ask "New Title? ")) > 0) [
  read/custom mysql-db append [] rejoin  ["UPDATE " "wp_" id "_options"  " SET option_value='" New_Title "' where option_name='blogname'"]
  ]

  probe read/custom mysql-db append [] rejoin  ["SELECT DISTINCT option_value from " "wp_" id "_options" " where option_name='blogdescription'"]
  if ((length? (New_Description: ask "New_Description? ")) > 0) [
  read/custom mysql-db append [] rejoin ["UPDATE " "wp_" id "_options"  " SET option_value='" New_Description "' where option_name='blogdescription'"]
  ]

  Print "Change the template"

  if/else ((length? (New_Template: ask "New Template? (default is DarkRed): ")) > 0) [
  ][
       New_Template: "DarkRed"
  ]

  read/custom mysql-db append [] rejoin ["UPDATE " "wp_" id "_options"  " SET option_value='" New_Template "' where option_name='template'"]
  read/custom mysql-db append [] rejoin append [] rejoin ["UPDATE " "wp_" id "_options" " SET option_value='" New_Template "' where option_name='stylesheet'"]

  Print "The End ... Press a Key"
  input

Reference: MySQL Driver Usage

Next: how to automate multiple Wordpress Mu Sites …

0 people like this post.

Like 

Bookmark this