mysql output to an array for easy parsing.

2011-05-11 293 words 2 mins 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>

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

Slides: “Accessing Databases from R” #rstats (r-bloggers.com)
Recover MySQL root Password (nurtya.wordpress.com)
Creating a MySQL database via the command line (rudypurweb.wordpress.com)
Electronic Arts is using Erlang (github.com)

Tags: Baron Schwartz Database InnoDB Marten Mickos mysql Password Percona Server php security Servers SQL injection User (computing) Wordpress

2011/02/09 analyze debug queries output for wordpress
2011/04/11 Easy ssh configuration with .ssh/config file
2011/02/19 Using ssmtp to send mail using gmail.
2010/07/29 Adding a sub-menu in the wordpress admin menu :)
2010/06/25 Using PHP to search a MySQL database and return paged results

Authored By Amit Agarwal

Amit Agarwal, Linux and Photography are my hobbies.Creative Commons Attribution 4.0 International License.

❮ Terminating a SSH session after starting background process. browsing the windows machines and their shares – listing ❯

We notice you're using an adblocker. If you like our webite please keep us running by whitelisting this site in your ad blocker. We’re serving quality, related ads only. Thank you!

I've whitelisted your website.

Not now

Related articles

Related Articles:

Authored By Amit Agarwal