Loop in bash file for mysql Post: 302763555

Sponsored Content

Top Forums Programming Loop in bash file for mysql Post 302763555 by davidm123SED on Wednesday 30th of January 2013 06:22:58 AM

01-30-2013

Registered User

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

davidm123SED

View Public Profile for davidm123SED

Find all posts by davidm123SED

10 More Discussions You Might Find Interesting

1. 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...

2. 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...

3. 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...

4. 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)...

5. 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...

6. 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...

7. 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...

8. 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...

9. 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...

10. 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...

LEARN ABOUT DEBIAN

gretl

gretl(1)							   econometrics 							  gretl(1)

NAME

       gretl - GNU Regression, Econometrics and Time-series Library

SYNOPSIS

       gretl [ options ] [ datafile | scriptfile | sessionfile ]

DESCRIPTION

       gretl  is  an econometrics package, built around a shared library which may be accessed using a command-line client program (gretlcli) or a
       graphical user interface (gretl_x11).  The program gretl is a wrapper script that calls the GUI if the DISPLAY environment variable is set,
       otherwise the command-line client.

       For  details,  please  start up either the GUI or the command-line client and consult the online help, or read the printed manual, which is
       available in PDF format via the gretl website, http://gretl.sourceforge.net.

OPTIONS

       These are the options supported by the gretl_x11 program; gretlcli is a little different.

       -r script, --run script

	      Open a script.

       -c, --dump

	      Dump the configuration file.

       -d database, --db database

	      Open a local database.

       -e, --english

	      Force use of English.

       -r script, --run script

	      Open a script.

       -w database, --webdb database

	      Open a remote database.

       -h, --help

	      Give help information and exit.

       -v, --version

	      Give version information and exit.

FILES

       ~/.gretl2rc per-user configuration file

       ~/.gretl directory for user runtime files

ENVIRONMENT VARIABLES

       HOME   Specifies the user's home directory

       DISPLAY
	      Specifies the X window display to use

SEE ALSO

       gretlcli(1)

AUTHOR

       The gretl team

The gretl team							       1.9.5								  gretl(1)