mysql output to an array for easy parsing.

2011-05-11 2 min read bash Database

 

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:

 

  <td>
    <div class="text codecolorer">
      output=$(mysql -e "select * from table")
    </div>
  </td>
</tr>
1

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.

  <td>
    <div class="text codecolorer">
      IFS="|"<br /> host="hostname"<br /> user="username"<br /> <br /> pw="password"<br /> 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 ) )<br /> for i in ${ar[@]}<br /> do<br /> echo $i --<br /> done
    </div>
  </td>
</tr>
1
2
3
4
5
6
7
8
9
10

 

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

  <td>
    <div class="text codecolorer">
      IFS="|"<br /> host="hostname"<br /> user="username"<br /> <br /> pw="password"<br /> ar=( $(echo 'select id,"| Account ::",account,"| Username &nbsp;::",username,"| Password ::",password,"| Other Deatils &nbsp;::",otherdetails,"|END|"  from wp_ak_login' |mysql -D $user -h $host &nbsp;-p$pw -u $user --batch --raw --xml) )<br /> for i in ${ar[@]}<br /> do<br /> echo $i --<br /> done
    </div>
  </td>
</tr>
1
2
3
4
5
6
7
8
9
10

Hope this will help

Enhanced by Zemanta
comments powered by Disqus