current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.
up vote 3 down vote favorite
1

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?

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

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
share|improve this answer
2  
Can't you just parse it directly? Like mysqlcommand | while read URL; do ... ; done ? Why use a temp file? –  terdon Jan 29 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 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 at 22:24
    
sorry typo - have removed the /. –  suspectus Jan 29 at 22:26
    
Thanks for the answer. It works. I have accepted it as the answer. –  Ramesh Jan 29 at 22:35
add comment
up vote 3 down vote

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
share|improve this answer
    
@suspectus facepalm! Of course, there will be spaces in the output. Thanks. –  terdon Jan 29 at 22:30
    
Thanks for the updated answer. This one also works. –  Ramesh Jan 29 at 22:35
    
@terdon - nice solution. –  suspectus Jan 29 at 22:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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