Get MySQL Cell in shell script

Question

I would like to execute a MySQL command in a shell script/cron job that returns a dynamic number of rows in which I can access a specific field from those rows. I would then like to loop through this performing additional commands on those field entries.

My two questions then are:

1. How do I return a set of rows (ideally just a single cell in each row) to a shell script variable?

2. Could I write a PHP script that returns the information I need and then save this to a shell script variable? If so, how do I run the PHP script from the shell and have it return the information?

Answer 1

You could use a tool like expect to issue a query and get your result via the mysql client: Expect

It's not a shell script, but you could call it from a shell script, the syntax is very simple and there's LOTS of examples on the net for how to use expect.

Answer 2

Can't you just use PHP instead of a shell script? Just run /path/to/php mycron.php in your cronjob instead of the shellscript.

Answer 3

echo 'select some_column from some_table' | mysql -uusername -ppassword some_db | tail -n+2

Several important things to pay attention to here:

1. There cannot be a space between -p and the password. If there is, it assumes you didn't give a password, and prompts you for it interactively. Beware: some OSes let other users see the full command line for commands other users are running, so -ppassword is a security risk. Only do this on machines where users that can log in are all trusted, or on OSes that prevent this sort of snooping, like FreeBSD.

2. The "tail" call ignores the first output line, which is the column name. Results begin on the second line, thus +2.

3. The "count rows" bit is similar, except that you say "select count(*) from some_table" instead.

Answer 4

You could also do something like this, if you need to keep it all in bash:

mysql -b -e "SELECT one_column FROM tables" | {
  read
  while [ ! -z "$REPLY" ]
  do
    # do something useful with the $REPLY variable here
    read
  done
}

(Certain that there's a more efficient way to do that)

Another option, using xargs:

mysql -b -e "SELECT one_column FROM tables" | xargs usefulcommand

where usefulcommand uses the builtin shift function to process all of its command line arguments.