[Stack-cvs] stack-1-0/scripts/install stackUpdateDatabase.php,NONE,1.1

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

Update of /cvsroot/stack/stack-1-0/scripts/install
In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv27032/scripts/install

Added Files:
	stackUpdateDatabase.php 
Log Message:

--- NEW FILE: stackUpdateDatabase.php ---
<html>
<head><title>Update STACK Database utility</title><head>
<body>
<p><b>This script updates the STACK database with new metadata fields. Please enter your mySQL username and password in the following boxes</b></p>
<form action="stackUpdateDatabase.php" method="POST">
	<p>mySQL Username:</p>
	<input type="text" name="userName">
	<p>mySQL Password:</p>
	<input type="password" name="password">
	<br><br>
	<input type="submit" value="Update STACK database" name="submit">
	</form>
<body><html>

<?php
/** This PHP script simply adds new fields to mySQL for metadata. This affects both questions and quizzes
*/
$addQuestionColumnQuery = '
ALTER TABLE stackquestion
	ADD COLUMN questionPublisher tinytext NULL, 
	ADD COLUMN type tinytext NULL,
	ADD COLUMN questionFormat tinytext NULL,
	ADD COLUMN questionLanguage tinytext NULL,
	ADD COLUMN questionRights text NULL,
	ADD COLUMN questionLearningContext tinytext NULL,
	ADD COLUMN questionDifficulty tinytext NULL,
	ADD COLUMN questionCompetency tinytext NULL,
	ADD COLUMN questionCompetencyLevel tinytext NULL,
	ADD COLUMN questionTimeAllocated time NULL,
	ADD COLUMN questionExcerciseType tinytext NULL,
	ADD COLUMN questionDateLastEdited datetime NULL, 
	DROP COLUMN questionDateLastEdited;';

$addQuizColumnQuery = '
ALTER TABLE stackquiz
	ADD COLUMN quizGUID varchar(22) NULL,
	ADD COLUMN quizKeywords tinytext NULL,
	ADD COLUMN quizUserLastEdited int NULL,
	ADD COLUMN quizDateLastEdited datetime NULL,
	ADD COLUMN quizPublisher tinytext NULL,
	ADD COLUMN type tinytext NULL,
	ADD COLUMN quizFormat tinytext NULL,
	ADD COLUMN quizLanguage tinytext NULL,
	ADD COLUMN quizRights text NULL,
	ADD COLUMN quizLearningContext tinytext NULL,
	ADD COLUMN quizDifficulty tinytext NULL,
	ADD COLUMN quizCompetency text NULL,
	ADD COLUMN quizCompetencyLevel text NULL,
	ADD COLUMN quizTimeAllocated time NULL,
	ADD COLUMN quizExcerciseType text NULL;';

//Global mysql connection settings
$stack_mysql['host']    = 'localhost';
$stack_mysql['user']    = '';
$stack_mysql['pswd']    = '';
$stack_mysql['stackdb'] = 'stack';

//Catch a postback event
if (isset($_POST["submit"]))
{	
	if (!empty($_POST["userName"]) && !empty($_POST["password"]))
	{
		$stack_mysql['user']    = $_POST["userName"];
		$stack_mysql['pswd']    = $_POST["password"];     

	stack_mysqlcmd($addQuestionColumnQuery,$stack_mysql['user'],$error);
	stack_mysqlcmd($addQuizColumnQuery,$stack_mysql['user'],$error);

	echo($error);

	}

	else
	{
	echo("Please ensure you have entered your mySQL username and password!");
	}
}

/**
* Used to connect to the MySQL stack database and execute a query.
*
*
* @param string $qu  The query to execute
* @param string $who The MySQL user name
* @param array &$err Array of errors passed by reference
* @return void
*/
function stack_mysqlcmd($qu,$who,&$err)
{
  global $stack_mysql;

   $host     = $stack_mysql['host'];
   $user     = $stack_mysql['user'];
   $password = $stack_mysql['pswd'];
   $db       = $stack_mysql['stackdb']; 

  mysql_connect($host,$user,$password);
  mysql_select_db($db);

  $result = mysql_query($qu);

  echo "User :<b>$who</b><br>
        Database Selected: <b>{$db}</b><br>
        Query: <b>$qu</b>\n<p><b>Results</b></p>\n\n";

  if($result == 0)
  {
     echo "<b>Error ".mysql_errno().": ".mysql_error()."</b>"; 
     $err=TRUE;
  } 
  elseif (@mysql_num_rows($result) == 0) 
  { 
      echo("<b>Query completed. No results returned. </b><br />"); 
  } 
  else 
  { 
     echo "<p><table border=1><thead><tr>\n"; 
     for($i = 0;$i < mysql_num_fields($result);$i++) 
     { 
         echo "<th>".mysql_field_name($result,$i). "</th>"; 
     } 
     echo " </tr></thead><tbody>\n"; 
     for ($i = 0; $i < mysql_num_rows($result); $i++) 
     { 
         echo "<tr>"; 
         $row = mysql_fetch_row($result); 
         for($j =  0;$j<mysql_num_fields($result);$j++) 
         { 
             echo("<td>" . $row[$j] . "</td>"); 
         } 
         echo "</tr>\n"; 
     } 
    echo "</tbody></table></p>\n"; 
  } //end else 
  echo "<hr />\n\n\n";
}
?>