Step 28 – Implementing SCO Instances in the Database

I’m going to start working from the bottom of my new code structure upwards starting with the database and the subs.php file (bear with me … this does make sense!).

I’ll start by modifying the table structure for the “scormvars” table in the database by introducing a column for the SCOInstanceID variable. I’ve also taken the opportunity to index the table on SCOInstanceID and varName to make the code run a little faster.

CREATE TABLE scormvars (
  SCOInstanceID int(10) unsigned NOT NULL default '0',
  varName varchar(255) default NULL,
  varValue text,
  KEY SCOInstanceID (SCOInstanceID),
  KEY varName (varName)
) ;

As before, this SQL code is for MySQL. But it shouldn’t be too hard to translate it to another database.

Now, I’ll have to change the database-specific code that I’ve consolidated into the subs.php file. I need to modify the readElement(), writeElement(), clearElement() and initializeElement() functions so that they act only on data that relates to the SCOInstance that is running. Here’s how it looks:

function readElement($varName) {

  global $link;
  global $SCOInstanceID;

  $safeVarName = mysql_escape_string($varName);
  $result = mysql_query("select varValue from scormvars where ((SCOInstanceID=$SCOInstanceID) and (varName='$safeVarName'))",$link);
  list($value) = mysql_fetch_row($result);

  return $value;

}

function writeElement($varName,$varValue) { 

  global $link;
  global $SCOInstanceID;

  $safeVarName = mysql_escape_string($varName);
  $safeVarValue = mysql_escape_string($varValue);
  mysql_query("delete from scormvars where ((SCOInstanceID=$SCOInstanceID) and (varName='$safeVarName'))",$link);
  mysql_query("insert into scormvars (SCOInstanceID,varName,varValue) values ($SCOInstanceID,'$safeVarName','$safeVarValue')",$link);

  return;

}

function clearElement($varName) {

  global $link;
  global $SCOInstanceID;

  $safeVarName = mysql_escape_string($varName);
  mysql_query("delete from scormvars where ((SCOInstanceID=$SCOInstanceID) and (varName='$safeVarName'))",$link);

  return;

}

function initializeElement($varName,$varValue) {

  global $link;
  global $SCOInstanceID;

  // make safe for the database
  $safeVarName = mysql_escape_string($varName);
  $safeVarValue = mysql_escape_string($varValue);

  // look for pre-existing values
  $result = mysql_query("select varValue from scormvars where ((SCOInstanceID=$SCOInstanceID) and (varName='$safeVarName'))",$link);

  // only if nothing found ...
  if (! mysql_num_rows($result)) {
    mysql_query("insert into scormvars (SCOInstanceID,varName,varValue) values ($SCOInstanceID,'$safeVarName','$safeVarValue')",$link);
  }

}

Note how the value of SCOInstanceID is being passed to the functions as a global variable. Next time, I’ll look at how this is passed through the system from the client-side JavaScript API to these server-side PHP functions.