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 = 'firstname.lastname@example.org'
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.