4

I am using the bash script to connect to the mysql database and execute a query.

I use the below script to connect to the database and execute the query.

#!/bin/bash
Total_Results=$(mysql -h server-name -P 3306 -u username-ppassword -D dbname<<<"select URL  from Experiment where URL_Exists = 1");
for URL in "$Total_Results";
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

The problem is the result set is considered as a one whole result and I am getting inside the else loop only once (we will update the value as 1 from here is printed only once). I have 2500 valid URLs and I expect 2500 echoes of we will update the value as 1 from here.

How can I process each and every row as a single result from mySQL query?

4

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
  • 2
    Can't you just parse it directly? Like mysqlcommand | while read URL; do ... ; done ? Why use a temp file? – terdon Jan 29 '14 at 22:18
  • Yes I'm sure there is an easier way - but when assigning to a shell variable the output is a single line. – suspectus Jan 29 '14 at 22:21
  • I get an error as /tmp_results no such file or directory. However, the file is getting generated as expected. – Ramesh Jan 29 '14 at 22:24
  • sorry typo - have removed the /. – suspectus Jan 29 '14 at 22:26
4

You don't need a variable or a temp file for this, just parse the mysql command directly by piping to the while loop:

#!/bin/bash
mysql <<<"select URL from Experiment where URL_Exists = 1" |

while IFS='\n' 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
  • @suspectus facepalm! Of course, there will be spaces in the output. Thanks. – terdon Jan 29 '14 at 22:30
  • @terdon - nice solution. – suspectus Jan 29 '14 at 22:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.