Date conversions...

[pthurmond]

Hey everybody, I am migrating data from an Access database to a MySQL database. Currently I have imported the records I need to a temporary table on the server that I set up. I am trying to move the data to its final table and the date of birth is not transferring for some reason. The dates are currently formatted with "/" (Month/Day/Year format) separating the values. I used str_split() to convert all of the "/" to "-". When copying from one table to the next it never makes it to the new table. I am having this problem with 2 table conversions and 3 fields. However in the second table conversion the second field that is converted (the date applied value) about 5% of the records dates made it through. However the numbers on the ones that make it are re-arranging.

Here is the code:

[code]
<?php
require_once('includes/config.php'); //Connect to the db


echo 'Starting student data conversion...';

$query = "SELECT Center_Code, Sex, Race, School, Last_Name, First_Name, MI, Date_Of_Birth, Address, City, State, Zip, Student_ID, Parents_Name, Home, Emergency_Contact, Phone, Medical_Insurance_Info, Physician_Name, Physician_phone, Hospital_preference, Medical_conditions, Renewal FROM student_export";

$result = mysql_query($query) or die('Query failed: ' . mysql_error());

if($result)
{
$stucount = 1;

while($row = mysql_fetch_array($result, MYSQL_BOTH))
{
$students[$stucount]['Center_Code'] = $row['Center_Code'];
$students[$stucount]['Sex'] = $row['Sex'];
$students[$stucount]['Race'] = $row['Race'];
$students[$stucount]['School'] = $row['School'];
$students[$stucount]['Last_Name'] = $row['Last_Name'];
$students[$stucount]['First_Name'] = $row['First_Name'];
$students[$stucount]['MI'] = $row['MI'];
$students[$stucount]['Date_Of_Birth'] = $row['Date_Of_Birth'];
$students[$stucount]['Address'] = $row['Address'];
$students[$stucount]['City'] = $row['City'];
$students[$stucount]['State'] = $row['State'];
$students[$stucount]['Zip'] = $row['Zip'];
$students[$stucount]['Student_ID'] = $row['Student_ID'];
$students[$stucount]['Parents_Name'] = $row['Parents_Name'];
$students[$stucount]['Home'] = $row['Home'];
$students[$stucount]['Emergency_Contact'] = $row['Emergency_Contact'];
$students[$stucount]['ecPhone'] = $row['Phone'];
$students[$stucount]['Medical_Insurance_Info'] = $row['Medical_Insurance_Info'];
$students[$stucount]['Physician_Name'] = $row['Physician_Name'];
$students[$stucount]['Physician_phone'] = $row['Physician_phone'];
$students[$stucount]['Hospital_preference'] = $row['Hospital_preference'];
$students[$stucount]['Medical_conditions'] = $row['Medical_conditions'];
$students[$stucount]['Renewal'] = $row['Renewal'];

$stucount++;
}

//Divide name data
for ($i = 1; $i <= $stucount; $i++)
{
$pname = $students[$i]['Parents_Name'];
$pname = explode(' ', $pname);
$pfname = $pname[0];
$plname = $pname[1];

$ecname = $students[$i]['Emergency_Contact'];
$ecname = explode(' ', $ecname);
$ecfname = $ecname[0];
$eclname = $ecname[1];

$students[$i]['pfname'] = $pfname;
$students[$i]['plname'] = $plname;
$students[$i]['ecfname'] = $ecfname;
$students[$i]['eclname'] = $eclname;
}

for ($i = 1; $i <= $stucount; $i++)
{
$center = $students[$i]['Center_Code'];
$center = remove_bad($center);
$sex = $students[$i]['Sex'];
$sex = remove_bad($center);
$race = $students[$i]['Race'];
$race = remove_bad($race);
$school = $students[$i]['School'];
$school = remove_bad($school);
$lname = $students[$i]['Last_Name'];
$lname = remove_bad($lname);
$fname = $students[$i]['First_Name'];
$fname = remove_bad($fname);
$mi = $students[$i]['MI'];
$mi = remove_bad($mi);
$dob = $students[$i]['Date_Of_Birth'];
$dob = date_convert($dob);
$address = $students[$i]['Address'];
$address = remove_bad($address);
$city = $students[$i]['City'];
$city = remove_bad($city);
$state = $students[$i]['State'];
$state = remove_bad($state);
$zip = $students[$i]['Zip'];
$zip = remove_bad($zip);
$sid = $students[$i]['Student_ID'];
$sid = remove_bad($sid);

$pfname = $students[$i]['pfname'];
$pfname = remove_bad($pfname);
$plname = $students[$i]['plname'];
$plname = remove_bad($plname);

$home_phone = $students[$i]['Home'];
$home_phone = phone_convert($home_phone);

$ecfname = $students[$i]['ecfname'];
$ecfname = remove_bad($ecfname);
$eclname = $students[$i]['eclname'];
$eclname = remove_bad($eclname);

$ecphone = $students[$i]['ecPhone'];
$ecphone = phone_convert($ecphone);

$medinsure = $students[$i]['Medical_Insurance_Info'];
$medinsure = remove_bad($medinsure);
$doc = $students[$i]['Physician_Name'];
$doc = remove_bad($doc);
$doc_phone = $students[$i]['Physician_phone'];
$doc_phone = phone_convert($doc_phone);

$hos_pref = $students[$i]['Hospital_preference'];
$hos_pref = remove_bad($hos_Pref);
$medcon = $students[$i]['Medical_conditions'];
$medcon = remove_bad($medcon);
$year = $students[$i]['Renewal'];


switch ($center)
{
case 'CPD':
$center = 3; //id of the location
break;
case 'EDP':
$center = 3; //id of the location
break;
case 'EPD':
$center = 3; //id of the location
break;
case 'epd':
$center = 3; //id of the location
break;
case 'Epd':
$center = 3; //id of the location
break;
case 'epd':
$center = 3; //id of the location
break;
case 'MPD':
$center = 5; //id of the location
break;

default:
$center = 3;
break;
}

switch ($sex)
{
case 'm':
$sex = 'Male';
break;
case 'M':
$sex = 'Male';
break;
case 'male':
$sex = 'Male';
break;
case 'Male':
$sex = 'Male';
break;

case 'A':
$sex = 'Female';
break;
case 'a':
$sex = 'Female';
break;
case 'f':
$sex = 'Female';
break;
case 'F':
$sex = 'Female';
break;
case 'female':
$sex = 'Female';
break;
case 'Female':
$sex = 'Female';
break;

default:
$sex = 'Male';
break;
}


switch ($race)
{
case 'A':
$race = 'Asian';
break;
case 'a':
$race = 'Asian';
break;
case 'AA':
$race = 'Asian';
break;
case 'Al':
$race = 'Asian';
break;
case 'Asian':
$race = 'Asian';
break;
case 'Asian A.':
$race = 'Asian';
break;

case 'B':
$race = 'Black';
break;
case 'b':
$race = 'Black';
break;
case 'Balck':
$race = 'Black';
break;
case 'black':
$race = 'Black';
break;
case 'Black':
$race = 'Black';
break;
case 'blk':
$race = 'Black';
break;
case 'BLK':
$race = 'Black';
break;


case 'C':
$race = 'Other';
break;

case 'H':
$race = 'Hispanic';
break;
case 'h':
$race = 'Hispanic';
break;
case 'HF':
$race = 'Hispanic';
break;
case 'Hispanic':
$race = 'Hispanic';
break;
case 'HM':
$race = 'Hispanic';
break;
case 'l':
$race = 'Hispanic';
break;
case 'Ispana':
$race = 'Hispanic';
break;
case 'L':
$race = 'Hispanic';
break;
case 'Latino':
$race = 'Hispanic';
break;
case 'm':
$race = 'Hispanic';
break;
case 'M':
$race = 'Hispanic';
break;
case 'M/R':
$race = 'Hispanic';
break;
case 'Mexican':
$race = 'Hispanic';
break;

case 'native american':
$race = 'Native American';
break;

case 'W':
$race = 'White';
break;
case 'w':
$race = 'White';
break;
case 'White':
$race = 'White';
break;
case 'Samion A':
$race = 'White';
break;
case 'Somian':
$race = 'White';
break;

default:
$race = 'Other';
break;
}


$query = "INSERT INTO Participants (Main_Location_ID, Sex, Race, School_Name, Last_Name, First_Name, Middle_Initial, Date_of_Birth, Address, City, State, Zip_Code, Student_ID, Home_Phone, Emergency_Contact_First_Name, Emergency_Contact_Last_Name, Emergency_Contact_Phone, Medical_Insurance_Info, Doctor_Name, Doctor_Phone, Hospital_Preference, Medical_Conditions, Renewal_Year) VALUES ('$center', '$sex', '$race', '$school', '$lname', '$fname', '$mi', '$dob', '$address', '$city', '$state', '$zip', '$sid', '$home_phone', '$ecfname', '$eclname', '$ecphone', '$medinsure', '$doc', '$doc_phone', '$hos_pref', '$medcon', '$year')";

$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$pid = mysql_insert_id();


$PinsertQuery = "INSERT INTO Parents (First_Name, Last_Name, Home_Phone, Address, City, State, Zip_Code) VALUES ('$pfname', '$plname', '$home_phone', '$address', '$city', '$state', '$zip')";
$result = mysql_query($PinsertQuery) or die('Query failed: ' . mysql_error());

$p1id = mysql_insert_id();

//Insert 1st Parent ID into Participants table
$p1idInsertQuery = "UPDATE Participants SET 1st_Parent_or_Guardian_ID = '$p1id' WHERE Participant_ID = '$pid'";
$result = mysql_query($p1idInsertQuery) or die('Query failed: ' . mysql_error());
}

echo 'Student conversion completed. ' . $stucount-1 . ' students have been converted.<br>';
}

echo 'Starting volunteer data conversion...';

$query = "SELECT Last_Name, First_Name, MI, DOB, Address, City, State, Zip, Phone, SS, Date_Applied, Work_Address, Work_Phone, Work_Position FROM volunteer_export";

$result = mysql_query($query) or die('Query failed: ' . mysql_error());

if($result)
{
$vcount = 1;

while($row = mysql_fetch_array($result, MYSQL_BOTH))
{
$students[$vcount]['Last_Name'] = $row['Last_Name'];
$students[$vcount]['First_Name'] = $row['First_Name'];
$students[$vcount]['MI'] = $row['MI'];
$students[$vcount]['DOB'] = $row['DOB'];
$students[$vcount]['Address'] = $row['Address'];
$students[$vcount]['City'] = $row['City'];
$students[$vcount]['State'] = $row['State'];
$students[$vcount]['Zip'] = $row['Zip'];
$students[$vcount]['Phone'] = $row['Phone'];
$students[$vcount]['SSN'] = $row['SS'];
$students[$vcount]['Date_Applied'] = $row['Date_Applied'];
$students[$vcount]['Work_Address'] = $row['Work_Address'];
$students[$vcount]['Work_Phone'] = $row['Work_Phone'];
$students[$vcount]['Work_Position'] = $row['Work_Position'];

$vcount++;
}

for($i=1; $i <= $vcount; $i++)
{
$lname = $students[$i]['Last_Name'];
$lname = remove_bad($lname);
$fname = $students[$i]['First_Name'];
$fname = remove_bad($fname);
$mi = $students[$i]['MI'];
$mi = remove_bad($mi);
$dob = $students[$i]['DOB'];
$dob = date_convert($dob);
$address = $students[$i]['Address'];
$address = remove_bad($address);
$city = $students[$i]['City'];
$city = remove_bad($city);
$state = $students[$i]['State'];
$state = remove_bad($state);
$zip = $students[$i]['Zip'];
$zip = remove_bad($zip);
$phone = $students[$i]['Phone'];
$ssn = $students[$i]['SSN'];
$da = $students[$i]['Date_Applied'];
$da = date_convert($da);
$wa = $students[$i]['Work_Address'];
$wa = remove_bad($wa);
$wp = $students[$i]['Work_Phone'];
$wpos = $students[$i]['Work_Position'];
$wpos = remove_bad($wpos);

$phone = phone_convert($phone);
$wp = phone_convert($wp);

$query = "INSERT INTO Volunteers (First_Name, Last_Name, Middle_Initial, Date_Of_Birth, Address, City, State, Zip_Code, Home_Phone, SSN, Date_Applied, Work_Address, Work_Phone, Work_Position, Main_Location_ID) VALUES ('$fname', '$lname', '$mi', '$dob', '$address', '$city', '$state', '$zip', '$phone', '$ssn', '$da', '$wa', '$wp', '$wpos', '3')";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

echo 'Volunteer conversion completed. ' . $vcount-1 . ' volunteers have been converted.<br>';
}
}

function phone_convert($conphone)
{
$testphone = str_split($conphone);
$result = "";

foreach ($testphone as $ch)
{
if (is_numeric($ch))
{
if(strlen($result) == 3)
{
$result .= '-';
}

if(count($testphone) > 8 && strlen($result) == 7)
{
$result .= '-';
}

$result .= $ch;
}
}

if(count($testphone) <= 8)
{
$nphone = '816-' . $result;
}

if(strlen($conphone) == 0)
{
$nphone = '';
return $nphone;
}

return $nphone;
}

function remove_bad($input)
{
$input = str_split($input);

foreach($input as $val)
{
if($val == "'" || $val == "&" || $val == "#")
{
if($val == "&")
{
$output .= " and ";
}

if($val == "#")
{
$output .= " No. ";
}
}
else
{
$output .= $val;
}
}

return $output;
}

function date_convert($date)
{
$date = str_split($date);

foreach($date as $num)
{
if(is_numeric($num))
{
$result .= $num;
}
else
{
$result .= '-';
}
}

return $result;
}

?>
[/code]

[matto]

Have you considered using the MySQL Migration Tool Kit - It seems to migrate very well - takes care of the correct formatting etc as well....

[url=http://www.mysql.com/products/tools/migration-toolkit/]http://www.mysql.com/products/tools/migration-toolkit/[/url]

;)

[Psycho]

Try using explode(). Also, you probably need to put the date values into the format YYYY-MM-DD.
[code]function date_convert($date)
{
$dateParts = explode("/",$date);

$date = $dateParts[2]."-".$dateParts[0]."-".$dateParts[1];
return $date;
}
[/code]

Aslo, just as an observation you could greatly cut down on the amount of code for your switch statement by converting the value to lowercase or uppercase and by combining some of the cases. For instance you could write the sex switch case like this:
[code]<?php
switch (strtolower($sex))
{
case 'a':
case 'f':
case 'female':
$sex = 'Female';
break;

case 'm':
case 'male':
default:
$sex = 'Male';
break;
}
?>[/code]