Get MySQL Cell in shell script
mysql, shell
Solution
echo 'select some_column from some_table' | mysql -uusername -ppassword some_db | tail -n+2
Several important things to pay attention to here:
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.
The "tail" call ignores the first output line, which is the column name. Results begin on the second line, thus +2.
The "count rows" bit is similar, except that you say "select count(*) from some_table" instead.
Problem
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: How do I return a set of rows (ideally just a single cell in each row) to a shell script variable? 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?