mysql output to an array for easy parsing.

 

Today I was looking for some way to put the output of the mysql output in an array in a bash script. Quick google search yeilded to results something like this:

 

1
output=$(mysql -e "select * from table")

The problem with the above approach is that all the words go into separate index. So if you have a line that has space then that is split into multiple index’s. Not good…

So, I finally came up with something that would not do so and would seperately put each column in each item in the array.

1
2
3
4
5
6
7
8
9
10
IFS="|"
host="hostname"
user="username"

pw="password"
ar=( $(echo 'select id,"| Account ::",account,"| Username ::",username,"| Password ::",password,"| Other Deatils ::",otherdetails,"|END|"  from wp_ak_login' |mysql -D $user -h $host -p$pw -u $user --batch --raw ) )
for i in ${ar[@]}
do
echo $i --
done

 

And if you are a big fan of xml and plan to use xmlstarlet to process the output then you can do the following:

1
2
3
4
5
6
7
8
9
10
IFS="|"
host="hostname"
user="username"

pw="password"
ar=( $(echo 'select id,"| Account ::",account,"| Username  ::",username,"| Password ::",password,"| Other Deatils  ::",otherdetails,"|END|"  from wp_ak_login' |mysql -D $user -h $host  -p$pw -u $user --batch --raw --xml) )
for i in ${ar[@]}
do
echo $i --
done

Hope this will help

Enhanced by Zemanta