Tag Archives: INTERVAL

Linux

Loop in bash file for mysql

Leave a comment

By davidm123SED

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.


#!/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

Source: FULL ARTICLE at The UNIX and Linux Forums

Linux

Backtick and escapes

Leave a comment

By davidm123SED

Hello all,
I have a problem with a bash script. It contains an MySQL query, which when I run it ‘as is’, executes without a problem.

When, however, I try to get it to assign its output to a variable, using the backtick, I get errors.

So ..

Code:
/usr/bin/mysql -N -B mydatabase -e 'SELECT COUNT(*)
FROM orders as o JOIN users as u ON o.userthing=u.package
WHERE o.table BETWEEN (now() - INTERVAL 1 day) AND
now() - INTERVAL 25 hour) AND o.p_stateorder=1 AND
u.name != "The Site" AND u.p_username NOT LIKE "myAddress@mailserver.com";'

.. no problem.

When, however …

Code:
result=`/usr/bin/mysql -N -B mydatabase -e 'SELECT COUNT(*)
FROM orders as o JOIN users as u ON o.userthing=u.package
WHERE o.table BETWEEN (now() - INTERVAL 1 day) AND
now() - INTERVAL 25 hour) AND o.p_stateorder=1 AND
u.name != "The Site" AND u.p_username NOT LIKE "myAddress@mailserver.com";'`

.. errors.

So I’m guessing that something in there needs to be escaped, but I can’t say what.

Can someone help?

Thanks.

Source: FULL ARTICLE at The UNIX and Linux Forums

Linux

[Solved] Backtick and escapes

Leave a comment

By davidm123SED

Hello all,
I have a problem with a bash script. It contains an MySQL query, which when I run it ‘as is’, executes without a problem.

When, however, I try to get it to assign its output to a variable, using the backtick, I get errors.

So ..

Code:
/usr/bin/mysql -N -B mydatabase -e 'SELECT COUNT(*)
FROM orders as o JOIN users as u ON o.userthing=u.package
WHERE o.table BETWEEN (now() - INTERVAL 1 day) AND
now() - INTERVAL 25 hour) AND o.p_stateorder=1 AND
u.name != "The Site" AND u.p_username NOT LIKE "myAddress@mailserver.com";'

.. no problem.

When, however …

Code:
result=`/usr/bin/mysql -N -B mydatabase -e 'SELECT COUNT(*)
FROM orders as o JOIN users as u ON o.userthing=u.package
WHERE o.table BETWEEN (now() - INTERVAL 1 day) AND
now() - INTERVAL 25 hour) AND o.p_stateorder=1 AND
u.name != "The Site" AND u.p_username NOT LIKE "myAddress@mailserver.com";'`

.. errors.

So I’m guessing that something in there needs to be escaped, but I can’t say what.

Can someone help?

Thanks.

Source: FULL ARTICLE at The UNIX and Linux Forums