I need a variable to hold results retrieved from the database. So far this is basically what I'm trying with no success.
myvariable=$(mysql database -u $user -p $password | SELECT A, B, C FROM table_a)
My understanding of bash commands is not very good as you can see.
I don't know much about the MySQL command line interface, but assuming you only need help with the bashing, you should try to either swap the commands around like so:
myvariable=$(echo "SELECT A, B, C FROM table_a" | mysql database -u $user -p$password)
which echos the string into MySQL. Or, you can be more fancy and use some new bash-features (the here string)
myvariable=$(mysql database -u $user -p$password<<<"SELECT A, B, C FROM table_a")
resulting in the same thing (assuming you're using a recent enough bash version), without involving echo.
Please note that the -p$password is not a typo, but is the way MySQL expects passwords to be entered through the command line (with no space between the option and value).
Note that myvariable will contain everything that MySQL outputs on standard out (usually everything but error messages), including any and all column headers, ASCII-art frames and so on, which may or may not be what you want.
EDIT:
As has been noted, there appears to be a -e parameter to MySQL, I'd go for that one, definitely.