Accessing MySQL Using Bash Scripting

Accessing MySQL Using Bash Scripting

We have a spreadsheet in which the first column contains a customer’s email addres.  We want to perform some processing on each customer, but the script that does the processing only recognizes the customerId.  What’s the quickest way to get the information we need?  You are probably thinking we run a query in MySQL and dump the data.  And that would also work.  But we need some kind of bash shell to run the processing against customer’s ID anyway.  So why not just extract the MySQL information we need at the same time.  The following script does just that.

It assumes a five column table, but can be easily modified for more or less:

CSV Data format:

------------------------------------------------------------------------------

Email, First Name, Last Name, Address, Birthdate

------------------------------------------------------------------------------

whoever@wherever.com, Ming, Nguyen, wherever, 4/6/1958

someoneelse@someother.com, John, Doe, somewhere, 4/6/1981

------------------------------------------------------------------------------

#!/bin/bash

# Processes customer Id’s via a CLI PHP Script

# 120406

PHP_DIR=/usr/bin

CLI_DIR=/home/user/site/public

AQCOMMAND=cli.queue-add.message

TQCOMMAND=cli.queue-run.listener

QUEUE_NAME=blog-test-queue

MTYPE=sendCustomerEmail

MESSAGE=customerId
USER=Your_MySQL_User
PASSWORD=Your_MySQL_Password

# Store file name

file="$1"

# make sure file exist and readable

if [ ! -f $file ]; then

   echo "$file : does not exist"

   exit 1

elif [ ! -r $file ]; then

   echo "$file: not readable"

   exit 2

fi

# read one line of file into $line variable

while read -r line

do

   # set the field separator to a “,” character

   # and read in five fields assigning each field

   # to $f1, $f2, $f3, etc for five fields  

   IFS=, read -r f1 f2 f3 f4 f5 <<<"$line"

   

     # execute a mysql select using $f1 (customer email)

     # to get the customer ID and assign it to $result

     # The “-N –r –B” prevents MYSQL from outputting header

     # information that we would have to strip out anyway

    result=$(

        mysql -N -r -B -u $USER –p$PASSWORD db_name << eof

           select customer_id from customer WHERE 

           email = '$f1'

        eof

    )

    # let’s echo it just so we can see our progress

    echo $result

       someCommand argument=$result;

    sleep 3

done <"$file"

exit 0

This method can be used to perform any kind of query against the database.  I have some examples of doing something similar from within a DOS batch file here.