The situation is as follows: you have a MySQL database that backstops your
blog, and you'd like to output each database entry as an individual text file.
Not as strange an idea as it seems - maybe you'd like to output whatever edits
you made to already-published articles, maybe you'd like a bunch of text files
you can use as a backup in case your relational database kicks the bucket.
Whatever the reason, anyone who's ever written a blog will agree that
significant time and effort goes into writing, and the risk of losing all that
text is formidable and vaguely hair-raising.
Here is a script I put together that goes through every entry in a blog's
database, and outputs the text to a file whose title is created from the data
and the name, with dashes, like this:
I run Serendipity (S9Y) so all the writing goes into a single table. Let's say
your database is called 'musings' in your MySQL system. Enter that database
using the MySQL command prompt and poke around. Type 'show tables' to list all
tables that make up that database. On a Serendipity system, all the writing
goes into a table called serendipity_entries, specifically into the fields
'body' and 'extended'. No matter whether you're using Wordpress or Joomla or
something else, it works the same way: log into your database and poke around
until you find out where the text is located, and modify the appropriate fields
in this script.
There are a few tricks in play here, and a few obvious ways it could be
improved (For example, I know there are fewer than 250 entries, so I used a 1
&endash; 250 loop. It would be smarter to query the database to figure out
what the highest number used is. I'll fix that in a next version).
The first trick is using the Unix 'tr' command to change the title's spaces to
dashes before outputting. The second trick is getting the date into a useable
YYYY-MM-DD format. The mysql commands 'date_format' and 'from_unixtime' do the
leg-work. The whole thing runs in an 'if then' loop so that empty entries
don't crash the script.
IDNUMBER=1
while [ $IDNUMBER -lt 250 ]
do
echo "Counter: $IDNUMBER"
if mysql -u $DBUSER -p$DBPASS -e "select id from serendipity_entries where id='$IDNUMBER' " $DBNAME;
then
ARTTITLE=$(echo "select title from serendipity_entries where id=$IDNUMBER" | mysql
$DBNAME -u $DBUSER -p$DBPASS -Ns)
echo "ID: $IDNUMBER Title: $ARTTITLE"
OUTFECHA=$(echo "select date_format(from_unixtime(timestamp), '%Y-%c-%d')
from serendipity_entries where id=$IDNUMBER" | mysql $DBNAME -u $DBUSER -p$DBPASS -Ns)
echo "Date: $OUTFECHA"
mysql -u $DBUSER -p$DBPASS -sN -e "select body from serendipity_entries where
id='$IDNUMBER' into outfile '/tmp/tmpbody'; " $DBNAME
mysql -u $DBUSER -p$DBPASS -sN -e "select extended from serendipity_entries where
id='$IDNUMBER' into outfile '/tmp/tmpextended'; " $DBNAME
cd /tmp
cat tmpbody tmpextended > tmpwildspacearticle.txt
cp tmpwildspacearticle.txt /home/randymon/articles/$OUTFECHA-$OUTTITLE
rm /tmp/tmpbody
rm /tmp/tmpextended
else
echo "$IDNUMBER is empty."
fi
