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';
}