Using SQL-Databases to pass Information among participants (PHP, MySQL)

Storing information can be done with ordinary files, but also using a database, in particular a SQL-database (see also section 1.3.3). An advantage of using SQL-databases is that several complex tasks can be delegated to the SQL Server. In particular when information that has be stored during an earlier stage of the experiment is accessed again, a database can be a great help.

The following is a simple examle. First one participant has to complete a given poem with a single line. Then a second participant gets the poem plus this line and has to extend it with a second line.

In the example we assume that we access a MySQL-Database. Other databases have very similar access methods.

<HTML>
<BODY>
<?
    // the following poem is to be completed
    // by the two participants
    $poem="To run o'er better waters hoists its sail<BR>
           The little vessel of my genius now,<BR>
           That leaves behind itself a sea so cruel;"

    // we do not store password information in the php-file
    // but rather in a seperate file that is easier to 
    // protect
    require ("header.inc"); // this defines $user and $pass

    // connect to the SQL server:
    if (! mysql_connect("localhost",$user,$pass)) {
        echo "could not connect to SQL-server";
        exit;
    }

    // open the database
    if (! mysql_select_db ("expCrash")) {
        mysql_create_db ("expCrash");
        if (! mysql_select_db ("expCrash")) {
            echo "failed to create database";
            exit;
        }
    }

    // verify existence of the table:
    if (! mysql_query ("SELECT COUNT(*) FROM table1")) {
        mysql_query ("CREATE TABLE table1 (
            vp_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
            line1 TEXT,
            matched INT,
            line2 TEXT)");
    }

    function stimul1 () {
        echo "$poem...<BR>
            <INPUT TYPE=TEXT SIZE=40 NAME=LINE1>
            <INPUT TYPE=SUBMIT VALUE=Continue>";
    }

    function stimul2 ($LINE1) {
        echo "$poem<BR>
            $LINE1 ...<BR>
            <INPUT TYPE=TEXT SIZE=40 NAME=LINE2>
            <INPUT TYPE=SUBMIT VALUE=Continue>";
    }

    function get_line1 () {
        if ($result = mysql_query ("SELECT vp_id,line1 FROM table1  
                     WHERE matched IS NULL LIMIT 1")) {
            if ($row=mysql_fetch_object($result)) { 
                mysql_query ("UPDATE table1 SET matched=1 WHERE vp_id=$row->vp_id");
                echo "<INPUT TYPE=HIDDEN NAME=VP_ID VALUE=$row->vp_id>\n";
                return $row->line1;
            }
        }
        else    
            return "";
    }

    echo "<FORM METHOD=POST ACTION=$SCRIPT_NAME>\n";

    if ($LINE1) {
        $LINE1=strtr($LINE1,"\"","'");
        mysql_query ("INSERT INTO table1 SET line1=\"$LINE1\"");
        echo "Thank you for your participation!<P>";
    }

    else if ($LINE2) {
        $LINE1=strtr($LINE1,"\"","'");
        mysql_query ("UPDATE table1 SET line2=\"$LINE2\" WHERE vp_id=$VP_ID");
        echo "Thank you for your participation!<P>";
    }

    else if ($LINE1 = get_line1 ()) 
        stimul2 ("$LINE1");

    else
        stimul1 ();
?>

</BODY>
</HTML>



Oliver Kirchkamp 2013-04-15