CREATE TABLE `scores` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `state` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `x` tinyint(11) DEFAULT NULL,
  `y` tinyint(11) DEFAULT NULL,
  `z` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sid` varchar(32) DEFAULT NULL,
  `lasttime` int(11) DEFAULT NULL,
  `team` varchar(4) DEFAULT NULL,
  `model` int(11) DEFAULT NULL,
  `delta` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

<?php

// Use Andy W. Schmeder's library <andy@a2hd.com>
require ('osc.php');

session_start();

$db = mysql_connect("127.0.0.1", "255", "j8qT9qrO") or die(mysql_error());
mysql_select_db("255", $db) or die(mysql_error());

function pointmod($uid, $team, $value) {
        // Ouick routine for updating scores, we'll need to redefine DB resource.

        $dba = mysql_connect("127.0.0.1", "255", "j8qT9qrO") or die(mysql_error());
        mysql_select_db("255", $dba) or die(mysql_error());

        // Give users less points than their team as a whole.
        // Rounding with intval() will mess up scores if changes are less than division factor below.
        $uvalue = intval($value / 3);
        mysql_query(sprintf("UPDATE scores SET score=score+'%s' WHERE name='%s'", $value, $team), $dba);
        mysql_query(sprintf("UPDATE users SET delta=delta+'%s' WHERE sid='%s'", $uvalue, $uid), $dba);
}

// TODO: Purge users from DB older than 5 minutes
$time = time();

$id = session_id();

// Check if session already exists (i.e. we've seen the user before)
if ((mysql_num_rows(mysql_query(sprintf("SELECT * FROM users where sid='%s'", $id), $db))) == 0) {

        // Assign team to new user, even ID is blue, odd ID is red.
        if ((mysql_num_rows(mysql_query("SELECT * FROM users", $db))) == 0) {
                $team = "red";
        }
        elseif ((mysql_num_rows(mysql_query("SELECT * FROM users", $db))) % 2 == 0) {
                $team = "blue";
        }
        else {
                $team = "red";
        }

        // Assign 'model' which corresponds to profile in Pd project
        // OSC path is /public/$model $buttonNumber, e.g. "/public/4 2"
        // Todo: Distribute users evenly, rather than completely at random
        $model = rand(1, 7);

        // Write all we want to store about the user to DB
        mysql_query(sprintf("INSERT INTO users(sid,lasttime,team,model)
                VALUES ('%s','%s','%s','%s')", $id, $time, $team, $model), $db);

} else {
        // Grab data (if user's already been seen) from DB
        $result = mysql_query("SELECT lasttime,team,model FROM users WHERE sid='$id'", $db);
        while($row = mysql_fetch_array($result)) {
                $lasttime = $row['lasttime'];
                $team = $row['team'];
                $model = $row['model'];
        }
}

if ($_POST) {
        // Construct OSC packet for delivery to sound controller
        $c = new OSCClient();
        $c->set_destination("127.0.0.1", 3890);
        $c->send(new OSCMessage("/public/$model", array($_POST['button'])));

        // Each strategy flips beween 'on' and 'off'.
        // Let's find out the status of the selected ($POST) strategy
        // TODO: Bug in SQL statement, if NULL, cannot compare to '0'.
        if (mysql_result(mysql_query(sprintf("SELECT z FROM state WHERE x='%s'
                AND y='%s'", $_POST['model'], $_POST['button']), $db), 0) == '1') {
                mysql_query(sprintf("UPDATE state SET z='0' WHERE x='%s' AND y='%s'",
                        $_POST['model'], $_POST['button']), $db);
        } else {
                mysql_query(sprintf("UPDATE state SET z='1' WHERE x='%s' AND y='%s'",
                        $_POST['model'], $_POST['button']), $db);
        }

        $result = mysql_query("SELECT x, y, z FROM state", $db);
        while($states = mysql_fetch_array($result)) {
                $state[$states['x']][$states['y']] = $states['z'];
        }

        // Matrix comparison.
        // There's probably a better way of defining these 'rules'
        switch ($state[$_POST['model']][$_POST['button']]) {
                case $state['1']['1']:
                pointmod($id, $team, '-10');
                break;

                // And so on ...
        }
}

?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>255</title>
<style type="text/css">
body {
        background-color:#AAA;
        font-family:verdana;
}
#form .button {
        padding: 15px;
        margin: 5px;
}
</style>
</head>
<body>

<div style="padding:20px;margin:3px;width:80px;background-color:#<?php
        if ($team == "blue") {echo ("0000FF"); } else { echo ("FF0000"); } ?>">
<form id="form" name="form" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
  <input type="submit" name="button" id="1" value="1" tabindex="1" class="button" />
  <input type="submit" name="button" id="2" value="2" tabindex="2" class="button" />
  <input type="hidden" name="model" value="<?php echo $model; ?>">
</form>
</div>
<div style="padding:10px;margin:3px;width:80px;font-weight:800;color:#ddd;background-color:#660099;">
        <?php
                // Get team score
                $score = mysql_query(sprintf("SELECT score FROM scores WHERE name='%s'", $team), $db);
                $row = mysql_fetch_row($score) or die(mysql_error());
                echo $row[0];
        ?>
</div>
<div style="padding:10px;margin:3px;width:80px;font-weight:800;color:#ddd;background-color:#990066;">
        <?php
                // Get user (session) score
                $score = mysql_query(sprintf("SELECT delta FROM users WHERE sid='%s'", $id), $db);
                $row = mysql_fetch_row($score) or die(mysql_error());
                echo $row[0];
        ?>
</div>
<div style="padding:10px;margin:3px;width:80px;font-weight:800;color:#ddd;background-color:#669900;">
        <?php
                // Cough up a short session ID (last 5 characters of $id)
                echo substr($id, -5);
        ?>
</div>
</body>
</html>