parse mysql result line by line and convert every line to array - bash script

Question

I'm using bash scripts and for some reasons I must only use bash and not the php or perl and etc.

I got some results from mysql with this code and saved them in a variable called result:

result=$(mysql -NB -u "$mysqlUser" -p"$mysqlPass" -D "$mysqlDb" -e "select * from sites" )

and this is the result:

1 news.bitcoin.com    NULL 840119 stopped 1509636516 1509636688 0 0 0 0
2 blog.blockchain.com NULL NULL   stopped NULL       0          0 0 0 0

now I want to parse this results line by line and convert each line to an array and access them for example in this way:

echo $results[0,0] # 1
echo $results[0,3] # 840119
echo $results[0,6] # 1509636688
echo $results[1,1] # blog.blockchain.com
echo $results[1,3] # NULL
echo $results[1,6] # 0

accourding to this post we can declare arrays through andices like multidimensional.

Would you please help me with this issue, or inform me of any other possible ways to walk through this task?

Thanks in advance

@Inian yes I know. but you can declare arrays via andices like multidimensional. for example: `test[0,1,2]=blabla test[0,1,3]=blabla2` and so on ... see this: [https://stackoverflow.com/a/31468914/3539776](https://stackoverflow.com/a/31468914/3539776) — saeid ezzati, Jul 02 '18 at 10:01
So why couldn't you use it and solve your use case, what is stopping you? — Inian, Jul 02 '18 at 10:11
@Inian I don't know how iterate result variable line by line and how merge this code with code of converting line to array !! Im noob yet — saeid ezzati, Jul 02 '18 at 10:15
Is that dataset going to always be small enough not to cause problems? are you sure there will never be embedded field separators? What are you trying to accomplish with this? — Paul Hodges, Jul 02 '18 at 18:42
@PaulHodges I use `-NB` switch in `mysql` which prevent from any extra characters in output result of `mysql` and that's while there is not any separetor in `mysql` result — saeid ezzati, Jul 03 '18 at 08:23

score 0 · Answer 1 · answered Jul 02 '18 at 19:17

0

Ignoring the paranoia that fields are going to get mismatched, you could try just walking through the data and assigning it by brute force to parallel arrays.

row=0
declare -a A B C D 
mysql -NB -u "$mysqlUser" -p"$mysqlPass" -D "$mysqlDb" -e "select * from sites" |
while read a b c d # ...
do (( row++ ))
   A[$row]="$a"
   B[$row]="$b"
   C[$row]="$c"
   D[$row]="$d"
done

Please use better names than a,b,c... but then, assuming field 2 is fqdn, you'd be able to say

echo ${FQDN[2]}

to quickly grab that field from that row. The whole table becomes immediately accessible...but one misformatted line is going to shoot you in the foot.

answered Jul 02 '18 at 19:17

Paul Hodges

8,723
1
12
28

your solution works when number of mysql result lines are static. for example only 4 lines. but my result lines is variable not static. – saeid ezzati Jul 03 '18 at 08:26
`row` is dynamically incremented for however many lines there are. It does expect a consistent number of columns, but every line, however many, gets parsed to fields as it comes it. How is this only going to work on 4 lines? – Paul Hodges Jul 03 '18 at 13:41
sorry I was wrong. I tested your code. but when I did `echo`, didn't show anything to me . `echo ${A[0]}` and `echo ${A[1]}` and `echo ${B[0]}` and `echo ${A[1]}` all were empty – saeid ezzati Jul 04 '18 at 08:43
What's the output if you pipe that mysql command through a head? – Paul Hodges Jul 05 '18 at 14:48

lojza · Answer 2 · 2018-07-03T12:39:42.563

0

Convert your output to csv - check How to output MySQL query results in CSV format? One (not nice) way is:

csv=`echo "${result}" | tr '\t' ','`

Then do the magic:

declare -A output 
IFS=$'\r\n' GLOBIGNORE='*' command eval  'lines=($(echo "${csv}"))'        
for (( i=0; i<${#lines[@]}; i++ ));
do
  IFS=$',' GLOBIGNORE='*' command eval  'oneline=($(echo "${lines[$i]}"))'
  for (( j=0; j<${#oneline[@]}; j++ ));
  do
    output[$i,$j]=${oneline[$j]}
  done
done

And finally

echo "${output[0,0]}" # 1
echo "${output[0,3]}" # 840119
echo "${output[0,6]}" # 1509636688
echo "${output[1,1]}" # blog.blockchain.com
echo "${output[1,3]}" # NULL
echo "${output[1,6]}" # 0

Be aware of all double quotes and curly brackets.

Thanks to https://stackoverflow.com/a/11393884/2235381 and https://stackoverflow.com/a/22432604/2235381

edited Jul 03 '18 at 12:39

answered Jul 03 '18 at 08:32

lojza

1,351
1
10
21

I tested your solution. but all of `$output` blocks were empty and didn't show me any values – saeid ezzati Jul 03 '18 at 09:10
Sorry case mishmash. Try now – lojza Jul 03 '18 at 12:40
it didn't work again. value of `${output[0,0]}` is `1 news.bitcoin.com NULL 840119 stopped 1509636516 1509636688 0 0 0 0` and value of `${output[1,0]}` is `2 blog.blockchain.com NULL NULL stopped NULL 0 0 0 0 0` and rest of array blocks are empty – saeid ezzati Jul 04 '18 at 07:24

saeid ezzati · Accepted Answer · 2018-07-04T09:14:50.790

finally my issue solved in this way:

declare -A results
row=0
while read -r line; do
    if [[ -z "${line// }" ]]; then 
        continue
    fi        

    declare -a temp2=($line)


    for (( col=0; col<${#temp2[@]}; col++ ));
    do

       results[$row,$col]=${temp2[$col]}

    done

    results[cols]=$(( ${#temp2[@]}-1 ))

    ((row++))
done <<< "$result"
results[rows]=$row

and results:

echo "${results[rows]}" # 2 => array rows count
echo "${results[cols]}" # 10 => array cols count
echo "${results[0,0]}" # 1
echo "${results[0,3]}" # 840119
echo "${results[0,6]}" # 1509636688
echo "${results[1,1]}" # blog.blockchain.com
echo "${results[1,3]}" # NULL
echo "${results[1,6]}" # 0

parse mysql result line by line and convert every line to array - bash script

3 Answers3