Help with parsing results from MySQL query in BASH script
# Query the database for Name, Serial Number, Email Contact and Days Offline
HEDS=({$(mysql gn_config --user=myname --password=mypass-s -e "SELECT hed_location, hed_name, hed_email, DATEDIFF(NOW(),xcd_last_seen) AS last_update FROM config.cfg_hs, repository.xml_data WHERE hed_key=xcd_hed_key ORDER BY last_update DESC")})
# The total number of Headends is total number records divided by number of fields
let "tot=${#HEDS[@]}/4"
echo $tot
# Loop through each account record
for (( i=0 ; i<tot ; i++ )); do
# The field ids to work with
let "val1=$i*4"
let "val2=val1+1"
let "val3=val2+1"
let "val4=val3+1"
# Extract the values from the array
SERIAL=${HEDS[$val1]##*/}
NAME=${HEDS[$val2]}
EMAIL=${HEDS[$val3]}
DAYS=${HEDS[$val4]}
Above is part of my bash script, the problem I am having is that the hed_name field in the database has data with spaces in it such as "Town Hall" or "City Center" minus the quotes. When I try to count the fields from the results and then pull the data out of it, each of these names with spaces get counted as 2 separate fields. So if I had one result from my query and the field hed_name was "City Center" it would see 5 fields instead of 4.
Not sure if there is a better way to pull the data out of this query or some way for the hed_name field to only be counted as 1 field regardless of its contents.
Last Comment
ASKER
I will try that here in the next hour and report back. Thanks
ASKER
Ok here is what I tried
1.) Tried
2.) Tried
3.) Tried
Any other ideas?
1.) Tried
'"' || hed_name || '"'
2.) Tried
'"' hed_name '"'
3.) Tried
' hed_name '
Any other ideas?
A little known fact is if you end a MySQL command with \G (Bell) you get a single field per row...
188 ~/bin >mysql -u root -p -e "select 'user','name' from user limit 5\G" mysql
Enter password:
**************************
user: user
name: name
**************************
user: user
name: name
**************************
user: user
name: name
**************************
user: user
name: name
**************************
user: user
name: name
So then parsing this is now trivial...
188 ~/bin >mysql -u root -p -e "select 'user','name' from user limit 5\G" mysql
Enter password:
**************************
user: user
name: name
**************************
user: user
name: name
**************************
user: user
name: name
**************************
user: user
name: name
**************************
user: user
name: name
So then parsing this is now trivial...
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Figured out how to do it on my own, none of the expert comments helped me.
Linux
Linux is a UNIX-like open source operating system with hundreds of distinct distributions, including: Fedora, openSUSE, Ubuntu, Debian, Slackware, Gentoo, CentOS, and Arch Linux. Linux is generally associated with web and database servers, but has become popular in many niche industries and applications.
71K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Open in new window
This would select field hed_name surrounded with double quotes. Don't know if this would work in mysql.