Using phpMyAdmin to update your database

Many users of Textpattern like to use rss_admin_db_manager to tweak or update their sites. In my case I prefer to use phpMyAdmin which in my view is much more stable than an orphaned plugin.

While writing a blog post this morning, I had to fire up phpMyAdmin to check on some stats and also to perform a couple of changes to an author name and the comments name.

Change the author name

I wanted to change my author name associated with the TXP Tips I have written. The author name is stored in a custom_field with the name “Jonathan”. In order to change the author name to my full name this is the command to enter:

UPDATE `textpattern` SET custom_2 = 'Jonathan Stubbs' WHERE custom_2 = 'Jonathan'

This command updates the textpattern table where the custom_2 field is set to “Jonathan” and replaces all instances with “Jonathan Stubbs”.

Change the comments name

Couple days back there was a commenter with the same name so I thought I should change my comments name too. Here is how – note the extra AND clause set to the email address in order to select only my comments name and not that of the other Jonathan!

UPDATE `txp_discuss` SET name = 'Jonathan Stubbs' WHERE name = 'Jonathan' AND email = 'mail@txptips.com'

Other commands

In order to count the number of articles in a given section, use this:

SELECT * FROM `textpattern` WHERE `Section` = 'articles'

Select all articles in the section articles authored by me:

SELECT * FROM `textpattern` WHERE `Section` = 'articles' AND custom_2 = 'Jonathan Stubbs'

Select all articles in the section articles NOT authored by me:

SELECT custom_2 FROM `textpattern` WHERE Section = 'articles' AND custom_2 != 'Jonathan Stubbs'

Just a few commands that may or may not be useful – using phpMyAdmin can be a real time saver but you should ALWAYS backup your database before performing any updates.

comments powered by Disqus