Conditional Batch file renaming with mysql data

Question

Hello,

I wonder if anyone knows how could I rename multiple files, all of them originally named with same structure, and add some data extracted from a mysql DB according to specifics rules.

For example I have 500 files named with this vars:

ID NAME ADDRESS PHONE.wav => 1234567 PAULSIMON WESTDR122 9942213456.wav

Now I need to rename files taking some data from the databases for each file, and append the data from a query appended to the filename.

For example add the data resulting from a query Where some conditions match, and the data to build the query is taked from original file name, as ID or NAME.

i other words, lets say that I want to build a query taking ID & NAME from file 1234567 PAULSIMON WESTDR123 9942213456.wav as WHERE statements to take another value as BirthDATE and add this to new filename, so final result should be:

ID NAME ADDRESS PHONE BIRTHDATE.wav

I will appreciate any help on this.

I need this to be done on a LINUX server.

Answer 1

#!/bin/sh

# adjust this to your actual database name
# also some tweaks will be needed if you use mysql login/password authentication
DB_NAME=my_db_name

IFS=$'\x0d\x0a'
FNAMES=`find . -name "*.wav" -printf '%P\n'`
for FNAME in $FNAMES; do
    echo "[.] file $FNAME"
    ID=`echo $FNAME | awk '{print $1}'`
    NAME=`echo $FNAME | awk '{print $2}'`
    echo "[.] id=$ID name=$NAME"
    # adjust query at next line to your needs
    Q=`mysql $DB_NAME --skip-column-names -Be "SELECT birthdate FROM users WHERE id='$ID' AND name='$NAME' LIMIT 1"`
    echo "[.] mysql returned $Q"
    if [ "$Q" != "" ]; then
        echo renaming "$FNAME"  TO  "${FNAME%.wav} $Q.wav"
        # uncomment next line to do actual rename
        #mv "$FNAME" "${FNAME%.wav} $Q.wav"
    fi
    echo
done