[Solved] Backtick and escapes

Shell Programming and Scripting
1

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

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

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.

2

What kind of errors do you get? Try replacing the backticks with $(...code...)

3 
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";')

Does this help?

4

It does, yes.

I am in your debt, Sir. Thank you!

---------- Post updated at 11:52 AM ---------- Previous update was at 10:05 AM ----------

Can I pick your brains a bit more?

OK, so I got that script to run, and it returns '0' - which is what it should return when everything's OK.

If it returns a non-zero result, I have to run the same SQL request, only ... well, this (note that the variable names are slightly different!) ..

orderStatus=$(/usr/bin/mysql -N -B mydatabase -e 'SELECT ordernumber 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";')

If there is one result to that query, then that's fine. But what if there are several? I tried an array in a bash shell - which is something I've never done, so a lot of googling was required, and came up with .. OK, here is the entire script..

## get date 

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

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

#### check orders

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";')

/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 'submitted' 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

orderStatus=$(/usr/bin/mysql -N -B mydatabase -e 'SELECT ordernumber 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";')

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@mymailaddress.com" >> /root/extrac.txt
/bin/echo "" >> /root/extrac.txt
/bin/echo "-------------------------------------------------------------" >> /root/extrac.txt
/bin/echo "" >> /root/extrac.txt

And then I cat the file and pipe it to mail.

For the moment, that always returns zero, but I'm not entirely sure that the day '$result' is non-zero, that I'm going to get an array.

I could probably do this in PhP, but it's not on the server, and it's a production box, so I can't install it.