Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Jul 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert data to phpMyAdmin mySQL database from Shell Script

    Hi guys, I would like to seek help on inserting data whenever the switch is on or off to my phpMyAdmin mySQL database from my Shell Script. I'm using Raspberry PI as my hardware and I have follow this LINK to create my own Web Control Interface. I have created a mySQL database and insert into my phpMyAdmin. After I implemented insert method to my GPIOServer.sh and execute it, my shell script can't work at all, my LED never light up and never store into mySQL database.

    This is my GPIOServer.sh:
    Code:
    #!/bin/sh
    
    mysqlusername="USERNAME HERE"
    mysqlpassword="PASSWORD HERE"
    
    #Set  Refresh
    echo "How long do you want the wait time to be?"
    read waitTime
    
    #Invoke GPIO
    echo "4" > /sys/class/gpio/export
    echo "17" > /sys/class/gpio/export
    echo "18" > /sys/class/gpio/export
    echo "21" > /sys/class/gpio/export
    echo "22" > /sys/class/gpio/export
    echo "23" > /sys/class/gpio/export
    echo "24" > /sys/class/gpio/export
    echo "25" > /sys/class/gpio/export
    
    #Start Loop
    while :
    do
    #Read MySQL Data
    #Direction
    direction4=$(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword gpio -e "SELECT pinDirection FROM pinDirection WHERE pinNumber='4'";)
    direction17=$(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword gpio -e "SELECT pinDirection FROM pinDirection WHERE pinNumber='17'";)
    direction18=$(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword gpio -e "SELECT pinDirection FROM pinDirection WHERE pinNumber='18'";)
    direction21=$(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword gpio -e "SELECT pinDirection FROM pinDirection WHERE pinNumber='21'";)
    direction22=$(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword gpio -e "SELECT pinDirection FROM pinDirection WHERE pinNumber='22'";)
    direction23=$(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword gpio -e "SELECT pinDirection FROM pinDirection WHERE pinNumber='23'";)
    direction24=$(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword gpio -e "SELECT pinDirection FROM pinDirection WHERE pinNumber='24'";)
    direction25=$(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword gpio -e "SELECT pinDirection FROM pinDirection WHERE pinNumber='25'";)
    #Status
    status4=$(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword gpio -e "SELECT pinStatus FROM pinStatus WHERE pinNumber='4'";)
    status17=$(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword gpio -e "SELECT pinStatus FROM pinStatus WHERE pinNumber='17'";)
    status18=$(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword gpio -e "SELECT pinStatus FROM pinStatus WHERE pinNumber='18'";)
    status21=$(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword gpio -e "SELECT pinStatus FROM pinStatus WHERE pinNumber='21'";)
    status22=$(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword gpio -e "SELECT pinStatus FROM pinStatus WHERE pinNumber='22'";)
    status23=$(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword gpio -e "SELECT pinStatus FROM pinStatus WHERE pinNumber='23'";)
    status24=$(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword gpio -e "SELECT pinStatus FROM pinStatus WHERE pinNumber='24'";)
    status25=$(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword gpio -e "SELECT pinStatus FROM pinStatus WHERE pinNumber='25'";)
    
    #Run Commands
    if [ "$direction4" == "out" ]; then
        echo "out" > /sys/class/gpio/gpio4/direction
        if [ "$status4" == "1" ]; then
            echo "1" > /sys/class/gpio/gpio4/value
            $(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword sensor -e "INSERT INTO sensor values("1","Red LED", dateTime,"")";)
            echo "GPIO 4 Turned On"
        else
            echo "0" > /sys/class/gpio/gpio4/value
            $(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword sensor -e "INSERT INTO sensor values("1","Red LED", "", dateTime)";)
            echo "GPIO 4 Turned Off"
        fi
    else
        echo "in" > /sys/class/gpio/gpio4/direction
    fi
    if [ "$direction17" == "out" ]; then
            echo "out" > /sys/class/gpio/gpio17/direction
        if [ "$status17" == "1" ]; then
                    echo "1" > /sys/class/gpio/gpio17/value
                    $(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword sensor -e "INSERT INTO sensor values("2","Blue LED", dateTime,"")";)
                    echo "GPIO 17 Turned On"
            else
                    echo "0" > /sys/class/gpio/gpio17/value
                    $(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword sensor -e "INSERT INTO sensor values("2","Blue LED", "", dateTime)";)
                    echo "GPIO 17 Turned Off"
            fi
    else
            echo "in" > /sys/class/gpio/gpio17/direction
    fi
    if [ "$direction18" == "out" ]; then
            echo "out" > /sys/class/gpio/gpio18/direction
        if [ "$status18" == "1" ]; then
                    echo "1" > /sys/class/gpio/gpio18/value
                    $(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword sensor -e "INSERT INTO sensor values("3","Green LED", dateTime,"")";)
                    echo "GPIO 18 Turned On"
            else
                    echo "0" > /sys/class/gpio/gpio18/value
                    $(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword sensor -e "INSERT INTO sensor values("3","Green LED", "", dateTime)";)
                    echo "GPIO 18 Turned Off"
            fi
    else
            echo "in" > /sys/class/gpio/gpio18/direction
    fi
    if [ "$direction21" == "out" ]; then
            echo "out" > /sys/class/gpio/gpio21/direction
        if [ "$status21" == "1" ]; then
                    echo "1" > /sys/class/gpio/gpio21/value
                    $(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword sensor -e "INSERT INTO sensor values("4","Yellow LED", dateTime,"")";)
                    echo "GPIO 21 Turned On"
            else
                    echo "0" > /sys/class/gpio/gpio21/value
                    $(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword sensor -e "INSERT INTO sensor values("4","Yellow LED", "", dateTime)";)
                    echo "GPIO 21 Turned Off"
            fi
    else
            echo "in" > /sys/class/gpio/gpio21/direction
    fi
    if [ "$direction22" == "out" ]; then
            echo "out" > /sys/class/gpio/gpio22/direction
        if [ "$status22" == "1" ]; then
                    echo "1" > /sys/class/gpio/gpio22/value
                    $(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword sensor -e "INSERT INTO sensor values("5","LED", "","")";)
                    echo "GPIO 22 Turned On"
            else
                    echo "0" > /sys/class/gpio/gpio22/value
                    $(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword sensor -e "INSERT INTO sensor values("5","LED", "", "")";)
                    echo "GPIO 22 Turned Off"
            fi
    else
            echo "in" > /sys/class/gpio/gpio22/direction
    fi
    if [ "$direction23" == "out" ]; then
            echo "out" > /sys/class/gpio/gpio23/direction
        if [ "$status23" == "1" ]; then
                    echo "1" > /sys/class/gpio/gpio23/value
                    $(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword sensor -e "INSERT INTO sensor values("6","LED", "","")";)
                    echo "GPIO 23 Turned On"
            else
                    echo "0" > /sys/class/gpio/gpio23/value
                    $(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword sensor -e "INSERT INTO sensor values("6","LED", "", "")";)
                    echo "GPIO 23 Turned Off"
            fi
    else
            echo "in" > /sys/class/gpio/gpio23/direction
    fi
    if [ "$direction24" == "out" ]; then
            echo "out" > /sys/class/gpio/gpio24/direction
        if [ "$status24" == "1" ]; then
                    echo "1" > /sys/class/gpio/gpio24/value
                    $(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword sensor -e "INSERT INTO sensor values("7","LED", "","")";)
                    echo "GPIO 24 Turned On"
            else
                    echo "0" > /sys/class/gpio/gpio24/value
                    $(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword sensor -e "INSERT INTO sensor values("7","LED", "", "")";)
                    echo "GPIO 24 Turned Off"
            fi
    else
            echo "in" > /sys/class/gpio/gpio24/direction
    fi
    if [ "$direction25" == "out" ]; then
            echo "out" > /sys/class/gpio/gpio25/direction
        if [ "$status25" == "1" ]; then
                    echo "1" > /sys/class/gpio/gpio25/value
                    $(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword sensor -e "INSERT INTO sensor values("8","LED", "","")";)
                    echo "GPIO 25 Turned On"
            else
                    echo "0" > /sys/class/gpio/gpio25/value
                    $(mysql -B --disable-column-names --user=$mysqlusername --password=$mysqlpassword sensor -e "INSERT INTO sensor values("8","LED", "", "")";)
                    echo "GPIO 25 Turned Off"
            fi
    else
            echo "in" > /sys/class/gpio/gpio25/direction
    fi
    #Complete Loop
    sleep $waitTime
    done
    Hope you guys could help me out with it as soon as possible, thanks in advance, guys.

  • #2
    New to the CF scene
    Join Date
    Jul 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is my sensor mySQL database in phpMyAdmin:
    Code:
    CREATE TABLE IF NOT EXISTS `sensor` (
      `id` int(11) NOT NULL,
      `sensor` text NOT NULL,
      `switchOnLog` datetime NOT NULL,
      `switchOffLog` datetime NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •