#!/bin/bash

MYSQL_USER="speedtest"
MYSQL_PASSWORD="hunter2"
MYSQL_DATABASE="speedtest_data"

# run the speedtest
speedtest=$(speedtest-cli --simple)

# check if successfull
if [ $? -eq 0 ]; then
    # extract the data we got
    ping=$(echo "$speedtest" | sed -n 1p | sed 's|[^0-9.]||g')
    download=$(echo "$speedtest" | sed -n 2p | sed 's|[^0-9.]||g')
    upload=$(echo "$speedtest" | sed -n 3p | sed 's|[^0-9.]||g')

    #insert into mysql
    echo "INSERT INTO speedtest (PING,DOWNLOAD,UPLOAD,TIME) VALUES ($ping, $download, $upload, NOW());" | mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" "$MYSQL_DATABASE"
else
    echo "speedtest error"
fi