Visit Our UNIX and Linux User Community


Loop in bash file for mysql


 
Thread Tools Search this Thread
Top Forums Programming Loop in bash file for mysql
# 1  
Old 01-30-2013
Loop in bash file for mysql

Hi,
I'm having problems with a script which some of you helped me with last week. It's a script to check the status of orders in an SQL database, and if the count(*) is zero, then the script stops. If it's non-zero, the script echos the result to a file and then in cron, I cat the file and mail it.

First thing first, this script is pig-ugly with all of the echos in there, so I need to tidy that up, but more important is the fact that the second part - from where I've inserted ## second part -- just constantly returns the same order code.

Oh, and it loops.

What a mess.

Can someone help me ?

Thanks.


Code:
#!/bin/sh

## get date 

today=`date +"%d-%m-%Y-%H-%S"`

if [ -f /root/extrac.txt ]; then 
	/bin/rm /root/extrac.txt
fi

#### check orders

orderStatus=$(/usr/bin/mysql -N -B mybase -e 'SELECT COUNT(*) FROM orders as o JOIN users as u ON o.userpk=u.pk WHERE o.createdts BETWEEN (now() - INTERVAL 1 day) AND (now() - INTERVAL 4 hour) AND o.p_omsstate=1 AND u.name != "wotsit" AND u.p_username NOT LIKE "e-mail_address@domain.co.uk";')

/bin/echo "" > /root/extrac.txt
/bin/echo "The date is $today" >> /root/extrac.txt
/bin/echo "" >> /root/extrac.txt
/bin/echo "" >> /root/extrac.txt
/bin/echo "We have verified the number of orders still in 'state' state" >> /root/extrac.txt
/bin/echo "after four hours." >> /root/extrac.txt
/bin/echo "" >> /root/extrac.txt

if [ $orderStatus != "0" ]; then
	/bin/echo "" >> /root/extrac.txt
	/bin/echo "" >> /root/extrac.txt
	/bin/echo "The number of orders in this state is not zero." >> /root/extrac.txt
	/bin/echo "" >> /root/extrac.txt
	/bin/echo "As such, the web order numbers are echoed below : " >> /root/extrac.txt
	/bin/echo "" >> /root/extrac.txt

## second part 
orderStatus=$(/usr/bin/mysql -N -B mybase -e 'SELECT Code FROM orders as o JOIN users as u ON o.userpk=u.pk WHERE o.createdts BETWEEN (now() - INTERVAL 1 day) AND (now() - INTERVAL 4 hour) AND o.p_omsstate=1 AND u.name != "wotsit" AND u.p_username NOT LIKE "e-mail_address@domain.co.uk";')

while [ $result ]
do
	/bin/echo "" >> /root/extrac.txt
	/bin/echo "" >> /root/extrac.txt
	/bin/echo "$result" >> /root/extrac.txt
	/bin/echo "" >> /root/extrac.txt
	/bin/echo "" >> /root/extrac.txt
done

exit 0 
fi 

/bin/echo "" >> /root/extrac.txt
/bin/echo "" >> /root/extrac.txt
/bin/echo "The number of orders is zero, and as such, no web order numbers are available" >> /root/extrac.txt
/bin/echo "" >> /root/extrac.txt
/bin/echo "" >> /root/extrac.txt

/bin/echo "Any questions, please e-mail me@myaddress.com" >> /root/extrac.txt
/bin/echo "" >> /root/extrac.txt
/bin/echo "-------------------------------------------------------------" >> /root/extrac.txt
/bin/echo "" >> /root/extrac.txt


Last edited by Scott; 01-30-2013 at 07:30 AM.. Reason: Please use CODE tags not ICODE tags for blocks of code
# 2  
Old 01-30-2013
The first thing I notice is that you do "while [ $result ]", but haven't set $result anywhere - is this present in your environment?

Also, the body of the while[$result] doesn't modify $result, so it will loop forever if $result is true on entry.
# 3  
Old 01-30-2013
Quote:
Originally Posted by JohnGraham
The first thing I notice is that you do "while [ $result ]", but haven't set $result anywhere - is this present in your environment?

Also, the body of the while[$result] doesn't modify $result, so it will loop forever if $result is true on entry.
Yeah, it's a complete mess, isn't it? The problem stems from the fact that I could do this in PhP, but it's a production server and they don't want PhP running on it, and so I have to do it in bash, and have never created arrays (which I think is what's required) in bash.

So here is what I hope is a tidier version - especially devoid of those ugly echo statements.

I think it starts to come unstuck at the first line which executes the first SQL query (the red line). If it comes back with 25 results, then what I want in the evaluation (the magenta line) is basically to say ..

if the first query returns anything other than zero lines, then do this. But I can't figure out which variable to use. ?#, ## and so on .. none works.


Code:
#!/bin/sh

########################################################
#                                                      #
#                       weborders.sh                   #
#                          v. 0.1                      #
#                                                      #
#                   Copyright (C) 2013                 #
#                                                      #
#                                                      #
########################################################



###
# define some important variables 
###

today=`date +"%d-%m-%Y"`
ttime=`date +"%H:%S"`
workingDir="/root"
outPut="$workingDir/webexecOutput.txt"
mysqlExec="/usr/bin/mysql"
database="mydatabase"
query1="SELECT COUNT(*) FROM orders as o JOIN users as u ON o.userpk=u.pk WHERE o.createdts BETWEEN (now() - INTERVAL 1 day) AND (now() - INTERVAL 4 hour) AND o.p_omsstate=1 AND u.name != \"Site Confidence\" AND u.p_username NOT LIKE \"andy.smith@mydatabase.co.uk\";"
query2="SELECT Code FROM orders as o JOIN users as u ON o.userpk=u.pk WHERE o.createdts BETWEEN (now() - INTERVAL 1 day) AND (now() - INTERVAL 4 hour) AND o.p_omsstate=1 AND u.name != \"Site Confidence\" AND u.p_username NOT LIKE \"andy.smith@mydatabase.co.uk \";"

###
# does the output file exist already?  If so, hose it
###

if [ -f $outPut ]; then
   /bin/rm -f $outPut
fi

### 
# let's run the first query 
###

result1=$($mysqlExec --skip-column-names -B $database $query1)

###
# set up beginning of file
# this will be echoed whatever the result of the script
###

/bin/cat << EOF > $outPut
Hello,

This is the weborders script on database.

The date is $today.  It is $ttime GMT. 

We have run the query on the mydatabase database.


EOF

###
# this is the first 'if' clause
# this will be run ONLY if the result of $result1 <> 0
###

if [ $numberResults neq "0" ]; then
	/bin/cat << EOF >> $outPut
There is a non-zero result, and the weborder numbers are echoed below.

EOF
	result2=$($mysqlExec --skip-column-names -B $database $query2)
	for x in "${result2[@]}"; do
		/bin/echo ${x} >> $outPut
	done
	/bin/cat << EOF >> $outPut
If you have any questions, please e-mail myemailaddress@anonexistentserver.com

EOF

fi
exit 0 

/cat/echo << EOF >> $outPut
There are no orders in a 'submitted' state or corresponding to the 
criteria outlined in the LLS.  As such, the script is exiting.

If you have any questions, please e-mail myemailaddress@anonexistentserver.com.

EOF


Last edited by Scrutinizer; 01-30-2013 at 11:59 AM.. Reason: ICODE to CODE tags
# 4  
Old 01-31-2013
Okay, you first need to assess whether or not the mysql query went OK or not. Either "set -e" at the start of the script or deal with it when you run the query.

My MySQL is rusty but I checked and you can't run queries by just appending the query string - I get errors (which end up in my variable) when I do that. In stead, do something like:

Code:
result1=$($mysqlExec --skip-column-names -B $database <<< "$query1")

Then instead of checking for the number of results, check if the string is empty.

You might also want to do a MySQL count statement (as opposed to a query) which may also be much better if the database is large. Additionally, when I do queries like that all my results end up on one line - you might want to append "limit 1" to your query to get just one result.

Hope this helps.

Previous Thread | Next Thread
Test Your Knowledge in Computers #741
Difficulty: Medium
The successor to FORTRAN 77 was informally known as Fortran 90.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Bash Variable scope - while loop while reading from a file

Cope sample1: test.sh i=0 echo " Outside loop i = $i " while do i=$(( $i + 1)) echo "Inside loop i = $i " done echo " Out of loop i is : $i " When run output : Outside loop i = 0 Inside loop i = 1 Inside loop i = 2 Inside loop i = 3 Inside loop i = 4 Inside loop i = 5 Inside... (8 Replies)
Discussion started by: Adarshreddy01
8 Replies

2. UNIX for Beginners Questions & Answers

Using bash script : How to Import data from a dsv file into multiple tables in mysql

HI I have a dsv file that looks like: <<BOF>> record_number|id_number|first name|last name|msisdn|network|points|card number|gender 312|9101011234011|Test Junior|Smith|071 123 4321|MTN|73|1241551413214444|M 313|9012023213011|Bob|Smith|27743334321|Vodacom|3|1231233232323244|M... (4 Replies)
Discussion started by: tera
4 Replies

3. Shell Programming and Scripting

Bash loop to result in one file

Is there a way to use a directory with multiple files (.bam) to create one new file? The below bash loop will create a new file (header.sam) for each of the bam files. However, I only need to use 1 of the bam file to make 1 header file. I attempted a code as well, but not sure if thats... (5 Replies)
Discussion started by: cmccabe
5 Replies

4. Shell Programming and Scripting

Bash loop to search file

In the bash when the user inputs an id to search for the bash currently closes, and if a match is found outputs a new file (match.txt). Is it possible to have not close the bash but rather, on the screen "searching for match" and if a match is found "match found in line.." is displayed... (3 Replies)
Discussion started by: cmccabe
3 Replies

5. Shell Programming and Scripting

Detail on For loop for multiple file input and bash variable usage

Dear mentors, I just need little explanation regarding for loop to give input to awk script for file in `ls *.txt |sort -t"_" -k2n,2`; do awk script $file done which sorts file in order, and will input one after another file in order to awk script suppose if I have to input 2 or... (4 Replies)
Discussion started by: Akshay Hegde
4 Replies

6. Shell Programming and Scripting

(BASH) Using a loop variable to grep something in a file?

Hi, I have a loop running until a variable L that is read previously in the full script. I'd like to grep some information in an input file at a line that contains the value of the loop parameter $i. I've tried to use grep, but the problem is nothing is written in the FILE files. It seems grep... (5 Replies)
Discussion started by: DMini
5 Replies

7. UNIX and Linux Applications

MySQL Daemon failed to start - no mysql.sock file

After doing a yum install mysql mysql-server on Fedora 14 I wasn't able to fully install the packages correctly. It installed MySQL 5.1. I was getting the following error when running the: mysql ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)... (3 Replies)
Discussion started by: jastanle84
3 Replies

8. Shell Programming and Scripting

bash mysql export to file

I'm trying to export a mysql query to a .csv file, right now I'm running a successful query like: us_id=`mysql -u $USER_NAME --password=$PASSWORD -D "databasename" \ -e "SELECT * \ FROM databasename.table \ WHERE somefield >0 AND otherfield ='$ctry' \ ORDER BY... (1 Reply)
Discussion started by: unclecameron
1 Replies

9. Shell Programming and Scripting

Using variables created sequentially in a loop while still inside of the loop [bash]

I'm trying to understand if it's possible to create a set of variables that are numbered based on another variable (using eval) in a loop, and then call on it before the loop ends. As an example I've written a script called question (The fist command is to show what is the contents of the... (2 Replies)
Discussion started by: DeCoTwc
2 Replies

10. Shell Programming and Scripting

How to use while loop in bash shell to read a file with 4 lines of gap

Hi , I am currently using the while loop in bash shell, as follows. while read line do echo $line done < file.txt However, i want to use the while loop on file.txt, which will read the file with 4 lines of gap. Ex- if file.txt is a file of 100 lines, then i want to use the loop such... (3 Replies)
Discussion started by: jitendriya.dash
3 Replies

Featured Tech Videos