Fetch data from MySQL, send cURL requests and use the API results to update the DB.

mysql_update_data.sh

#!/bin/bash

urlencode() {
    # urlencode <string>
    # from https://gist.github.com/cdown/1163649
 
    local length="${#1}"
    for (( i = 0; i < length; i++ )); do
        local c="${1:i:1}"
        case $c in
            [a-zA-Z0-9.~_-]) printf "$c" ;;
            *) printf '%%%02X' "'$c"
        esac
    done
}

MYSQL_HOST=''
MYSQL_USER=''
MYSQL_PASSWORD=''
MYSQL_DATABASE=''

while read data
do
    IFS="," read -a row <<< "$data"

    row_id=$(urlencode "${row[0]}")
    column1=$(urlencode "${row[1]}")
    column2=$(urlencode "${row[2]}")

    post_data="column1=${column1}&column2=${column2}"

    api_result=$(curl -XPOST -s http://api.webapp.net/resource --data "${post_data}")
    grep 'status": "ok' <<< ${api_result} # or check the HTTP status if you are using a real REST service :p
    
    if [ $? -ne 0 ]; then
        echo "##################################"
        echo "The API returned an error"
        echo ${api_result}
        echo "##################################"
        echo ""
    else
        data=($(echo ${api_result} | sed 's/[{}" ]//g' | cut -d, -f2,3 | sed 's/[a-z:]//g' | sed 's/,/ /g'))
        query="UPDATE my_table SET column1 = '${data[0]}', column2 = '${data[1]}' WHERE id = '${row_id}';"
        mysql -h ${MYSQL_HOST} -u ${MYSQL_USER} -p${MYSQL_PASSWORD} -D ${MYSQL_DATABASE} -e "${query}"
    fi

done < <(mysql -B -N -s -h ${MYSQL_HOST} -u ${MYSQL_USER} -p${MYSQL_PASSWORD} -D ${MYSQL_DATABASE} -e "SELECT
    ta.some_column as column1,
    ta.another_column as column2
FROM
    some_table ta
WHERE
    ta.created_at BETWEEN SUBDATE(CURDATE(), INTERVAL 30 DAY) AND CURDATE();" | sed "s/$(printf '\t')/,/g")