Bash – Convert thesql count(*) to int in bash

mysql seems to output the results to a shell variable in a single line. One way round this is to write the contents to a temporary file, then process in a while loop.

EDIT On my system IFS="\n" before the mysql command (when the results are assigned to a shell variable) gives the correct multi-line output. e.g.

 IFS="\n"
 Total_results=$(mysql.....)

=============== End of Edit ==========================

#!/bin/bash
mysql --silent -h server-name -P 3306 -u username-ppassword -D dbname<<<"select URL  from Experiment where URL_Exists = 1" > tmp_results

while read URL
do 
   echo $URL
   var=$(curl -s --head $URL | head -n 1 | grep "HTTP/1.[01] [23]..")
   echo "$var"
   if [ -z "$var" ]
   then
     echo "Ok we do not have a valid link and the value needs to be updated as -1 here"
   else
     echo "we will update the value as 1 from here"
   fi
done < tmp_results

output of mysql is tab delimited. column replaces tabs with spaces breaking your input.

#!/bin/bash

while read -r output;
do
    # use double quotes with "$output" to avoid conversion
    # of tabs to spaces and set awk's Field Separator to "\t"
    echo "$output" | awk -F"\t" '{print $4}'
    #do something
done< <(mysql -u root -ptoor super_market -h 0 -e "select * from items;" | sed 1d)

test run:

root@c2:~# mysql -u root -ptoor super_market -h 0 -e "select * from items;"
+---------+------------+--------+-------------+-------+
| item_id | item_name  | weight | brand       | price |
+---------+------------+--------+-------------+-------+
|       1 | Milk       |      2 | Nestle      |     2 |
|       2 | Cheese     |    2.5 | Amul        |     6 |
|       3 | Chips      |     25 | Lays        |     3 |
|       4 | Coke       |      5 | Coke Cola   |   3.5 |
|       5 | Engage     |      5 | Deo         |   3.5 |
|       6 | Engage     |      5 | Deo         |   3.5 |
|       7 | Ear phones |      4 | Skull Candy |  32.3 |
+---------+------------+--------+-------------+-------+
root@c2:~# ./test.sh 
Nestle
Amul
Lays
Coke Cola
Deo
Deo
Skull Candy
root@c2:~#