Location: PHPKode > projects > Run > Run-0.8.0.2/Run-0.8.0.2/Install/Upgrade073-08.php
<?php

include("../system/configuration.php");

$connect = mysql_connect($mysqlservername, $mysqlusername, $mysqlpassword) or die(mysql_error());
mysql_select_db($mysqldbname, $connect) or die(mysql_error());


//create table Runners where data about runners is stored
$createRunnersQuery = "create table $mysqlTableRunners (
ID int not null auto_increment primary key,
RunnerID varchar(30) not null,
Password varchar(40) not null,
FirstName varchar(30) not null,
LastName varchar(30) not null,
DateOfBirth date not null,
Gender char(1) not null,
MemberSince date not null,
GroupID varchar(30),
Notes text,
Active char(1) not null,
ViewMode varchar(14),
Language varchar(2),
Measurements varchar(10))";
mysql_query($createRunnersQuery) or die(mysql_error());


//create table RunningLogs where data about runs is stored
$createRunningLogsQuery = "create table $mysqlTableRunningLogs (
ID int not null auto_increment primary key,
RunnerID varchar(30) not null,
Date date not null,
TimeOfDay varchar(10) not null,
Time time not null,
Distance decimal(3,1) unsigned not null,
DistanceUnit char(2),
RunType varchar(15) not null,
Surface varchar(15) not null,
Terrain varchar(15) not null,
Weather varchar(15) not null,
Temperature tinyint(2),
TemperatureUnit char(2),
AHR tinyint(3) unsigned,
Weight tinyint(3) unsigned,
WeightUnit char(2),
Shoes varchar(50),
Feeling varchar(15) not null,
Notes text
)";
mysql_query($createRunningLogsQuery) or die(mysql_error());


//transfers all runner's data from their tables to the newly created table RunningLogs
for ($i = 0; $i < mysql_num_rows(mysql_list_tables($mysqldbname)); $i++) {
//get names of all tables in the Run database
	$names = mysql_tablename(mysql_list_tables($mysqldbname), $i);
//exclude tables Shoes, Runners and RunningLogs
	if ($names != "Shoes" && $names != "RunningLogs" && $names != "Runners") {
//get all data from the old tables for each runner ...
		$pickOldData = mysql_query("select Date, TimeOfDay, Time, Distance, RunType, Surface, Terrain, Weather, Temperature, AHR, Weight, Shoes, Feeling, Notes from $names");
		while ($oldData = mysql_fetch_assoc($pickOldData)) {
//... and insert it into the newly created RunningLogs table
			$insertDataQuery = sprintf("insert into $mysqlTableRunningLogs (RunnerID, Date, TimeOfDay, Time, Distance, DistanceUnit, RunType, Surface, Terrain, Weather, Temperature, TemperatureUnit, AHR, Weight, WeightUnit, Shoes, Feeling, Notes) values ('".$names."', '".$oldData["Date"]."', '".$oldData["TimeOfDay"]."', '".$oldData["Time"]."', '".$oldData["Distance"]."', 'km', '".$oldData["RunType"]."', '".$oldData["Surface"]."', '".$oldData["Terrain"]."', '".$oldData["Weather"]."', '".$oldData["Temperature"]."', 'C', '".$oldData["AHR"]."', '".$oldData["Weight"]."', 'kg', '".$oldData["Shoes"]."', '".$oldData["Feeling"]."', '%s')", mysql_real_escape_string($oldData['Notes']));
			mysql_query($insertDataQuery) or die(mysql_error());
		}
	}
}


//changes column Runner to RunnerID in Shoes table
$runnerIDShoesColumnQuery = "alter table $mysqltableshoes change column Runner RunnerID varchar(30) not null";
mysql_query($runnerIDShoesColumnQuery) or die(mysql_error());


//add user Administrator to the Runners with pass admin, activity set to Y and language to en
$adminQuery = mysql_query("insert into $mysqlTableRunners (RunnerID, Password, Active, Language) values ('Administrator', sha1('admin'), 'Y', 'en')");


//pick whatever data can be picked from the current runners and put that data into Runners table
for ($i = 0; $i < mysql_num_rows(mysql_list_tables($mysqldbname)); $i++) {
//get names of all tables in the Run database
	$names = mysql_tablename(mysql_list_tables($mysqldbname), $i);
//exclude tables Shoes, Runners and RunningLogs
	if ($names != "Shoes" && $names != "RunningLogs" && $names != "Runners") {
//pick the date of first run and hold that date as the day when that runner joined
		$memberSince = mysql_fetch_array(mysql_query("select min(Date) from $names"), MYSQL_NUM);
		$insertRunners = mysql_query("insert into $mysqlTableRunners (RunnerID, MemberSince, Active, ViewMode, Language, Measurements) values ('".$names."', '".$memberSince[0]."', 'N', '10-11111111111', 'en', 'km-C-kg')");
	}
}


echo "Database structure was successfully changed and old data migrated.<br>You can now remove this file.";

?>
Return current item: Run