Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

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

#!/bin/bash
#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 192.168.1.17 192.168.1.24 192.168.1.17

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?

share|improve this question
    
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 at 21:29
    
3, the number of users, query is defined in the first code @vladeli –  Networker May 29 at 21:30

4 Answers 4

up vote 3 down vote accepted

I would try something like that :

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

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}}}'
share|improve this answer

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.

share|improve this answer
    
Don't know about MySQL but that query would generate a syntax error on most databases –  1_CR May 29 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
user1
user2
..
192.1.1.1
192.3.4.5
..
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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