I want to connect to my DB only once to do multiple SELECT queries as follows:

#Begin Code
query=$(echo "SELECT COUNT(*) from USER_IPTable;
      SELECT User from USER_IPTable;
      SELECT IP_Address from USER_IPTable;" | mysql -u root --password='PASS' MatchingDB)

this will return something like that:

COUNT(*) 3 User user1 user2 user3 IP_Address

I have managed to get the COUNT by using awk as follows:

numberOfUsers=$(echo $query| awk ' { print $2 } ')

my question is how can I use awk to get every word after the word User according to the count and to get every word after the word IP_Address according to the count too?

  • what is the output of this numberOfUsers=$(echo $query| awk ' { print $2 } '), it's not quite clear at least for me :) what you actually want – klerk May 29 '14 at 21:29
  • 3, the number of users, query is defined in the first code @vladeli – Nidal May 29 '14 at 21:30

I would try something like that :

for i in `seq 1 $numberOfUsers`;
    #Will print users
    echo $query | awk ' { print `expr $i + 2` } '
    #Will print ip
    echo $query | awk ' { print `expr $i + $numberOfUsers + 3` } '

The variable NF contains the number of fields, and you can reference a field using a variable by prepending a $ ($i == $4 where i == 4). Then loop over the fields and use a flag variable to know when you have reached each type of value you want:

<your command> | awk '{for (i=1; i<=NF; i++) {if ($i == "User") {type="user"} else if ($i == "IP_Address") {type="ip"}  else if(type == "user") { print "user value = " $i} else if (type == "ip") { print "ip value = " $i}}}'

Use one query and get the results in csv format, that is much cleaner:

SELECT count(*), User, IP_Address from USER_IPTable;

After that you can use awk to parse the results line by line and fill your user and ip arrays or work directly with the data whatever you need to do.

  • Don't know about MySQL but that query would generate a syntax error on most databases – iruvar May 29 '14 at 22:20

If your intent is to list out users and ip addresses and assuming that MYSQL returns one line per record of output you can simply quote $query passed to echo to prevent word-splitting from losing the newlines, and grep out the data of interest from the columnar output

echo "$query" | grep -v -x -e 'COUNT(\*)' -e 'User' -e 'IP_Address' | 
tail -n +2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.