Posts Tagged ‘mySQL’

Convert one post type to another another

If you can’t tell yet, we’re a huge fan of custom post types in our theme development and blog tutorials.  They allow us to create feature rich admin areas while keeping with the intuitiveness that is WordPress; so, of course, we dig em!  Have you ever created a custom post type, but wanted to convert a bunch of already written posts into that new post type?  To accomplish this task, we’ve used a Post Type Switcher in the past, however, as of WordPress 3.1 that plugin no longer works so we needed to get down and dirty in the database for this one.  Here is how you do it:

  1. Backup your database just in case you need to roll back for some reason, we’ve never had a problem running the SQL below, but its generally a good idea to back up the DB when performing any action like this.
  2. Run the SQL below in phpMyAdmin, tweaking it to your needs first.
UPDATE wp_posts p
JOIN wp_term_relationships tr ON (p.ID = tr.object_id)
JOIN wp_term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
JOIN wp_terms t ON (tt.term_id = t.term_id)
SET post_type = 'event'
WHERE p.post_type = 'post'
AND p.post_status = 'publish'
AND tt.taxonomy = 'category'
AND t.name = 'Events'

The code above takes posts in the “post” post type that are “publish”ed and in the category “Events” and converts them to the post type “event”. Yes, we’re using The Events Calendar as an example yet again.  Now your events that used to be grouped in with all your other posts now have a neat new home under their own Event post type menu in the admin, making tutorials for clients that much easier!

Change the Status of all WordPress Posts

We recently worked on a project where the client had a blog they wanted reskinned to match a new site design.  During development, they decided to start this blog from scratch with all new posts, but they still wanted to keep the older posts just in case they needed to reference them at some point.  This can be done fairly easily via the database, and here it the SQL to do it:

update wp_posts set post_status = replace(post_status, 'publish', 'draft');
update wp_posts set post_status = replace(post_status, 'inherit', 'draft');

How to Find and Replace Text in MySQL Database using SQL

MySQL database has a handy and simple string function REPLACE() that allows table data with the matching string (from_string) to be replaced by new string (to_string). This is useful if there is need to search and replace a text string which affects many records or rows, such as change of company name, postcode, URL or spelling mistake.

The syntax of REPLACE is REPLACE(text_string, from_string, to_string)

MySQL reference describes REPLACE as function that returns the string text_string with all occurrences of the string from_string replaced by the string to_string, where matching is case-sensitive when searching for from_string. text_string can be retrieved from the a field in the database table too. Most SQL command can be REPLACE() function, especially SELECT and UPDATE manipulation statement.

For example:

update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, 'find this string', 'replace found string with this string');
update client_table set company_name = replace(company_name, 'Old Company', 'New Company')

The above statement will replace all instances of ‘Old Company’ to ‘New Company’ in the field of company_name of client_table table.

Another example:

SELECT REPLACE('www.mysql.com', 'w', 'Ww');

Above statement will return ‘WwWwWw.mysql.com’ as result.

Change Your Table Prefix in WordPress the Simple Way

If your table prefix is “wp_” or “wp1_” or even “wordpress_”, then changing it will bring your WordPress site security to a higher level.

By default Fantastico installation sets “wp_” as a prefix for each WordPress table name. Since this is a known vulnerability, malicious users can exploit your data easily.
They specifically look for the wp_options table, because it will alter your WordPress site look. Through wp_options they can set the url to redirect to their sites, leaving you the impression that your site was defaced.

If you already have a WordPress site, take a look at either your config.php file or go to phpMyAdmin in cPanel to check your tables names.

// Entry in config.php showing wordpress table prefix used in the installation
$table_prefix  = ‘wp_’;
// Only numbers, letters, and underscores please!

Attackers can easily send malicious code using JavaScript injecting SQL targeting your wp_ based tables. To make your wordpress site really secure, change the prefix to something that is difficult to guess. I would pick something almost like a password, except you are limited here to only numbers, letters, and/or underscores.

You might want to check a plugin “wp prefix changer” written by Philipp Heinze for BlogSecurity.net. It should do the job for you. However, I had problems using it, and prefer the manual way which I already done for 2 blogs.

I strongly recommend you to do change the prefix, if it is plain wp_. Just follow the next 6 steps and you should be in good shape. I have tested these steps already with a WordPress 2.8 installation:

1- Take a backup

Since this is a change in your WordPress table structure, you will have to take a backup first.
In cPanel click on the “Backups” icon and click on “Generate/Download a full Backup” and proceed with a “Home Directory Backup”.

2- Edit your wp-config.php file and change

$table_prefix  = ‘wp_’;

to something like

$table_prefix  = ‘op2mro445_’;

3- Change all your your WordPress table names

Go to phpMyAdmin and choose your WordPress database. Click on sql menu item and enter the command to rename all your tables. Do it one table at a time.

Note: You might have more tables that start with “wp_” prefix, change all the tables.
Every time you paste one line into the SQL window, click on GO and see the table name change on your left. Keep changing the table names until all your wordpress tables have the new prefix.

Rename table wp_comments to op2mro445_comments;
Rename table wp_links to op2mro445_links;
Rename table wp_options to op2mro445_options;
Rename table wp_postmeta to op2mro445_postmeta;
Rename table wp_posts to op2mro445_posts;
Rename table wp_terms to op2mro445_terms;
Rename table wp_term_relationships to op2mro445_term_relationships;
Rename table wp_term_taxonomy to op2mro445_term_taxonomy;
Rename table wp_usermeta to op2mro445_usermeta;
Rename table wp_users to op2mro445_users;

4- Edit wp_options

Then you need to edit in the op2mro445_options table ( formaly wp_options ) table

Click on the table name link and then click on “Browse” menu item.
You will see all the data stored in that table. Look under the option_name column header and change wp_user_roles to op2mro445_user_roles.
You will be able to change it by clicking on the edit button for that record.

5- Edit wp_usermeta

And finally apply changes to op2mro445_usermeta formally ( wp_usermeta)

In phpMyAdmin highlight op2mro445_usermeta link and click browse menu.

Change every value under meta_key column header, that starts with the old prefix wp_ to the new prefix op2mro445_ the number or records might be different for your web site.

I had values wp_capabilities, wp_autosave_draft_ids, wp_user_level, wp_usersettings, and wp_usersettingstime for the field meta_key need, and changed each one to the new prefix: op2mro445_capabilities, op2mro445_autosave_draft_ids, op2mro445_user_level….

6- Done! Test your WordPress site now

It should be a lot more secure giving you the peace of mind to focus on blogging.

Oh, one more thing. Do another backup.

How To Turn Off Post Revision In WordPress 2.6 and better

One of the irritating feature for me in WordPress 2.6 is the post revision. I am the only author of my blog and hence this feature is useless to me.

Just in case you are wondering how post revision works, whenever a post is edited, a new row will be created in wp_posts table. Hence if your posts or pages got edited 10 times, you will have 10 new rows in wp_posts table.

In no time your wp_posts table will be filled up and the post ID will be huge.

To turn off this feature, add this following code to wp-config.php:

define('WP_POST_REVISIONS', false);

You can also delete all post revisions by running this query in phpMyAdmin:

DELETE a,b,c
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision'

Be sure to backup your database first before performing any queries in phpMyAdmin.