Processing MySQL result in bash

Question

I'm currently having a already a bash script with a few thousand lines which sends various queries MySQL to generate applicable output for munin.

Up until now the results were simply numbers which weren't a problem, but now I'm facing a challenge to work with a more complex query in the form of:

$ echo "SELECT id, name FROM type ORDER BY sort" | mysql test
id      name
2       Name1
1       Name2
3       Name3

From this result I need to store the id and name (and their respective association) and based on the IDs need to perform further queries, e.g. SELECT COUNT(*) FROM somedata WHERE type = 2 and later output that result paired with the associated name column from the first result.

I'd know easily how to do it in PHP/Ruby , but I'd like to spare to fork another process especially since it's polled regularly, but I'm complete lost where to start with bash.

Maybe using bash is the wrong approach anyway and I should just fork out?

I'm using GNU bash, version 3.2.39(1)-release (i486-pc-linux-gnu).

Answer 1

My example is not Bash, but I'd like to point out my parameters at invoking the mysql command, they surpress the boxing and the headers.

#!/bin/sh

mysql dbname -B -N -s -e "SELECT * FROM tbl" | while read -r line
do
  echo "$line" | cut -f1   # outputs col #1
  echo "$line" | cut -f2   # outputs col #2
  echo "$line" | cut -f3   # outputs col #3
done