gsi February 2016

Store mysql result in a bash array variable

I am trying to store MySQL result into a global bash array variable but I don't know how to do it.

Should I save the MySQL command result in a file and read the file line by line in my for loop for my other treatment?

Example:

#Database, table uses

user password
Pierre aaa
Paul bbb

Command:

$results = $( mysql –uroot –ppwd –se  « SELECT * from users );

I want that results contains the two rows.

Answers


silverdrop February 2016

  1. If you're looking to get a global variable inside your script you can simply assign a value to a varname:

    VARNAME=('var' 'name') # no space between the variable name and value
    

    Doing this you'll be able to access VARNAME's value anywhere in your script after you initialize it.

  2. If you want your variable to be shared between multiple scripts you have to use export:

    script1.sh:

    export VARNAME=('var' 'name')
    echo ${VARNAME[0]} # will echo 'var'
    

    script2.sh

    echo ${VARNAME[1]} # will echo 'name', provided that 
                       # script1.sh was executed prior to this one
    

NOTE that export will work only when running scripts in the same shell instance. If you want it to work cross-instance you would have to put the export variable code somewhere in .bashrc or .bash_profile


F. Hauri February 2016

Mapfile for containing whole table into one bash variable

You could try this:

mapfile result < <(mysql –uroot –ppwd –se  "SELECT * from users;")

Than

echo ${result[0]%$'\t'*}
echo ${result[0]#*$'\t'}

or

for row in "${result[@]}";do
    echo Name:  ${row%$'\t'*}  pass: ${row#*$'\t'}
done

Nota This will work fine while there is only 2 fields by row. More is possible but become tricky

Read for reading table row by row

while IFS=$'\t' read name pass ;do
    echo name:$name pass:$pass
  done  < <(mysql -uroot –ppwd –se  "SELECT * from users;")

Read and loop to hold whole table into many variables:

i=0
while IFS=$'\t' read name[i] pass[i++];do
    :;done  < <(mysql -uroot –ppwd –se  "SELECT * from users;")

echo ${name[0]} ${pass[0]}
echo ${name[1]} ${pass[1]}

Post Status

Asked in February 2016
Viewed 1,294 times
Voted 6
Answered 2 times

Search




Leave an answer