I imported an old Blogger account into a WordPress blog recently and my titles did not import correctly. So there I was stuck with 500 posts with no titles. I decided the easiest way to fix that would be to copy the post dates from the Blogger account to the titles in WordPress. So here is a fairly quick and dirty way to import qd_db_posts.post_date to qd_db_posts.post_title.
This will all be completed from your OS shell which in this case was CentOS 5.2.
First you will want to create a file that includes post_id, post_date, and post_title from your wordpress databases qd_db_posts table. This can be done via phpMyAdmin by exporting this data or use the below sql code to accomplish the same goal.
[quickcode:sql command]
mysql -u root -p –database=wordpress -e “SELECT ID, post_date, post_title FROM qd_db_posts WHERE post_title = ‘ ‘ > no-title-posts
[/quickcode]
Next open up your new file no-title-posts in your favorite text editor, which in my case is vi, and remove the top line that looks like the below.
ID post_date post_title
In vi you can just open the file by typing vi no-title-posts and then type dd on your keyboard to remove the top line followed by the following :wq! which will write and quit the file.
Now you need to chop off the hours, minutes, and seconds from the no-title-posts lists by running the sed command below after creating an awk input file.
[quickcode:format.awk]
{
printf(“%s %s\n”,$1,$2)
}
[/quickcode]
Now run the following sed command.
[quickcode:sed]
sed “s/’, ”),//g” me | sed “s/(//g” | sed “s/, ‘/ /g” | awk -f format.awk > fix-post_titles
[/quickcode]
Last but not least create the following bash script.
[quickcode:fix-titles.sh]
#!/bin/bash
# sed “s/’, ”),//g” me | sed “s/(//g” | sed “s/, ‘/ /g” | awk -f awk.1
DBNAME=$2
printUsage() {
echo “Usage: $0″
echo ” UPDATE
”
return
}
doAllRows() {
# loop through the tables and optimize them
awk ‘{print $1,$2}’ fix-post_titles | while read POSTID POSTTITLE
do
mysql -u root -pPASSWORDHERE -D $DBNAME -e “UPDATE qd_db_posts SET post_title = ‘$POSTTITLE’ WHERE qd_db_posts.ID = ‘$POSTID’;”
echo “updated $POSTID with title $POSTTITLE”
done
}
if [ $# -eq 0 ] ; then
printUsage
exit 1
fi
case $1 in
–update) doAllRows;;
–help) printUsage; exit 1;;
*) printUsage; exit 1;;
esac
[/quickcode]
The bash script should be used in the following format.
sh fix-post_titles.sh --fix-titles $DATABASENAME
You will see an output from your shell similar to this:
updated 1037 with title 2005-07-03 updated 1034 with title 2005-05-24
Now there should be no empty titled posts in your blog. Check out the books below that are sure to assist resolving issues like the one above.
