Posts Tagged ‘php’

Quick and dirty way to migrate from old Coldfusion / SQL Server to PHP / MySQL using jSON

There may be another way to do this out there, but when I needed it, I couldn’t find an easy free way to do it, so I made one.

You first need to create your new tables in your new MySQL DB and make sure you have¬†allow_url_fopen on in htaccess or in your PHP config. I use PDO so you may have to modify the code if you don’t.

Step 1: The CF file

I made one of these for each table I wanted to move. Map the jSON key to the new column names in your new DB.

<cfquery datasource="yourDSN" name="qName">
 select * from table order by id
 </cfquery>
 [
 <cfoutput query="qName">
 {
 "id":"#URLEncodedFormat(id)#",
 "col1":"#URLEncodedFormat(col1)#",
 "col2":"#URLEncodedFormat(col2)#"
 }
 <cfif currentrow neq recordcount>,</cfif>
 </cfoutput>
 ]

 Step 2: The PHP file

You can run this in your browser using https://yourserver/import.php?records=yourCFfilenameonyourremoteserver

function get_web_page($url)
{
	$options = array( 'http' => array(
			'user_agent' => 'spider', // who am i
			'max_redirects' => 30, // stop after 10 redirects
			'timeout' => 220, // timeout on response
		) );
	$context = stream_context_create( $options );
	$page = @file_get_contents( $url, false, $context );

	$result = array( );
	if ( $page != false )
		$result['content'] = $page;
	else if ( !isset( $http_response_header ) )
			return null; // Bad url, timeout

		// Save the header
		$result['header'] = $http_response_header;

	// Get the *last* HTTP status code
	$nLines = count( $http_response_header );
	for ( $i = $nLines-1; $i >= 0; $i-- )
	{
		$line = $http_response_header[$i];
		if ( strncasecmp( "HTTP", $line, 4 ) == 0 )
		{
			$response = explode( ' ', $line );
			$result['http_code'] = $response[1];
			break;
		}
	}

	return $result;
}

error_reporting(E_ALL);
ini_set("display_errors", 1);
ini_set('memory_limit', '128M');

require_once('db.php');

$records = $_GET['records'];

$inserts = get_web_page("https://pathtoyourserverwithcfandsql/".$records.".cfm");

print_r($inserts);

if ($inserts['http_code'] == 200)
{
	$inserts = $inserts['content'];
}

if (json_decode($inserts,true) == true)
{

	$insertsR = json_decode($inserts,true);

	foreach ($insertsR as $items)
	{
		$cols = array();
		$vals = array();

		foreach ($items as $key => $value)
		{
			$cols[] = $key;
			$vals[] = urldecode($value);
		}

		$colsI = implode(',',$cols);

		$questions = '';

		$valsC = count($vals);

		for ($i=1; $i <= $valsC; $i++)
		{
			$questions .= ($i < $valsC) ? '?,' : '?'; 		} 		print_r($cols); 		print_r($vals); 		echo "insert ignore into $records (".implode(',',$cols).") values ($questions)"; 		print_r($vals); 		$statement = $db->prepare("insert ignore into $records (".implode(',',$cols).") values ($questions)");
		$statement->execute($vals);

	}

}

else
{
	echo 'no bueno';
}

Canadian provinces in PHP multi-dimensional array

This was useful when using the shipping address PayPal returns for CA addresses to get rates from the UPS API. The UPS API seems to only accept CA provinces as the two-digit code, PayPal sends it back is full words.

$caProv = array("Alberta" => "AB",
"British Columbia" => "BC",
"Manitoba" => "MB",
"New Brunswick" => "NB",
"Newfoundland and Labrador" => "NL",
"Northwest Territories" => "NT",
"Nova Scotia" => "NS",
"Nunavut" => "NU",
"Ontario" => "ON",
"Prince Edward Island" => "PE",
"Quebec" => "QC",
"Saskatchewan" => "SK",
"Yukon" => "YT");
$state = ($httpParsedResponseAr['SHIPTOCOUNTRYCODE'] == "CA") ? $caProv[$httpParsedResponseAr['SHIPTOSTATE']] : $httpParsedResponseAr['SHIPTOSTATE'];