WordPress DB Hacks: Determining the ID of a category parents
June 3, 2010
When manipulating WordPress databases for exports and merges, sometimes it is helpful to get a list of all of the parents for the categories your posts are in.
For the import I am working on right now. The category list is being flattened from 30 categories down to 5 categories. The blog has just over 75 thousand posts in it and doing the conversion manually would be a rather arduous process to say the least.
So, I came up with this little query to give me the parent category for each of the child categories on that site.
It’s fairly simple but I’ve had to write it several times now and thought maybe someone else might need it sometime too:
SELECT `tt1`.`term_taxonomy_id` as 'Child Tax ID', `t1`.`term_id` as 'Child Term ID', `t1`.`name` as 'Child Name', `tt2`.`term_taxonomy_id` as 'Parent Tax ID', t2.`term_id` as 'Parent Term ID', `t2`.`name` as 'Parent Name' FROM `wp_term_taxonomy` `tt1`, `wp_term_taxonomy` `tt2`, `wp_terms` `t1`, `wp_terms` `t2` where `tt1`.`term_id` = `t1`.`term_id` and `tt1`.`parent` = `t2`.`term_id` and `tt1`.`taxonomy` = 'category' and `tt2`.`term_id` = `t2`.`term_id` and `tt2`.taxonomy = 'category';
Now I can just grab it instead of rewriting it again next time. After all, this is The Code Cave…
PS – If you just want to see the top level categories, here’s how:
SELECT `tt1`.`term_taxonomy_id`, `t1`.`term_id`, `t1`.`name` FROM `wp_term_taxonomy` `tt1`, `wp_terms` `t1`where `tt1`.`term_id` = `t1`.`term_id` and `tt1`.`taxonomy` = 'category' and `tt1`.`parent`=0
MySQL Founder Resigns from Sun over Quality of MySQL 5.1
February 5, 2009
I first laid eyes on Michael Widenius, the original and principle author of the MySQL database software at the 2008 MySQL Conference in San Jose. Michael, who is more commonly known simply as “Monty”, had recently had his pride and joy, the MySQL AB company purchased by Sun Microsystems. I’d say that just about every attendee was extremely nervous about the future of MySQL, and every (new) Sun employee was eager to say “Oh, the purchase was great thing!”. There certainly was a Sun head hunter at every corner ready to hand out an application form (and a pair of boxer shorts or two).
I left the conference having learned a lot of the techniques Lee Newton would be soon applying to the b5media database architecture. But far as the Sun purchase was concerned… I felt a little less safe. It was worrying that something that important was not quite as secure as it once was. There was no indication something bad was about to happen, but the way things were, it was sure to be painful if something did.
So now not a year later, Monty announced today that he has quit because Sun released MySQL 5.1 without first resolving significant flaws despite Monty’s strenuous objections. Monty previously released a detailed list describing some of the “many known and unknown fatal bugs in the new features that are still not addressed.” My take from the article is that we should consider MySQL 5.1 should be considered a 5.0 maintenance release with pre-release beta features included.
Obviously Monty had spoken up to the higher ups at Sun prior to the release, but as he explained this had little affect. I think that the open source world collided heavily with the corporate reality of “Cost, Schedule, Features, or Quality – Choose 3”. In the open source communities, the choice is simple, the schedule rarely if ever enters the mix. In this corporate battle, it obviously was one of the three chosen. Monty had been seen this coming early on and had been very vocal even back in April 2008 (see page 19) calling for Sun to “Create a release policy and independent release policy board that can’t be manipulated by people in charge of server development (to not allow anyone to sacrifice quality to reach personal goals)” Whoa… “To reach Personal Goals” – even then it sounded to me like he had someone in particular in mind. Additionally, from another comment later in the keynote: “Sun is more opensource/free software friendly than MySQL AB has been lately and is driving MySQL in the right direction” it seems obvious that there was a power struggle going on. After all MySQL AB was co-founded by Michael and he should have had significant influence over the company’s philosophies. I don’t know the rest of this particular sub-plot, but I’m certain there is more to be told.
In any case, in light of MySQL 5.1’s quality issues at time of general availability Michael tells us he immediately quit but was talked into giving three months months to Sun for reconciliation and putting things right. That stretched into seven months, but the end result was the same. Michael announced today that he’s resigned and will be creating his own version of MySQL called MySQL-Maria which will will incorporate all MySQL updates but include rewrites and additional code to improve stability. It will be primarily developed by a new company he is forming named Monty Program Ab which will be “a true open source company”. I’m still not sure what that means, but I guess I could read up on it in more detail, if I wanted to.
So, what does this mean? Is it a good thing? I guess it is good that someone is out there fixing known bugs in MySQL, but won’t that happen anyway with an open source project? It’s great to see another company formed to further the open source movement, but can a MySQL standards war be beneficial? Given the adoption rate of new MySQL releases, does it even matter that 5.1 was released? It’s not as if ISPs will install it anytime before 2010 by which time there will be patches.
In the end, from the clues I’ve seen I suspect this episode occurred due to a personal, philosophical dispute that Monty didn’t win. Regardless, I wish him success with his new project and thank him for providing a tool that I use ever day: MySQL.
WordPress Quick Tip: Fixing the number of comments per post
January 7, 2009
For various reasons, sometimes the number of comments shown under the title of the post may not match the number of actual comments displayed under the post.
Here is a simple SQL statement that will resolve the issue:
update `wp_posts` set comment_count = (select count(*) from wp_comments
WHERE `comment_post_ID` = `ID` and comment_approved = '1')
You can use this to verify what would change and where your problems may lie:
SELECT ID, `post_title`, `comment_count`,
(select count(*) from `wp_comments`
WHERE (`comment_post_ID` = `ID`) and (`comment_approved` = '1')) as NewCC
FROM `wp_posts`
WHERE `comment_count` <> (select count(*) from `wp_comments`
WHERE (`comment_post_ID` = `ID`) and (`comment_approved` = '1'))
PHPMyAdmin announces drop of PHP 4 support.
October 2, 2007
I was just over at the PHPMyAdmin site and saw this quote:
Welcome to phpMyAdmin 2.11, which will probably be the last series supporting PHP 4.
Notice the ”probably” stuck in there. They are testing the waters.
If phpMyAdmin is dropping the whole PHP 4 line, it just might push the WordPress adoption time a bit further. Where phpMyAdmin goes, I have to think, the ISPs will not be slow to follow.
MySQL Commands Cheat Sheet
April 19, 2006
I wanted to get a list of the tables in my MySQL database a couple weeks ago. I bookmarked on place that had a list of commands. I’m including that info here for furture reference. I’ve found dozens of copies of that list elsewhere, so I’m not gonna link to any particular site.
Check back here periodically as I add more and more commands here. There are already 5 good ones that I need to put on later tonight…but I’ve got a hot date with my wife that takes priority!
Common MySQL Commands
| Description | Command |
|---|---|
| Administrative | |
| To login as root (from shell) | mysql -uroot -p[password] |
| Create a database | create database [db name]; |
| Grant all permissions for a database to a user | grant all privileges on [db name].* to ‘[user]‘@’localhost’ identified by ‘[user password]‘; |
| List all databases on the sql server. | show databases; |
| Switch to a database. | use [db name]; |
| To see all the tables in the db. | show tables; |
| To see database’s field formats. | describe [table name]; |
| To delete a database | drop database [db name]; |
| Dump all databases for backup.Backup file is sql commands to recreate all db’s. | mysqldump --user=root --password=blah --all-databases
>/tmp/sql-01_backup.sql |
| Exit mysql command line | quit |
| Queries (SELECTS) | |
| Show all data in a table. | SELECT * FROM [table name]; |
| Count rows. | SELECT COUNT(*) FROM [table name]; |
| Show certain selected rows with the value “whatever”. | SELECT * FROM [table name] WHERE [field name] = “whatever”; |
| Show all records containing the name “Bob” AND the phone number ’3444444′. | SELECT * FROM [table name] WHERE name = “Bob” AND phone_number = ’3444444′; |
| Show all records not containing the name “Bob” AND the phone number ’3444444′ order by the
phone_number field. |
SELECT * FROM [table name] WHERE name != “Bob” AND phone_number = ’3444444′ order by
phone_number; |
| Show all records starting with the letters ‘bob’ AND the phone number ’3444444′. | SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ’3444444′; |
| Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This
finds any record beginning with a. |
SELECT * FROM [table name] WHERE rec RLIKE “^a$”; |
| Show unique records. | SELECT DISTINCT [column name] FROM [table name]; |
| Show selected records sorted in an ascending (asc) or descending (desc). | SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC; |
| Join tables on common columns. | select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id; |
| User Management | |
| Switch to the mysql db. Create a new user. | INSERT INTO [table name] (Host,User,Password) VALUES(‘%’,'user’,PASSWORD(‘password’)); |
| Change a users password.(from unix shell). | [mysql dir]/bin/mysqladmin -u root -h hostname.blah.org -p password
‘new-password’ |
| Change a users password.(from MySQL prompt). | SET PASSWORD FOR ‘user’@'hostname’ = PASSWORD(‘passwordhere’); |
| Switch to mysql db.Give user privilages for a db. | INSERT INTO [table name]
(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES (‘%’,'db’,'user’,'Y’,'Y’,'Y’,'Y’,'Y’,'N’); |
| Update database permissions/privilages. | FLUSH PRIVILEGES; |
| Table Alteration | |
| To delete a table. | drop table [table name]; |
| Returns the columns and column information pertaining to the designated table. | show columns from [table name]; |
| To update info already in a table. | UPDATE [table name] SET Select_priv = ‘Y’,Insert_priv = ‘Y’,Update_priv = ‘Y’ where
[field name] = ‘user’; |
| Delete a row(s) from a table. | DELETE from [table name] where [field name] = ‘whatever’; |
| Delete a column. | alter table [table name] drop column [column name]; |
| Add a new column to db. | alter table [table name] add column [new column name] varchar (20); |
| Change column name. | alter table [table name] change [old column name] [new column name] varchar (50); |
| Make a unique column so you get no dupes. | alter table [table name] add unique ([column name]); |
| Make a column bigger. | alter table [table name] modify [column name] VARCHAR(3); |
| Delete unique from table. | alter table [table name] drop index [colmn name]; |
| Load a CSV file into a table. | LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES
TERMINATED BY ‘\n’ (field1,field2,field3); |
| Create Table Example 1. |
CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname
VARCHAR(35),suffix VARCHAR(3), |
| Create Table Example 2. |
create table [table name] (personid int(50) not null auto_increment primary key,firstname
varchar(35),middlename varchar(50),lastname varchar(50) default ‘bato’); |