-1

Lets say I have a query first that inserts certain values in a table and the next query is to display the maximum value of one of the columns and then store that in a variable. I then need to display that variable such that it shows the max value. For example:

sudo mysql -u$mysql_user -p$mysql_pwd -h $mysql_host --database $db_name -e "INSERT INTO service_status_batch VALUES ();"
batch_id= sudo mysql -u$mysql_user -p$mysql_pwd -h $mysql_host --database $db_name -e "SELECT MAX(id) as maxid  FROM service_status_batch;"
echo "Value of the id is:" $batch_id

This echo command should then show the value of the variable. However it ends up showing me the value of the select query in the form of a table and not the value of the variable.

Is there a particular way to assign the query value to a variable in shell script?

I have attached the select query value that it shows.

not showing the value of the batch id variable

Community
  • 1
  • 1
Vijit Vengsarkar
  • 21
  • 1
  • 7

2 Answers2

0

Use -s and -N options with mysql command like this.

sudo mysql -u$mysql_user -p$mysql_pwd -h $mysql_host --database $db_name -e "INSERT INTO service_status_batch VALUES ();"
batch_id=`sudo mysql -u$mysql_user -p$mysql_pwd -h $mysql_host --database $db_name -s -N -e "SELECT MAX(id) as maxid  FROM service_status_batch;"`
echo "Value of the id is:" $batch_id

Refer the details for -s and -N :

--silent, -s

   Silent mode. Produce less output. This option can be given multiple
   times to produce less and less output.

   This option results in nontabular output format and escaping of
   special characters. Escaping may be disabled by using raw mode; see
   the description for the --raw option.

--skip-column-names, -N

   Do not write column names in results.
Nishu Tayal
  • 20,106
  • 8
  • 49
  • 101
0

EDIT3: Bad explanation - I was trying to show how to get the value considering it could be used as necessary: 

sudo echo $(echo "SELECT MAX(id) as maxid  FROM service_status_batch" | mysql dbnamehere -uUser -pPassword)

EDIT1: variable version obviously

EDIT2: corrected variable assignment by using shellcheck.net as suggested. thanks.

EDIT3: one last edit to add sudo right before mysql command as it won't work without it for users other than root.

batch_id=$(echo "SELECT MAX(id) as maxid  FROM service_status_batch" | sudo mysql dbnamehere -uUser -pPassword)
smozgur
  • 1,772
  • 1
  • 15
  • 23
  • Edited in 20 seconds to show variable assignment. Send by hoping it helps someone as it helped me. – smozgur Jun 10 '16 at 10:06
  • 1
    The first `sudo echo` adds complexity, unnecessary features, and security woes without actually attempting to solve the OP's problem. But anyway, new visitors are better off if they click through to the duplicate instead of checking the answers here, as they will be properly vetted by many visitors. – tripleee Jun 10 '16 at 10:11
  • @tripleee You are right. I just edited everything as it should be to just keep it right. Perhaps I should delete my answer? – smozgur Jun 10 '16 at 10:17
  • @tripleee Checked your duplicate question's answer too. Didn't work that way either. – Vijit Vengsarkar Jun 10 '16 at 10:22