Modifying bash script including MySQL query to determine if query returns content or no result

user_15352489 0
Viewed 224

Hope someone can help.

I have an existing bash script that sends a weekly email listing any new documents added to my CMS:

#!/bin/bash
DATE=$(date +"%d-%b-%Y")
RECIPIENTS=***@***.com
MAILBODY=$(</root/bin/mailbody.txt)
BCC="***@***.com"
SENDER="***@***.com"
SUBJECT="New Documents Released Week Ending "
GREETING="Good morning,"
MAILBODY=$MAILBODY
#MAILBODY="The following content has been added or updated in the Content Management System in the last 7 days (if no files are listed, it means there have been no updates):"
CLOSEBODY="This is an automated email sent by the **** Content Management System (https://***.****.***:****). If you have any problems accessing the content listed in this email, please email ***@****.com"

echo "FROM:" $SENDER > /root/bin/mailtext
echo "TO:" $RECIPIENTS >> /root/bin/mailtext
echo "BCC:" $BCC >> /root/bin/mailtext
echo "SUBJECT:" $SUBJECT $DATE >> /root/bin/mailtext
echo "" >> /root/bin/mailtext
echo $GREETING >> /root/bin/mailtext
echo "" >> /root/bin/mailtext
echo $MAILBODY >> /root/bin/mailtext
echo "" >> /root/bin/mailtext
echo "SELECT name AS NAME,MIN(id) as ID,MIN(created_at) as CREATED,MIN(CONCAT('https://***.***.com:****/link/',id)) as URL from pages WHERE deleted_at IS NULL AND name NOT LIKE 'AO%' AND name NOT LIKE 'KB%' AND name NOT LIKE 'Odoo%' AND name NOT LIKE 'WK%' AND draft=0 AND created_at > DATE_SUB(NOW(), INTERVAL 7 DAY) GROUP BY name \G" | mysql -u root -D  bookstack >> /root/bin/mailtext
echo "" >> /root/bin/mailtext
echo "" >> /root/bin/mailtext
echo $CLOSEBODY >> /root/bin/mailtext

cat /root/bin/mailtext | /usr/sbin/sendmail -t

However, I want to personalise this script a little more, so that if the query returns no results, it will change the body text (contained in $MAILBODY) of the email to something like:

There have been no new documents added to the CMS in the last 7 days.

I have tried so many different things to try and achieve what I want, but failed miserably (I know very little about bash and even less about SQL!). I have tried to create an IF, THEN, ELSE construct, but I cannot seem to find a way to detect when the query returns no results. The last attempt I had was this, but obviously it doesn't work:

TEST=$(echo "SELECT IFNULL(SELECT name AS NAME,MIN(id) as ID,MIN(created_at) as CREATED,MIN(CONCAT('https://***.****.***:****/link/',id)) as URL from pages WHERE deleted_at IS NULL AND name NOT LIKE 'AO%' AND name NOT LIKE 'KB%' AND name NOT LIKE 'Odoo%' AND name NOT LIKE 'WK%' AND draft=0 AND created_at > DATE_SUB(NOW(), INTERVAL 1 DAY) GROUP BY name \G")" | mysql -u root -D  bookstack) 
IF (echo "IS NULL $TEST") THEN
$MAILBODY = "There have been no new documents added to the CMS in the last 7 days."
fi

Can anyone help me modify my script to achieve what I need?

All help gratefully received!

Mark

linux mysql sql bash
1 Answers

Get the output of the SQL query into a variable, v.gr. SQLQUERY. Test the string contained by that variable and execute a command according to the result

So, try:

#!/bin/bash
DATE=$(date +"%d-%b-%Y")
RECIPIENTS=***@***.com
MAILBODY=$(</root/bin/mailbody.txt)
BCC="***@***.com"
SENDER="***@***.com"
SUBJECT="New Documents Released Week Ending "
GREETING="Good morning,"
MAILBODY=$MAILBODY
#MAILBODY="The following content has been added or updated in the Content Management System in the last 7 days (if no files are listed, it means there have been no updates):"
CLOSEBODY="This is an automated email sent by the **** Content Management System (https://***.****.***:****). If you have any problems accessing the content listed in this email, please email ***@****.com"
SQLQUERY=$(echo "SELECT name AS NAME,MIN(id) as ID,MIN(created_at) as CREATED,MIN(CONCAT('https://***.***.com:****/link/',id)) as URL from pages WHERE deleted_at IS NULL AND name NOT LIKE 'AO%' AND name NOT LIKE 'KB%' AND name NOT LIKE 'Odoo%' AND name NOT LIKE 'WK%' AND draft=0 AND created_at > DATE_SUB(NOW(), INTERVAL 7 DAY) GROUP BY name \G" | mysql -u root -D  bookstack)

echo "FROM:" $SENDER > /root/bin/mailtext
echo "TO:" $RECIPIENTS >> /root/bin/mailtext
echo "BCC:" $BCC >> /root/bin/mailtext
echo "SUBJECT:" $SUBJECT $DATE >> /root/bin/mailtext
echo "" >> /root/bin/mailtext
echo $GREETING >> /root/bin/mailtext
echo "" >> /root/bin/mailtext
echo $MAILBODY >> /root/bin/mailtext
echo "" >> /root/bin/mailtext
if [ -z "$SQLQUERY" ] ; then # SQLQUERY contains an empty string.
  echo "There have been no new documents added to the CMS in the last 7 days." >> /root/bin/mailtext
else
  echo "$SQLQUERY" >> /root/bin/mailtext
fi 
echo "" >> /root/bin/mailtext
echo "" >> /root/bin/mailtext
echo $CLOSEBODY >> /root/bin/mailtext

cat /root/bin/mailtext | /usr/sbin/sendmail -t

Since you don't provide a minimal working example as usual on SO, I cannot debug the code that I provide you. It is possible that you will have to adapt "if" test above in function of your configuration.

user_17827820
Related