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.
Classic Shell Scripting (Paperback)
|
||||||||
