How to get line count from variable (from MYSQL query)?

Question

My problematic code:

testMYSQL=`mysql -u $mysqlUser -p$mysqlPass -h $mysqlHost --skip-column-names --batch -D $mysqlDB -e "SELECT $select FROM $mysqlTable WHERE nameTXT='test';"`       

$testMYSQL now contains:

test
test
test

Then I do:

TEST=$(echo $testMYSQL | wc -l)
echo "$TEST"

I would of thought that would work, but it doesn't, it returns 1

But if I put this into $testMYSQL: "test\ntest\ntest" it will say 3…

Whats going on here? does MYSQL not use new lines?

PS, I know I can use a for loop to loop though the lines then count up the lines that way, but I was hoping for a simpler solution like wc

Answer 1

use the $() syntax whenever possible

$ testMYSQL=$(mysql -u $mysqlUser -p$mysqlPass -h $mysqlHost --skip-column-names --batch -D $mysqlDB -e "SELECT $select FROM $mysqlTable WHERE nameTXT='test';" )
$ set -- $testMYSQL
$ echo ${#}

also, if you just want a count of how many nameTXT that is "test", why not do it in mysql? something like select count($select) from $mysqlTable where nameTXT='tests';

Answer 2

TEST="$(echo "$testMYSQL" | wc -l)"

or

TEST="$(wc -l <<< "$testMYSQL")"