I am try to make a game for my son, it has 2 vibration sensors that represent Balls and Strikes (for baseball). When a sensor is hit I have a loop that adds a tick to either the strike or the ball. When a sensor is hit I want it also to enter into my sqlite db. then I have a php file that reads the sqlite db and displays the balls and strikes as they happen in realtime. I am using php5, sqlite3 and apache2 server. I have the server setup, the python script detects the sensors correctly and creates 2 variables, balls and strikes to. I have the php script linked to the py script and can see a simple "print 'welcome' when I run the php file. My problem is it doesnt seem like either 1. Its not updating the sqlite db or 2. its not retrieving the data correctly in php. Here is my python script for the sensors -

#sensor.py file
#!/usr/bin/env python
import RPi.GPIO as GPIO
import time
import sqlite3

KnockPin = 12 
ShockPin = 13 

BtnPin = 15

inning = 1
runs = 0
strikes = 0
balls = 0

print ("Welcome to Bases Loaded!")

def setstr():
    global strikes
    strikes = 0
    global balls
    balls = 0

def setup():
    GPIO.setup(KnockPin, GPIO.IN, pull_up_down=GPIO.PUD_UP)
    GPIO.setup(ShockPin, GPIO.IN, pull_up_down=GPIO.PUD_UP)
    GPIO.setup(BtnPin, GPIO.IN, pull_up_down=GPIO.PUD_UP)   

def knock(ev=None):

    conn = sqlite3.connect('basesLoaded.db');

    global strikes

    strikes += 1

    if strikes ==0:

    elif strikes == 1:

        conn.execute("UPDATE bl set STRIKE = 1 WHERE ID =1");
        print ("Strike 1 :", conn.total_changes);

    elif strikes == 2:

        conn.execute("UPDATE bl set STRIKE = 2 WHERE ID=1");
        print ("Strike 2 :", conn.total_changes);

    elif strikes == 3:

        conn.execute("UPDATE bl set STRIKE = 0 WHERE ID=1");
        print ("Strike 3 Your Out :", conn.total_changes);

        global inning
        inning +=1

        conn.execute("UPDATE bl set INNING = inning WHERE ID=1");
        print ("Inning Number :", inning);

def shock(ev=None):
    conn = sqlite3.connect('basesLoaded.db');
    print ("Opened database successfully");
    global balls

    balls += 1

    if balls ==0:
    elif balls == 1:

        conn.execute("UPDATE bl set BALL = 1 WHERE ID=1");
        print ("Ball 1 :", conn.total_changes);

    elif balls == 2:

        conn.execute("UPDATE bl set BALL = 2 WHERE ID=1");
        print ("Ball 2 :", conn.total_changes); 

    elif balls == 3:

        conn.execute("UPDATE bl set BALL = 3 WHERE ID=1");
        print ("Ball 3 :", conn.total_changes);

    elif balls == 4:

        global runs
        runs += 1
        conn.execute("UPDATE bl set BALL = 0 WHERE ID=1");
        conn.execute("UPDATE bl set RUN = 'runs' WHERE ID=1");
        print ("Run Scored! score is :", runs); 

def loop():

    if GPIO.add_event_detect(ShockPin, GPIO.FALLING, callback=shock, bouncetime=2500):
        print "Balls"

    elif GPIO.add_event_detect(KnockPin, GPIO.FALLING, callback=knock, bouncetime=2500): # wait for falling
         while True:

if __name__ == '__main__':     # Program start from here

    except KeyboardInterrupt:  # When 'Ctrl+C' is pressed, the child program destroy() will be  executed.

As you can see I have the "update bl" script in the IF ELIF statements that is supposed to update the db row #1 as I want to only have 1 row that gets updated.

Here is my php script to retrieve the sqlite data -

#basesLoaded.php file
$try = exec('python sensor.py');
print_r ($try);
$db = new SQLite3('basesLoaded.db');

echo '<br>';
echo '<br>';
$strikes = ($db->querySingle('SELECT STRIKE FROM bl WHERE ID = 1'));
$balls = ($db->querySingle('SELECT BALL FROM bl WHERE ID = 1'));
$outs = ($db->querySingle('SELECT OUT FROM bl WHERE ID = 1'));
$runs = ($db->querySingle('SELECT RUN FROM bl WHERE ID = 1'));
$inning = ($db->querySingle('SELECT INNING FROM bl WHERE ID = 1'));

echo 'Strikes: ' . $strikes . '<br>';
echo 'Balls: ' . $balls . '<br>';
echo 'Outs: ' . $outs . '<br>';
echo 'Runs: ' . $runs . '<br>';
echo 'Inning: ' . $inning . '<br>';

And here is my script to create the db and create first row -


import sqlite3

conn = sqlite3.connect('basesLoaded.db')
print ("Opened database successfully");

conn.execute('''CREATE TABLE bl
   INNING         INT   ,
   RUN            INT   ,
   OUT            INT   ,
   STRIKE         INT    ,
   BALL           INT   );''')
print ("Table created successfully");

  VALUES (1,1,1,1,1,1)");

print ("Records Created Successfully");

When I run basesLoaded.php I get strike:1, ball:1 etc.. But when it wont update when a sensor gets knocked.. Any ideas? Thank you!

Also when I run the sensor.py script in idle it works great and says that a row is updated whenever a sensor is hit, but thats the only indication that the db is actually getting updated as the values in php do not change.. I would like this to change in the php file dynamically or in realtime if possible..either using ajax or something like that..

  • 1
    This is way too much code to read. Please indicate where in your code you suspect it is the Pi that is not behaving as expected, as well as what tests you have run to try to isolate the problem. – JayEye May 4 '16 at 0:42
  • Essentially my php script is not continually executing the python file.. – Ryan D May 4 '16 at 0:48
  • 1
    This is not an RPi problem, it's a php/python problem. Have you tried asking on php and python fora? – JayEye May 4 '16 at 0:50
  • Yes I have it posted there as well.. Didnt know if the sensors being involved would be my issue in trying to access that info from php.. Php is not continually running the python script which produces the sensor info – Ryan D May 4 '16 at 1:05
  • The sensors can have no effect on either php or python code. – JayEye May 4 '16 at 1:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.