Grant Grant - 1 year ago 46
MySQL Question

Can't select some mysql data and store it to a variable in bash

All of the other variables that make this work are tested and working correctly so I'm obviously doing this wrong.

I have a bash script that first selects some mysql data and stores into a new variable.

Then it goes on to connect again and update the database.

title=$(mysql -u $user -p$pass -h $host dbname | SELECT post_title FROM wp_posts WHERE ID=$8);

mysql --host=$host --user=$user --password=$pass dbname <<EOF
UPDATE wp_my_music_lib SET title = "$title" WHERE track_id=${4}${6};

entry is always blank which says to me that the initial SELECT isn't working properly. It should also be noted that the data expected from the select result has white space and special chars in it ie :

Some Artist (10/10/13)

I thought quoting the var
would fix any potential problems with gobbling but that isn't the issue here as I've tried selecting a single numerical object from a different column and that doesn't work either.

If I hard code the title var it works as expected.

1) Can you see what I'm doing wrong?

2) Is it possible to perform all of the above with one db connection instead as that would make more sense?

Answer Source

mysql | SELECT pipes the output of mysql to a command called SELECT, which is сertainly not what you want.

To execute a query via mysql and capture the output you can use this syntax:

out=$(mysql -B dbname <<< "SELECT post_title FROM wp_posts WHERE ID=$8")

You could also put the SELECT into a subquery and do it with a single SQL-query:

mysql --host=$host --user=$user --password=$pass dbname <<EOF
UPDATE wp_my_music_lib SET title = (
 SELECT post_title FROM wp_posts WHERE ID=$8)
WHERE track_id=${4}${6}