I'm using the following BASH mysql statement to get data from my DB.
mysql -N -u root --password=ROOT DB -h 127.0.0.1 -e "SELECT * FROM site where id like '1234'" | while read A B C D E F G H I J K L M N O P Q R S T U V W X Y Z; do
echo $A
echo $B
done
To get all the values for A to Z this seems a long winded way.
How can I do this better and create variables based on the read
value name and results. ?
eg:
create var A which contains value of $A and do this all the way upto var Z containing the value of $Z
This is only an example, the values won't be called A-Z, but have proper names. Is there any way to create variable based on the true proper name and then associate the value to it ?
eg:
mysql -N -u root --password=ROOT DB -h 127.0.0.1 -e "SELECT * FROM site where id like '1234'" | while read site_id site_location site_size etc.......
; do
The mysql
call needs to be piped-in to the while loop this way:
while read -r site_id site_location site_size _ ; do
echo "$site_id" "$site_location" "$site_size"
done < <(
mysql -N -u root --password=ROOT DB -h 127.0.0.1 -e "SELECT * FROM site where id like '1234'"
)
The < <(command)
redirection allows the command
to execute in a sub-shell, but feed the main thread's read
. So when it read -r var1 var2 ... varn < <(command)
, variables are valid for the main thread.
Whereas command | read -r var
has command
to execute in the main thread and read
execute in a sub-shell, and var is only valid within the sub-shell.
This script reads the table description and creates a vars
array containing the column names.
It reads, creates and prints each shell variable of these names using indirection.
#!/usr/bin/env bash
# Pupulate the vars array with the column names from the table
IFS=$'\n' read -r -d '' -a vars < <(
mysql -N -u root --password=ROOT DB -h 127.0.0.1 -e 'SHOW COLUMNS FROM site' |
cut -d ' ' -f1
)
# Output column headers
printf '%s\t' "${vars[@]}"
# read and create variables names matching column names
while read -r "${vars[@]}"; do
for varname in "${vars[@]}"; do
# Use ! indirection to print the value for the variable
printf '%s\t' "${!varname}"
done
done < <(
mysql -N -u root --password=ROOT DB -h 127.0.0.1 -e "SELECT * FROM site where id like '1234'"
)