Access element of array

Question

I have the following script which retrieves info from mysql

#!/bin/bash
set -f       
IFS=$'\n'
arr=($(sudo mysql -u root -h localhost -e "USE mydb;SELECT * FROM users"))
for i in "${arr[@]}"
do
   echo "$i}"
done

When I run the previous script I get:

id      firstname       lastname        phonenumber     password        email   ip}
5       User    User    111111111111    Passtest1       testuser@gmail.com      147.236.147.91}

Here is the problem: I want to print one single key value as I run:

#!/bin/bash
set -f

IFS=$'\n'
arr=($(sudo mysql -u root -h localhost -e "USE mydb;SELECT * FROM users"))
echo ${arr[ip]}

Then I get:

id      firstname       lastname        phonenumber     password        email   ip

How can I print id value, which in this case is 5?

Answer 1

First, may I recommend that you run all shell scripts through shellcheck - also available as standalone program on many Linux distributions - to detect syntax errors. The echo $i} e.g. looks like a typo which only "works" because $i is the same as ${i}, and the additional } "only" leads to a stray } appended to the intended output.

Second, there seems to be a misconception on the use of arrays and variables here. You want to print the last column labeled ip from the "actual data line" of your output. Your use of

echo ${arr[ip]}

however would not work because you have neither declared (as in declare -A arr) nor "filled" arr as an associative array, meaning that dereferencing the array like that will not work - the shell will expect a numerical array index here. If you supply a text string (like ip) instead, the behavior will depend on the shell, though it will likely interpret that as a variable name, find that the variable is undefined (i.e. evaluates to the empty string), and then print only the first entry of the array (the first line in your case).

Also, even if you declare the array as associative, the matching of "column titles" to "array indices" will not be automatic; you have to do it manually if you want a targeted acces as you describe it to work.

Third, text processing is in most cases better performed using a text-processing tool such as awk instead of doing it in the shell. Since you want to ignore the header line and print the last column of the second line, I would recommend

sudo mysql -u root -h localhost -e "USE mydb;SELECT * FROM users" | awk 'NR==2{print $7}'

which will process the output of your sudo call invoking the mysql command through an awk program that will only process the second line (condition NR==2) and print the 7th column if that line is encountered.

Fourth, the best option is still to select data using the database tools themselves. I am no SQL expert, but I think a query such as

mysql -u root -h localhost -e "USE mydb; SELECT ip FROM users WHERE id='5'"

should work (but please verify with an uncritical example before production use).