Location: PHPKode > projects > Mozart > Mozart-0.0.4/chgtypes.php
<html>
<body>
<?
// This program should only be executed once, and then it can be deleted from your system.
// You should only have to run this program if you're upgrading from version 0.0f or earlier to version 0.0.1.
//
// This program corrects an error in the system.  The Contact, Phone and Address tables all contain a _type field.
// This correlates to the TYPES table and identifies the contact type (doctor, nurse, etc.), the phone type (home,
// business, etc.) and the address type (home, business, etc.).  The bug arises from the way the fields were
// originally updated.  Rather than insert the type_id into the Contact, Phone or Address _type field, the 
// type_source was inserted.  The problem is only apparent if and when the type_source on the TYPES table is
// changed.  For example, if Doctor is changed to Medical Provider, all contacts with the _type of Doctor would
// effectively be orphaned and become inaccessible.
// 
// This program updates each table with the associated type_id in the _type field to correct the problem.
//
include("connect.inc");
include("reqlogin.inc");

// First, we walk through the CONTACT table
$CntTypeSQL = "SELECT DISTINCT contact_type FROM contact";
$CntType    = mysql_query($CntTypeSQL);

$errno = mysql_errno($db);
$error = mysql_error($db);
if ($errno != 0 ) {
	print "Error $errno: $error";
	exit;
}

while ($myrow = mysql_fetch_array($CntType)) {
	$ctype     = $myrow["contact_type"];
	$TypeDBSQL = "SELECT type_id FROM types WHERE type_source = 'Contact' AND type_name = '$ctype'";
	$TypeDB    = mysql_query($TypeDBSQL);
	$typeid    = mysql_fetch_array($TypeDB);

	$errno = mysql_errno($db);
	$error = mysql_error($db);
	if ($errno != 0 ) {
		print "Error $errno: $error";
		exit;
	}

	$tid = $typeid["type_id"];
	$UpdCntSQL = "UPDATE contact SET contact_type = '$tid' WHERE contact_type = '$ctype'"; 
	$UpdCnt    = mysql_query($UpdCntSQL);

	$errno = mysql_errno($db);
	$error = mysql_error($db);
	if ($errno != 0 ) {
		print "Error $errno: $error";
		exit;
	}
}

// Next, we walk through the ADDRESS table
$AddrTypeSQL = "SELECT DISTINCT address_type FROM address";
$AddrType    = mysql_query($AddrTypeSQL);

$errno = mysql_errno($db);
$error = mysql_error($db);
if ($errno != 0 ) {
	print "Error $errno: $error";
	exit;
}

while ($myrow = mysql_fetch_array($AddrType)) {
	$atype     = $myrow["address_type"];
	$TypeDBSQL = "SELECT type_id FROM types WHERE type_source = 'Address' AND type_name = '$atype'";
	$TypeDB    = mysql_query($TypeDBSQL);
	$typeid    = mysql_fetch_array($TypeDB);

	$errno = mysql_errno($db);
	$error = mysql_error($db);
	if ($errno != 0 ) {
		print "Error $errno: $error";
		exit;
	}

	$tid = $typeid["type_id"];
	$UpdAddrSQL = "UPDATE address SET address_type = '$tid' WHERE address_type = '$atype'"; 
	$UpdAddr    = mysql_query($UpdAddrSQL);

	$errno = mysql_errno($db);
	$error = mysql_error($db);
	if ($errno != 0 ) {
		print "Error $errno: $error";
		exit;
	}
}

// Finally, we go through the PHONE table
$PhoneTypeSQL = "SELECT DISTINCT phone_type FROM phone";
$PhoneType    = mysql_query($PhoneTypeSQL);

$errno = mysql_errno($db);
$error = mysql_error($db);
if ($errno != 0 ) {
	print "Error $errno: $error";
	exit;
}

while ($myrow = mysql_fetch_array($PhoneType)) {
	$ptype     = $myrow["phone_type"];
	$TypeDBSQL = "SELECT type_id FROM types WHERE type_source = 'Phone' AND type_name = '$ptype'";
	$TypeDB    = mysql_query($TypeDBSQL);
	$typeid    = mysql_fetch_array($TypeDB);

	$errno = mysql_errno($db);
	$error = mysql_error($db);
	if ($errno != 0 ) {
		print "Error $errno: $error";
		exit;
	}

	$tid = $typeid["type_id"];
	$UpdPhoneSQL = "UPDATE phone SET phone_type = '$tid' WHERE phone_type = '$ptype'"; 
	$UpdPhone    = mysql_query($UpdPhoneSQL);

	$errno = mysql_errno($db);
	$error = mysql_error($db);
	if ($errno != 0 ) {
		print "Error $errno: $error";
		exit;
	}
}
?>
All tables updated successfully.  Please see the UPGRADE file for further necessary steps.
</body>
</html>
Return current item: Mozart