Into the Wild Spaces

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).

1. The first trick is using the Unix 'tr' command to change the title's spaces to dashes before outputting.
2. 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.
3. The whole thing runs in an 'if then' loop so that empty entries don't crash the script.

#!/usr/local/bin/bash

DBUSER='MY_USER_NAME'
DBPASS='MY_DB_PASSWORD'
DBNAME='MY_DB_NAME'

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"

        OUTTITLE=$(echo $ARTTITLE | tr ' ' '-' ).txt
        echo "New Title: $OUTTITLE"

        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

let IDNUMBER=IDNUMBER+1
done