9>>problem-record-mysql

#!/bin/bash
#
# Update_Problem - updates problem record in database
###############################################################
# Determine sql location & set variable
#
MYSQL=`which mysql`" Problem_Task -u testuser"
#
################################################################
#
if [ $# -eq 0 ] #Check if id number was passed.
then
#
# Check if any unfinished records exist.
#
RECORDS_EXIST=`$MYSQL -Bse ‘select id_number from problem_logger where
fixed_date="0000-00-00" or prob_solutions=""‘`
#
#
if [ "$RECORDS_EXIST" != "" ]
then
echo
echo "The following records need updating..."
$MYSQL <<EOF
select id_number,report_date,prob_symptoms
from problem_logger
where fixed_date="0000-00-00" or
prob_solutions=""\G
EOF
fi
echo
echo "What is the ID number for the"
echo -e "problem you want to update?:\c"
read ANSWER
ID_NUMBER=$ANSWER
else
ID_NUMBER=$1
fi
#
################################################################
# Obtain Solution (aka Fixed) Date
#
echo
echo -e "Was problem solved today?[y/n] \c"
read ANSWER
#
case ANSWER in
y|Y|yes|Yes|yEs|yeS|YEs|yES|YeS|YES)
FIXED_DATE=`date +%Y-%m-%d`
;;
*)
#if answer is anything but yes,ask for date
echo
echo -e "What was the date of resolution?[YYYY-MM-DD]\c"
read ANSWER
#
FIXED_DATE=$ANSWER
;;
esac
#
###############################################################
# Acquire problem solution
#
echo
echo -e "Briefly describe the problem solution :\c"
#
read ANSWER
PROB_SOLUTIONS=$ANSWER
#
##############################################################
# Update problem record
#
#
echo
echo "Problem record updated as follows:"
echo
$MYSQL <<EOF
UPDATE problem_logger SET
prob_solutions="$PROB_SOLUTIONS"
fixed_date=$FIXED_DATE
WHERE id_number=$ID_NUMBER
#
select * from problem_logger where id_number=$ID_NUMBER\G
EOF
#
#END