12

Im new in bash scripting. I want to save sql-query outputs in variable, but

actually I must connect for every query to mysql with:

mysql -u $MYUSER -p$MYPASS -D database

and want to save every output in seperatly variable

sample query is: SELECT domain FROM domains WHERE user='$USER'

to

$variable1 = FIRST_OUTPUT
$variable2 = 2ND_OUTPUT

thank you

Yang
  • 7,712
  • 9
  • 48
  • 65
mostworld77
  • 427
  • 4
  • 7
  • 27

3 Answers3

28

Taken from bash script - select from database into variable, you can read the query result into a variable.

Example

mysql> SELECT * FROM domains;
+-------+---------+
| user  | domain  |
+-------+---------+
| user1 | domain1 |
| user2 | domain2 |
| user3 | domain3 |
+-------+---------+

Usage

$ myvar=$(mysql -D$MYDB -u$MYUSER -p$MYPASS -se "SELECT domain FROM domains")
$ echo $myvar
domain1 domain2 domain3

echo is the bash command for output. You can then split $myvar into separate variables:

$ read var1 var2 var3 <<< $myvar
$ echo $var1
domain1
$ echo $var2
domain2

You can combine these two commands into a single one:

read var1 var2 var3 <<< $(mysql -D$MYDB -u$MYUSER -p$MYPASS -se "SELECT domain FROM domains")

It is possible to store the results into arrays (useful if you don't know how many records there):

$ read -ra vars <<< $(mysql -D$MYDB -u$MYUSER -p$MYPASS -se "SELECT domain FROM domains")
$ for i in "${vars[@]}"; do
$     echo $i
$ done
domain1
domain2
domain3
Community
  • 1
  • 1
Yang
  • 7,712
  • 9
  • 48
  • 65
2

Another way of doing is:

dbquery=`mysql -D$MYDB -u$MYUSER -p$MYPASS -se "SELECT domain FROM domains"`      
dbquery_array=( $( for i in $dbquery ; do echo $i ; done ) )

The first line stores all the output from the query in a varriable dbquery in a array-like-way. The second line converts the dbquery into an array dbquery_array with a simple for loop.

3kstc
  • 1,871
  • 3
  • 29
  • 53
2

I did this

variable=mysql -u root -ppassworrd database << EOF select MAX(variable) AS a from table where variable2 = 'SOMETEXT' AND day(datevalue) >= 22; EOF

I hope it helps

Jorge Magos
  • 21
  • 1